상황 : Function Execute 권한은 부여 하고, Function Source 는 조회 못하게 하고 싶다.
<< 테스트 결과 >>
1. Function Execute 권한을 부여 받은 유저는
Execute 권한은 물론, Function Source 조회 가능 ( Procedure 도 마찬가지 )
2. Package Execute 권한을 부여 받은 유저는
Execute 권한을 갖고, Package 선언부만 조회 가능하고, Pakcage Body 는 조회 불가
3. (1) Function Execute 권한 부여 + Source 조회 불가를 구현하기 위해서는 wrap 기능 필요
wrap 결과로, 소스 조회는 되지만, wrap 된 소스가 조회됨
%% wrap : 포장하다, 감싸다, 감추가
더보기
<< Oracle execute 권한 설명 >>
Execute the procedure or function directly, or access any program object declared in the specification of a package, or compile the object implicitly during a call to a currently invalid or uncompiled function or procedure. This privilege does not allow the grantee to explicitly compile using ALTER PROCEDURE or ALTER FUNCTION. For explicit compilation you need the appropriate ALTER system privilege.
-- scott 유저로 testuser 생성
SQL> show user
USER is "SCOTT"
SQL>
SQL> CREATE USER testuser IDENTIFIED BY qwer1234;
User created.
SQL> GRANT CREATE SESSION TO testuser;
Grant succeeded.
-- scott 유저로 test_fnc 평션 생성
SQL>create or replace function test_fnc
return number
is
v_no number := 1 ;
begin
null;
return v_no;
end;
/
Function created.
SQL> grant execute on test_fnc to testuser;
Grant succeeded.
SQL>
SQL> grant execute on emp_info to testuser ;
Grant succeeded.
SQL>
-- scott 유저로 emp_info 패키지 생성
-- emp_info 소스 참조 : oracleclub pl/sql 강좌
-- http://www.gurubee.net/lecture/1075
-- testuser 로 접속
SQL> conn testuser/qwer1234;
Connected.
SQL>
-- test_fnc 수행 --> 정상 수행
SQL> select scott.test_fnc() from dual ;
SCOTT.TEST_FNC()
----------------
1
SQL>
SQL> col owner for a14
SQL> set linesize 180
SQL> col name for a20
SQL> col line for 999
SQL> col text for a80
SQL>
-- execute 권한만 부여 했음에도 불구하고, test_fnc 소스 조회 가능
SQL> select owner, name, line, text from all_source where owner='SCOTT' and name ='TEST_FNC';
OWNER NAME LINE TEXT
-------------- -------------------- ---- --------------------------------------------------------------------------------
SCOTT TEST_FNC 1 function test_fnc
SCOTT TEST_FNC 2 return number
SCOTT TEST_FNC 3 is
SCOTT TEST_FNC 4 v_no number := 1 ;
SCOTT TEST_FNC 5 begin
SCOTT TEST_FNC 6 null;
SCOTT TEST_FNC 7 return v_no;
SCOTT TEST_FNC 8 end;
8 rows selected.
SQL>
SQL> set serveroutput on
-- emp_info 패키지 수행 >> 정상 수행
SQL> exec scott.emp_info.dept_emp_info(10);
?? : 7782
?? : CLARK
??? : 1981/06/09
?? : 7839
?? : KING
??? : 1981/11/17
?? : 7934
?? : MILLER
??? : 1982/01/23
PL/SQL procedure successfully completed.
-- emp_info 패키지 execute 권한만 부여 했음에도 불구하고, 패키지 선언부만 조회됨
SQL> select owner, name, line, text from all_source where owner='SCOTT' and name ='EMP_INFO';
OWNER NAME LINE TEXT
-------------- -------------------- ---- --------------------------------------------------------------------------------
SCOTT EMP_INFO 1 PACKAGE emp_info AS
SCOTT EMP_INFO 2
SCOTT EMP_INFO 3 PROCEDURE all_emp_info; -- ?? ??? ?? ??
SCOTT EMP_INFO 4
SCOTT EMP_INFO 5 PROCEDURE all_sal_info; -- ?? ??? ?? ??
SCOTT EMP_INFO 6
SCOTT EMP_INFO 7 -- ?? ??? ?? ??
SCOTT EMP_INFO 8 PROCEDURE dept_emp_info (v_deptno IN NUMBER) ;
SCOTT EMP_INFO 9
SCOTT EMP_INFO 10 -- ?? ??? ?? ??
SCOTT EMP_INFO 11 PROCEDURE dept_sal_info (v_deptno IN NUMBER) ;
SCOTT EMP_INFO 12
SCOTT EMP_INFO 13 END emp_info;
SCOTT EMP_INFO 14
14 rows selected.
SQL>
-- PAKCAGE 경우, PACKAGE BODY 는 조회 되지 않고, 선언부만 조회됨 !!!
-- wrap 수행
[oracle@oel7 ~]$ cat test_fnc.sql
create or replace function test_fnc
return number
is
v_no number := 1 ;
begin
null;
return v_no;
end;
/
[oracle@oel7 ~]$ which wrap
/ORA19/app/oracle/product/19.0.0/db_1/bin/wrap
[oracle@oel7 ~]$ wrap iname=test_fnc.sql
PL/SQL Wrapper: Release 19.0.0.0.0 - Production on Mon Dec 19 21:46:20 2022
Version 19.14.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Processing test_fnc.sql to test_fnc.plb
[oracle@oel7 ~]$ cat test_fnc.plb
create or replace function test_fnc wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
54 85
xxSk47G/q/rhM3XagiIkyO01Uz4wg8eZgcfLCNL+XhahYtH0rtkMi57Asr2ym16lmYEywLIG
m3SLnm3+TgfAlNh+lHSU2L5ucVUAc1Pk5hDP2Dj8NeXYvnFzcdiIps4Ib6k=
/
[oracle@oel7 ~]$
-- wrap 결과 파일로 재 컴파일
SQL> conn scott/tiger
Connected.
SQL> @test_fnc.plb
Function created.
SQL>
-- testuser 로 평션 수행 및 평션 소스 조회
SQL> show user
USER is "TESTUSER"
SQL>
-- test_fnc 수행 --> 정상 수행
SQL> select scott.test_fnc() from dual ;
SCOTT.TEST_FNC()
----------------
1
-- execute 권한만 부여 했음에도 불구하고, test_fnc wrap 된 소스 조회 가능 !!!
SQL> select owner, name, line, text from all_source where owner='SCOTT' and name ='TEST_FNC';
OWNER NAME LINE TEXT
-------------- -------------------- ---- --------------------------------------------------------------------------------
SCOTT TEST_FNC 1 function test_fnc wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
54 85
xxSk47G/q/rhM3XagiIkyO01Uz4wg8eZgcfLCNL+XhahYtH0rtkMi57Asr2ym16lmYEywLIG
m3SLnm3+TgfAlNh+lHSU2L5ucVUAc1Pk5hDP2Dj8NeXYvnFzcdiIps4Ib6k=
SQL>