Trusted stored units are usually used to allow specific users to perform operations on tables protected by labels. In this recipe, we will grant the READ
privilege on a specific result set to the user SMAVRIS
, through a trusted stored unit.
We grant the READ
privilege to the user SMAVRIS
, on a specific result set, using the trusted stored unit as follows:
We will create a function which returns a result set from
EMPLOYEES_OLS_TBL
. Connect as the userHR
and create the functionols_tru_stored_unit
as follows:SQL> conn HR Enter password: Connected. SQL> create or replace function ols_tru_store_unit RETURN sys_refcursor 2 is 3 ret_cur sys_refcursor; 4 begin 5 open ret_cur for select count(*) as no_employees, department_name as department from employees_ols_tbl 6 group by department_name; 7 return ret_cur; 8 end; 9 / Function created. SQL>
Test the function...