儲存過程,角色相關的呼叫者許可權和定義者許可權問題

儲存過程,角色相關的呼叫者許可權和定義者許可權問題:

建立儲存過程/函式/包的語句中有個invoker_rights_claus
其語法是：
  AUTHID { CURRENT_USER | DEFINER }
   
    預設是AUTHID DEFINER，意思是使用過程擁有者的許可權來執行過程；
   
    而AUTHID CURRENT_USER則使用過程呼叫者的許可權執行過程。

    如果使用AUTHID DEFINER將會禁止呼叫者所獲得的所有role許可權，因此假如A使用者如果想在自己的過程p中更新B使用者的t表（無論是直接寫的update語句還是透過動態SQL來做update），必須直接給A使用者授予更新b.t的許可權或者是更新任意使用者表的許可權，而不能透過role來獲得這樣的許可權。

    但如果是使用AUTHID CURRENT_USER，就不會禁止呼叫者所獲得的所有role許可權，可是在建立過程時仍然不會使用role獲得的許可權，因此如果A的過程中是直接執行的update b.t，那麼就需要直接給A授予更新b.t的許可權或者是更新任意使用者表的許可權，但假如是透過動態SQL執行的更新b.t，則可以A可以透過role來獲得對b.t的更新許可權。

AUTHID CURRENT_USER Specify CURRENT_USER if you want the methods of the class to execute with the privileges of CURRENT_USER. This clause is the default and creates an "invoker-rights class."
This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of CURRENT_USER. External names in all other statements resolve in the schema in which the methods reside.

AUTHID DEFINER Specify DEFINER if you want the methods of the class to execute with the privileges of the user who defined it.
This clause also specifies that external names resolve in the schema where the methods reside

測試:

建立測試使用者,測試表
Create user usera identified by usera;

grant connect to usera;
grant resource to usera;
grant select any dictionary to usera;

Create user userb identified by userb;

grant connect to userb;
grant resource to userb;
grant select any dictionary to userb;

 

conn usera/usera

Create table t1 as select * from dba_users;

conn userb/userb

Create table t1 as select * from dba_users;

 

測試 AUTHID DEFINER，意思是使用過程擁有者的許可權來執行過程, 儲存過程中的直接SQL和動態SQL都不能透過ROLE的間接授權來獲得許可權.

許可權授予:
Create role testrole;
grant update on userb.t1 to testrole;
grant insert on userb.t1 to testrole;

透過角色間接授權.
grant testrole to usera;

SQL> conn usera/usera;
Connected.
SQL> update userb.t1 set username='gdut';

38 rows updated.

SQL> rollback;

Rollback complete.

說明usera是可以直接更改userb.t1的.

測試在儲存過程中

conn usera/usera
直接SQL
Create or replace procedure testright  AUTHID DEFINER
is
begin
 update userb.t1 set username='gdut';
end;
/

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE TESTRIGHT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1      PL/SQL: SQL Statement ignored
4/14     PL/SQL: ORA-00942: table or view does not exist

在編譯儲存過程中就發生了錯誤, usera對userb沒有許可權.

動態SQL
Create or replace procedure testright  AUTHID DEFINER
is
v_sql varchar2(2000) := '';

begin
 v_sql := 'update userb.t1 set username=''mouse''';
 EXECUTE IMMEDIATE v_sql;
end;
/

如果是動態SQL,則在編譯過程中不會報錯,但執行過程中會報

SQL> exec testright;
BEGIN testright; END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "USERA.TESTRIGHT", line 7
ORA-06512: at line 1

這與動態SQL的原理有關,在執行過程中才檢查

###################################################
直接授權.
###################################################

conn /as sysdba
grant update on userb.t1 to usera;
grant insert on userb.t1 to usera;

直接SQL

再重新編譯儲存過程:
conn usera/usera
Create or replace procedure testright  AUTHID DEFINER
is
begin
 update userb.t1 set username='mouse';
 commit;
end;
/

Procedure created.

成功編譯

SQL> exec testright;

PL/SQL procedure successfully completed.
成功執行

SQL> select username from t1;

USERNAME
------------------------------
gdut
gdut

 

動態SQL
conn usera/usera

Create or replace procedure testright  AUTHID DEFINER
is
v_sql varchar2(2000) := '';

begin
 v_sql := 'update userb.t1 set username=''gdut''';
 EXECUTE IMMEDIATE v_sql;
 commit;
end;
/

SQL> exec testright;

PL/SQL procedure successfully completed.
成功執行

結果正常.
說明 儲存過程中的直接SQL和動態SQL都不能透過ROLE的間接授權來獲得許可權.

 

 

測試 AUTHID CURRENT_USER，則使用過程呼叫者的許可權執行過程。直接DML語句同樣不能透過ROLE獲得相應許可權,但動態SQL可以.

conn /as sysdba
SQL> revoke update on userb.t1 from usera;

Revoke succeeded.

SQL> revoke insert  on userb.t1 from usera;

Revoke succeeded.

回收直接許可權

conn usera/usera

直接SQL

Create or replace procedure testright  AUTHID CURRENT_USER
is
begin
 update userb.t1 set username='gdut';
end;
/

 

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE TESTRIGHT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1      PL/SQL: SQL Statement ignored
4/14     PL/SQL: ORA-00942: table or view does not exist

同樣報錯.

動態SQL:

Create or replace procedure testright  AUTHID CURRENT_USER
is
v_sql varchar2(2000) := '';

begin
 v_sql := 'update userb.t1 set username=''gdut''';
 EXECUTE IMMEDIATE v_sql;
 commit;
end;
/

Procedure created.

SQL> exec testright

PL/SQL procedure successfully completed.

成功驗證.