Using trusted stored units

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.

Getting ready

All the steps will be performed on the database HACKDB.

How to do it...

We grant the READ privilege to the user SMAVRIS, on a specific result set, using the trusted stored unit as follows:

  1. We will create a function which returns a result set from 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>
  2. Test the function to make sure that it returns valid information as follows:
    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>
  3. Connect as the user 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>
  4. Next, as the user 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>
  5. Connect as the user 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>

How it works...

A trusted stored unit executes using its own privileges and the caller's label. As a security limitation, it cannot be granted to roles but only to users.

There's more...

A trusted stored unit can be compiled, created, or recreated as a normal procedure or function, but remember that these operations will remove the label privileges if they are not performed by the owner.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.14.141.115