存储过程,角色相关的调用者权限和定义者权限问题:

创建存储过程/函数/包的语句中有个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.

成功验证.