오라클 함수 생성/컴파일 시 권한 에러 조치

오라클 함수 생성/컴파일 시 권한 에러 조치

다른유저 함수를 실행하는 권한을 롤(ROLE)에 부여하여하고 자신 함수에서 호출 하는 경우

menu_book Oracle 데이터베이스에서 PL/SQL 함수, 프로시저 등을 생성할 때 발생하는 권한 문제는 매우 흔하며,

• 주로 롤(Role)을 통한 권한 상속 방식
• 직접 부여된 권한의 차이 때문에 발생합니다.

• 가정 사항

1. [유저A]는 함수 FA2를 생성하려고 한다.
2. [유저B]가 생성한 FB1함수가 있다.
3. [유저A]는 [유저B].FB1함수를 'EXECUTE'하는 권한을 가진 롤(RB1)을 가지고 있다. SQL에서 실행시 권한문제 없이 잘 되고 있다.
4. [유저A]는 함수FA2 생성시 함수 내부에서 [유저B].FB1함수를 SELECT 할때 권한 에러가 발생한 상황임.

menu_book 질문 1) [유저A]는 [유저B].FB1함수를 실행할수 있는 권한을 롤(RB1)로 부여 받았다.

2) [유저A]는 FA2함수를 생성하고 FA2함수는 내부에서 [유저B].FB1함수를 호출한다.

3) 왜 [유저A]가 함수 FA2을 생성 하려고 하는데 [유저B].FB1함수 실행 권한이 없다는 에러가 발생하는가?

• 답변) Oracle에서는 PL/SQL 프로시저(함수, 프로시저, 패키지)를 컴파일(CREATE FUNCTION/PROCEDURE)할 때는 롤(RB1)을 통해 부여된 권한을 인식하지 못합니다.
• 결론) 롤에 부여한 권한말고 직접 유저에게 함수 실행권한을 부여해야한다.
• (참조) 함수생성시 옵션 :
  • AUTHID DEFINER  : 실행 시 컴파일 할 때의 유저권한 사용(DEFAULT)
  • AUTHID CURRENT_USER  : 실행 시 현재 실행하는 유저권한 사용.

생성(컴파일) 단계 (Definition Rights)

1. [유저A]가 함수FA2를 생성(컴파일)하는 시점에는, [유저B].FB1함수에 대한 `EXECUTE` 권한이 직접(Directly) 부여 되어 있어야 합니다.
2. 롤(RB1)을 통해 부여된 권한은 컴파일 시점의 권한 체크에서는 유효하게 인식되지 않습니다.
   1. 이는 PL/SQL 객체의 보안과 안정성을 높이기 위한 Oracle의 설계 원칙입니다.
   2. 즉, PL/SQL 코드를 작성하고 컴파일하는 사용자는 해당 코드에 참조되는 모든 객체에 대한 명시적인 권한을 가지고 있어야 합니다.

실행 단계 (Invoker's Rights / Definer's Rights)

1. Definer's Rights (기본값): FA2함수가 생성될 때 AUTHID DEFINER (기본값)로 생성되면,

   FA2함수가 실행될 때 FA2함수를 생성한 사용자의 권한으로 FB1함수를 호출합니다.

   이 경우에도 FB1함수에 대한 권한은 FA1함수를 생성한 [유저A]에게 '직접 부여 되어 있어야 합니다.

2. Invoker's Rights (AUTHID CURRENT_USER): 만약 FA2함수가 AUTHID CURRENT_USER로 생성되었다면,

   FA2함수가 실행될 때 FA2함수를 실행하는 [유저A]의 권한으로 FB1함수를 호출합니다.

   이때는 FA2함수를 실행하는 유저 또는 해당 사용자가 가진 롤에 FB1함수 EXECUTE 권한이 있어야 B함수를 호출할 수 있습니다.

   하지만 함수를 생성하는 시점의 권한 문제 와는 별개 입니다.

• 결론적으로, FA2함수를 생성(컴파일)할 때는 롤(RB1)을 통해 FB1함수 `EXECUTE` 권한이 부여되어 있더라도, Oracle은 이를 유효한 권한으로 간주하지 않습니다.**

---

질문 2: 사용자에게 직접 B함수 실행 권한을 부여하면 권한 에러가 발생되지 않는가?

• 사용자에게 FB1함수에 대한 `EXECUTE` 권한을 직접 부여하면 FA2함수 생성 시 발생하는 권한 에러는 해결될 것입니다.
• 예를 들어, A함수를 생성하려는 사용자(예: `USER_A`)가 있다고 가정하고, B함수가 `SCHEMA_B` 스키마에 있다고 가정할 때:

  -- USER_A 에게 SCHEMA_B.B 함수에 대한 실행 권한을 직접 부여
  GRANT EXECUTE ON SCHEMA_B.B TO USER_A;

  • 이렇게 직접 권한을 부여한 후 `USER_A`로 로그인하여 A함수를 생성하면, A함수는 B함수를 성공적으로 참조하고 컴파일될 것입니다.

---

왜 직접 권한이 필요한가?

1. Oracle은 PL/SQL 객체의 의존성과 안정성을 중요하게 생각합니다.
2. 롤을 통해 부여된 권한은 동적으로 변할 수 있습니다 (롤이 부여되거나 회수될 수 있음).
3. 만약 롤에 의해 부여된 권한으로 함수를 컴파일할 수 있다면, 나중에 해당 롤이 변경되어 함수가 참조하는 객체에 대한 권한이 사라졌을 때 함수가 무효화되거나 런타임 오류를 일으킬 수 있습니다.

• • 결론) Oracle은 PL/SQL 객체가 생성될 때는 해당 객체가 의존하는 모든 객체에 대한 권한이 영구적으로 보장될 수 있는 '직접 부여된 권한 이 있어야 한다고 강제합니다.

추가 고려사항

1. B함수에서 조회하는 테이블 권한 :
   1. A함수를 생성할 때 B함수를 호출하는 데에는 B함수 `EXECUTE` 권한만 필요합니다.
   2. B함수 내부에서 어떤 테이블을 조회하든 그 테이블에 대한 `SELECT` 권한은 B함수의 `DEFINER` (B함수를 생성한 사용자)가 가지고 있어야 합니다.
   3. 즉, B함수를 생성할 때 이미 B함수가 참조하는 테이블에 대한 권한이 B함수 생성자에게 직접 부여되어 있어야 합니다.

      • 이는 A함수 생성과는 직접적인 관련이 없습니다.

2. 권한 관리의 복잡성:
   1. 운영 환경에서는 모든 사용자에게 직접 권한을 부여하는 것이 관리상 비효율적일 수 있습니다.
   2. 이러한 경우, PL/SQL 개발을 위한 특정 스키마/사용자를 만들고, 해당 스키마/사용자에게 필요한 모든 객체에 대한 직접 권한을 부여하는 방식을 사용하기도 합니다.
   3. 그리고 일반 사용자들에게는 이러한 프로시저를 실행할 수 있는 권한을 롤을 통해 부여하는 방식으로 관리합니다.