Secure Application Roles

Secure Application Roles
------------------------

Step 1. Defi ne the view and grant privileges. Any user involved in the application should have
SELECT privileges on the view because all logons need to lookup their name in the table:

SQL> create user blake identified by blake;

User created.

SQL> create user james identified by james;

User created.

SQL> grant connect to blake,james;

Grant succeeded.

SQL> grant create view to scott;

Grant succeeded.

SQL> connect scott
Enter password:
Connected.

SQL> create view emp_job as (select ename, job from emp);

View created.

SQL> grant select on emp_job to blake;

Grant succeeded.

SQL> grant select on emp_job to james;

Grant succeeded.

Make sure that a user only has privileges to the view but not the table:

SQL> connect blake
Enter password:
Connected.
SQL> select count(*) from scott.emp_job;

  COUNT(*)
----------
        14

SQL> select * from scott.emp;
select * from scott.emp
                    *
ERROR at line 1:
ORA-00942: table or view does not exist

Step 2. Create the secure application role and grant it the appropriate object privileges:

SQL>  create role manager_role identified using access_control_policy;

Role created.

SQL> grant select on scott.emp to manager_role;

Grant succeeded.

Note that you’re not granting the role to any user—the package will do that. If a user tries to set the role they will fail—only the procedure will be able to set it:

SQL> set role manager_role;
set role manager_role
*
ERROR at line 1:
ORA-28201: Not enough privileges to enable application role 'MANAGER_ROLE'


Step 3. Build the procedure that assigns the role. Th e procedure looks up the currently logged on user and looks it up in the emp_job view. If it is a manager it sets the role thereby granting privileges to access the table:

create or replace PROCEDURE access_control_policy
authid current_user
AS
v_user varchar2(50);
v_job varchar2(50);
BEGIN
-- get the user from the context
v_user := lower( (sys_context ('userenv','session_user')));
-- get the job description
select job into v_job from scott.emp_job where lower(ename) = v_user;
-- if we're a manager then set the role
if v_job = 'MANAGER' then
DBMS_SESSION.SET_ROLE('manager_role');
else null;
end if;
END access_control_policy;
/

Procedure created.

Step 4. Grant execute privileges to the appropriate users, e.g.,

SQL> grant execute on access_control_policy to blake;

Grant succeeded.

SQL> grant execute on access_control_policy to james;

Grant succeeded.

Step 5. Test manually by invoking the procedure from your session. Blake should be able to access EMP while James should not, due to the contents of emp_job:

SQL> conn scott/scott
Connected.

SQL> select * from emp_job;
ENAME JOB
---------- ---------
…
BLAKE MANAGER
…
JAMES CLERK
…

SQL> connect blake
Enter password:
Connected.
SQL> exec sys.access_control_policy;

PL/SQL procedure successfully completed.

SQL> select * from session_roles;

ROLE
------------------------------
MANAGER_ROLE

SQL> select max(sal) from scott.emp;

  MAX(SAL)
----------
      5030

SQL> connect james
Enter password:
Connected.
SQL> exec sys.access_control_policy;

PL/SQL procedure successfully completed.

SQL> select * from session_roles;

ROLE
------------------------------
CONNECT

SQL> select max(sal) from scott.emp;
select max(sal) from scott.emp
                           *
ERROR at line 1:
ORA-00942: table or view does not exist

Finally, you can now create a log-on trigger that will execute the procedure and thus set the role as soon as the user logs in. To view which package/procedure controls the secure application role:

SQL>  select * from dba_application_roles;

ROLE                           SCHEMA     PACKAGE
------------------------------ ---------- ------------------------------
MANAGER_ROLE                   SYS        ACCESS_CONTROL_POLICY