BEGIN
? INSERT INTO departments
? ? ? ? VALUES (10, 'IT', 'Y');

? INSERT INTO departments
? ? ? ? VALUES (20, 'HR', 'N');

? COMMIT;
END;
/

CREATE TABLE employees
(
? employee_id? ? INTEGER,
? department_id? INTEGER,
? last_name? ? ? VARCHAR2 (100)
)
/

BEGIN
? DELETE FROM employees;

? INSERT INTO employees
? ? ? ? VALUES (100, 10, 'Price');

? INSERT INTO employees
? ? ? ? VALUES (101, 20, 'Sam');

? INSERT INTO employees
? ? ? ? VALUES (102, 20, 'Joseph');
? INSERT INTO employees
? ? ? ? VALUES (103, 20, 'Smith');

? COMMIT;
END;
/

并且假设SCOTT模式下仅包含employees表，定义和填充数据如下：

CREATE TABLE employees
(
? employee_id? ? INTEGER,
? department_id? INTEGER,
? last_name? ? ? VARCHAR2 (100)
)
/

BEGIN
? DELETE FROM employees;

? INSERT INTO employees
? ? ? ? VALUES (100, 10, 'Price');

? INSERT INTO employees
? ? ? ? VALUES (104, 20, 'Lakshmi');

? INSERT INTO employees
? ? ? ? VALUES (105, 20, 'Silva');

? INSERT INTO employees
? ? ? ? VALUES (106, 20, 'Ling');
? COMMIT;
END;
/

HR也包含一个可以移除一个部门下所有员工的过程。我先用定义者权限创建该过程，如下：
Code Listing 2: Definer’s rights procedure that removes employee records

CREATE OR REPLACE PROCEDURE remove_emps_in_dept (
? department_id_in IN employees.department_id%TYPE)
? AUTHID DEFINER
IS
? l_freeze? departments.staff_freeze%TYPE;
BEGIN
? SELECT staff_freeze
? ? INTO l_freeze
? ? FROM HR.departments
? ? WHERE department_id = department_id_in;

? IF l_freeze = ‘N’
? THEN
? ? ? DELETE FROM employees
? ? ? ? ? ? WHERE department_id = department_id_in;
? END IF;
END;
/

这个时候SCOTT可以执行该过程：

GRANT EXECUTE
? ON remove_emps_in_dept
? TO SCOTT
/

当SCOTT像以下方式执行过程时，将会从HR的表employees中移除3行！因为这个时候使用的是定义者权限单元。

BEGIN
? HR.remove_emps_in_dept (20);
END;
/

我需要改变该过程使得删除的是SCOTT下表employees的数据，而不是HR下。此时修改为调用者权限。
AUTHID CURRENT_USER

但是运行报错：

BEGIN
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "HR.REMOVE_EMPS_IN_DEPT", line 7
ORA-06512: at line 2问题在于Oracle数据库在SCOTT模式下找不到表HR.departments。毕竟SCOTT对HR.departments表无任何权限。
12c以前，DBA不得不赋予必要的权限给SCOTT。现在，DBA们可以采取以下步骤：

CREATE ROLE hr_departments
/

GRANT hr_departments TO hr
/

连接到HR, 授权想要的权限给角色然后授权角色给过程：

GRANT SELECT
? ON departments
? TO hr_departments
/

GRANT hr_departments TO PROCEDURE remove_emps_in_dept
/

回过头来再次执行，数据从SCOTT.employees表正确移除了！

SELECT COUNT (*)
? FROM employees
?WHERE department_id = 20
/

? COUNT(*)
—————————————
? ? ? ? 3

BEGIN
? hr.remove_emps_in_dept (20);
END;
/

SELECT COUNT (*)
? FROM employees
?WHERE department_id = 20
/

? COUNT(*)
—————————————
? ? ? ? 0

授权给程序单元的角色不会影响编译。取而代之的，他们影响运行时SQL语句的权限检查。因此，过程或函数以它自己的角色和任何当前可用的角色权限运行。
这个特性将对调用者权限程序单元最有用。你将更可能的考虑授予角色给执行动态SQL的定义者权限单元，因为动态语句的权限是在运行时检查的。