Roles in Oracle stored procedures

The way in which roles (database permission sets) interact with stored procedures, functions, and packets is one of the most difficult parts of the Oracle security model to handle. Object permissions in Oracle can be indirectly granted to users, either directly or through roles. Suppose an HR user gives Abel the user some license to the Employees table:

GRANT Select, INSERT, UPDATE, delete

This statement directly grants the above four permissions to the user Abel. On the other hand, suppose an HR user does this:

GRANT Select, INSERT, UPDATE, delete on employees to hr_role;

If Abel had been granted the hr_role role, he would now have the right to do so directly through the role.

Regardless of which method is used, Abel now has the SELECT permission for the Hr.employees table. If Abel chooses data directly from a table through a SELECT statement, it doesn't matter how he gets permission.

However, if Abel tries to establish a stored procedure, function, or packet that is selected from this table, there is a big difference between whether he is licensed directly or through a role.

Oracle requires that the user be granted direct permission to a non-owning object in a stored procedure. The role is temporarily closed during the editing process and the user cannot access any content that is granted to them. This is done for performance and security reasons.

Roles can be dynamically activated and disabled through the Set role command, which is a huge administrative burden if Oracle needs to constantly check which roles and licenses are currently active.

The following code is a short stored procedure that updates an HR employee file (this code represents hr.employees in a synonym EMPLOYEES). When Abel tries to edit the file with direct permissions in the first case, the editor succeeds; when he edits only indirect permissions in the second case, the edit fails.

CREATE OR REPLACE PROCEDURE update_emp (

p_employee_id in number

, p_salary in number

)

As

V_department_idemployees.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 = THEN

UPDATE local_employees

SET salary = P_salary

WHERE employee_id = p_employee_id;

End IF;

End;

/

Interestingly, granting a license to public is the same as granting all user licenses directly. Public is often considered a role, but it is not a role. It is a user set rather than a license set. Abel will be able to establish his own stored procedures if the Hr.employees permission is granted to public. Although this is not recommended in the Employees table, any tables granted to public are free to be applied in stored procedures.