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:
EMPLOYEES_OLS_TBL
. Connect as the user HR
and create the function ols_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>
SQL> var r refcursor; SQL> exec :r := ols_tru_store_unit; PL/SQL procedure successfully completed. SQL> print r NO_EMPLOYEES DEPARTMENT ------------ ------------------------------ 1 Administration 2 Accounting 1 Human Resources 1 Public Relations 3 Executive 5 IT 6 Purchasing 45 Shipping 34 Sales 6 Finance 2 Marketing 11 rows selected. SQL>
LBACSYS
and add the function as a trusted unit with READ
rights, as follows:SQL> conn LBACSYS Enter password: Connected. SQL> begin SA_USER_ADMIN.SET_PROG_PRIVS(policy_name => 'REG_ACCESS', schema_name => 'HR', program_unit_name => 'OLS_TRU_STORE_UNIT', privileges => 'READ,'), end ; 2 / PL/SQL procedure successfully completed. SQL>
HR
, grant execute on ols_tru_store_unit
to the user SMAVRIS
as follows:SQL> conn HR Enter password: Connected. SQL> grant execute on ols_tru_store_unit to SMAVRIS; Grant succeeded. SQL>
SMAVRIS
and execute the function ols_tru_store_unit
as follows:SQL> conn SMAVRIS Enter password: Connected. SQL> var r refcursor; SQL> exec :r := hr.ols_tru_store_unit; PL/SQL procedure successfully completed. SQL> print r NO_EMPLOYEES DEPARTMENT ------------ ------------------------------ 1 Administration 2 Accounting 1 Human Resources 1 Public Relations 3 Executive 5 IT 6 Purchasing 45 Shipping 34 Sales 6 Finance 2 Marketing 11 rows selected. SQL>
18.116.36.71