并且假设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的定义者权限单元，因为动态语句的权限是在运行时检查的。