12c 新增許可權Inherit privilege說明

inherit privilege許可權：

B使用者(較低許可權)建立了Invoker’s right procedure，A使用者(較高許可權)呼叫B使用者的procedure執行時使用的是A的許可權，為防止B在A不知情的情況下修改Procedure利用A的高許可權做一些越權操作，等下次A使用者執行procedure是這些操作便被執行。12c中引入了inherit privilege許可權，它表明invoker是否能以invoker的身份去執行owner的invoker’s right privilege，或者說B使用者的procedure是否有權以A使用者的許可權去執行這個procedure。

 

1、  預設情況下系統對於新建的使用者會將inherit privilege許可權授予PUBLIC，相當於grant inherit privileges on NEWUSER to PUBLIC：

---建立使用者：

drop user a cascade;

drop user b cascade;

create user a identified by a;

create user b identified by b;

grant connect, resource to a;

grant connect, resource to b;

grant unlimited tablespace to a,b;

 

---dba_tab_privs檢視中驗證a,b使用者都已經將inherit privilege許可權付給了PUBLIC

select * from dba_tab_privs where grantee='PUBLIC' AND privilege like '%INHERIT%'

connect a/a@192.168.56.101/orapdba

create table a (col1 varchar2(1));

insert into a values ('a');

commit;

 

connect b/b@192.168.56.101/orapdba

create table a (col1 varchar2(1));

insert into a values ('b');

commit;

 

create or replace procedure test_inv_rights authid current_user as

   v_current_user varchar2(30);

   v_session_user varchar2(30);

   t_val varchar2(10);

begin

    SELECT sys_context('USERENV','SESSION_USER'),

           sys_context('USERENV','CURRENT_USER')

        INTO v_session_user, v_current_user

    FROM dual;

    SELECT col1 into t_val from a;

    dbms_output.put_line( 'Called procedure is: ' || 'test_inv_rights' );

    dbms_output.put_line( 'User calling procedure: ' || v_session_user );

    dbms_output.put_line( 'User-context for procedure: ' || v_current_user );

    dbms_output.put_line( 'Table value : ' || t_val );

    --execute immediate 'begin test_schema_a; end;';

end;

/

 

---賦權給使用者a

grant execute on b.test_inv_rights to a;

 

---a使用者執行b使用者下的儲存過程

SQL> connect a/a@192.168.56.101/orapdba

SQL> set serveroutput on

SQL> exec b.test_inv_rights;

Called procedure is: test_inv_rights

User calling procedure: A

User-context for procedure: A

Table value : a

 

PL/SQL procedure successfully completed.

 

 

---從public revoke inherit privilege許可權，a使用者沒有執行b使用者procedure的許可權了

SQL> connect a/a@192.168.56.101/orapdba

Connected.

 

SQL> revoke inherit privileges on user a from public; 

 

Revoke succeeded.

 

SQL>  exec b.test_inv_rights;

BEGIN b.test_inv_rights; END;

 

*

ERROR at line 1:

ORA-06598: insufficient INHERIT PRIVILEGES privilege

ORA-06512: at "B.TEST_INV_RIGHTS", line 1

ORA-06512: at line 1

 

---要恢復執行b使用者procedure的許可權，可以採取如下方法：

給b使用者賦予inherit privilege的許可權

SQL> connect a/a@192.168.56.101/orapdba

Connected.

SQL> grant inherit privileges on user a to b;

 

Grant succeeded.

    SQL> set serveroutput on

SQL> exec b.test_inv_rights;

Called procedure is: test_inv_rights

User calling procedure: A

User-context for procedure: A

Table value : a

 

PL/SQL procedure successfully completed.