Understanding roles in Oracle stored procedures

By Bob Watkins, Special to ZDNet Asia
24 May 2007

One of the trickiest parts of Oracle's security model is the way that roles (collections of database privileges) interact with stored procedures, functions, and packages. Object privileges in Oracle can be granted directly to the user or indirectly via a role.

Suppose an HR user grants some permissions on the EMPLOYEES table to user ABEL:

GRANT select, insert, update, delete ON employees TO abel;

This directly grants the four privileges mentioned to the user named ABEL. On the other hand, suppose an HR user did this:

GRANT select, insert, update, delete ON employees TO hr_role;

If ABEL has been granted the role HR_ROLE, he now has these privileges indirectly via that role.

Either way, ABEL now has the SELECT privilege on the table HR.EMPLOYEES. If ABEL selects data from the table directly via the SELECT statement, it doesn't matter how he obtained permission. However, if ABEL tries to create stored procedures, functions, or packages that SELECT from this table, it makes a big difference whether he was granted permission directly or via a role.

Oracle requires that permissions to non-owned objects in a stored procedure be granted directly to the user. Roles are temporarily turned off during compilation, and the user has no access to anything granted through them. This is done for performance and security reasons. Roles can be dynamically activated and deactivated via the SET ROLE command, and it would be a large overhead for Oracle to constantly check which roles and permissions are currently active.

The following code shows a short stored procedure that updates the HR copy of employees (the code assumes that a synonym, EMPLOYEES, is used to stand for HR.EMPLOYEES). When Abel tries to compile this under the first case above with direct rights, the compilation succeeds. When he tries to compile it under the second case above with only indirect rights, the compilation fails.

CREATE OR REPLACE PROCEDURE update_emp (
p_employee_id IN NUMBER
,p_salary IN NUMBER
)

AS
v_department_id employees.department_id%TYPE;

BEGIN
SELECT department_id INTO v_department_id
FROM employees
WHERE employee_id = p_employee_id;


UPDATE employees
SET salary = p_salary
WHERE employee_id = p_employee_id;


IF v_department_id = 100 THEN
UPDATE local_employees
SET salary = p_salary
WHERE employee_id = p_employee_id;
END IF;



END;

/

One interesting fact is that granting to PUBLIC is the same as granting to all users directly. PUBLIC is often thought of as a role, but it isn't. It's a collection of users and not a collection of permissions. If the permissions on HR.EMPLOYEES had been granted to PUBLIC, ABEL would have been able to create his stored procedure. While it's not recommended in the case of an EMPLOYEES table, any table that is granted to PUBLIC can be freely used in stored procedures.