Oracle定义者权限，调用者权限

权限概述：

定义者权限：定义者权限使用它所有者的权限，而不是当前用户来执行过程。因此，你可以限制用户执行的数据库操作，允许他们仅通过运行定义者权限的过程和函数访问数据。创建过程、函数和程序包的默认权限是定义者权限。

调用者权限：在当前的用户模式下用当前的用户权限来执行过程。换句话说，就是调用者的权限过程并不与某个特定的用户或模式绑定。调用者权限程序可以使应用程序开发人员很容易的将应用逻辑集中起来，即使底层的数据在用户和模式中被划分。创建时需要显式使用AUTHID CURRENT_USER来定义调用者过程。

演示：

1、创建两个测试用户，并分别授权

SQL> create user test1 identified by test1 ;
User created.
SQL> grant connect ,resource to test1;
Grant succeeded.
SQL> create user test2 identified by test2;
User created.
SQL> grant connect ,resource to test2;
Grant succeeded.

2、切换到用户Test1，创建过程

过程一：定义者权限，为创建过程的默认权限

TEST1@orcl _SQL>CREATE OR REPLACE PROCEDURE proc_definer IS
  2  BEGIN
  3    dbms_output.put_line('Current User :' || sys_context('userenv', 'current_user'));
  4    dbms_output.put_line('Session User :' || sys_context('userenv', 'session_user'));
  5    dbms_output.put_line('Current Schema :' || sys_context('userenv', 'current_schema'));
  6  END proc_definer;
  7  /

Procedure created.

过程二：调用者权限

TEST1@orcl _SQL>CREATE OR REPLACE PROCEDURE proc_invoker AUTHID CURRENT_USER IS
  2  BEGIN
  3    dbms_output.put_line('Current User :' || sys_context('userenv', 'current_user'));
  4    dbms_output.put_line('Session User :' || sys_context('userenv', 'session_user'));
  5    dbms_output.put_line('Current Schema :' || sys_context('userenv', 'current_schema'));
  6  END proc_invoker;
  7  /

Procedure created.

3、查看这两个过程的权限

TEST1@orcl _SQL>column object_name for a20
TEST1@orcl _SQL>column authid for a20
TEST1@orcl _SQL>select object_name , authid from user_procedures where object_name like '%PROC%';

OBJECT_NAME	     AUTHID
-------------------- --------------------
PROC_INVOKER	     CURRENT_USER
PROC_DEFINER	     DEFINER

4、在TEST1下分别执行定义者权限和调用者权限过程

TEST1@orcl _SQL>set serveroutput on
TEST1@orcl _SQL>exec proc_definer;
Current User :TEST1
Session User :TEST1
Current Schema :TEST1

PL/SQL procedure successfully completed.

TEST1@orcl _SQL>exec proc_invoker;
Current User :TEST1
Session User :TEST1
Current Schema :TEST1

PL/SQL procedure successfully completed.

5、将TEST1的两个过程授权给TEST2

TEST1@orcl _SQL>grant execute on proc_definer to test2;
Grant succeeded.

TEST1@orcl _SQL>grant execute on proc_invoker to test2;
Grant succeeded.

6、切换用户TEST2，测试，结果显示在调用者权限下，程序在当前用户下用当前用户的权限执行

TEST2@orcl _SQL>set serveroutput on 
TEST2@orcl _SQL>exec test1.proc_definer;
Current User :TEST1
Session User :TEST2
Current Schema :TEST1

PL/SQL procedure successfully completed.

TEST2@orcl _SQL>exec test1.proc_invoker;
Current User :TEST2
Session User :TEST2
Current Schema :TEST2

PL/SQL procedure successfully completed.

TEST2@orcl _SQL>