Introduction

This is the second of many articles on new Oracle8i release 8.1 database features. I will be discussing in some depth many of the 150+ new features contained in the Oracle database over the next couple of months. In each of these articles we will explore some new feature or set of features found in the database and explain/provide you with:

This week we will take a look at 2 new features. Fine Grained Access Controls and Secure Application Contexts. These two features, when used together, add a new dimension to your ability to secure your data in the database.

You will see Fine Grained Access Control referred to with various names in different publications. The following are synonymous terms for this feature:

Fine Grained Access Control and Application Contexts

In a nutshell, the Fine Grained Access Control in Oracle8i is the ability for you to dynamically attach, at runtime, a predicate (where clause) to any and all queries issued against a database table or view. You now have the ability to procedurally modify the query at runtime. You may evaluate who is running the query, where they are running the query from, when they are running the query and develop a predicate given those circumstances. With the use of Application Contexts, your may securely add additional information to the environment (such as an application role the user may have) and access this in your procedure or predicate as well.

As an example of Fine Grained Access Control, you might have a security policy that determines what rows different groups of people may see. Your security policy will develop a predicate based on who is logged in and what group they are in. Fine Grained Access Control allows the query "select * from emp" when submitted by different users to be evaluated as:

User Logged In

Query dynamically rewritten to

Notes

Employee

select * 
from ( select * 
         from emp 
        where ename = USER )

Employees may see their records only

Manager

select * 
  from ( select * 
           from emp 
          where mgr = ( select empno 
                          from emp 
                         where ename = USER )
             or ename = USER )

Managers may see their record and the records of people that work for them.

HrRep

select * 
  from (select * 
          from emp 
         where deptno = 
             SYS_CONTEXT( ‘OurApp’, ‘Deptno’ ) )

HrRep may see anyone in a given department. This introduces the syntax for retrieving variables from an application context – the SYS_CONTEXT() builtin function.

 

Why use this feature?

There are many reasons to use this feature. Some of the most prevalent ones are:

 

 

 

 

How to use this feature

Fine grained access control is implemented in Oracle8i with 2 constructs:

In order to use this feature, the developer will need the following privileges in addition to the standard connect and resource roles:

An application context is created using a simple SQL command

SQL> create context OurApp using Our_Context_Pkg;

Here OurApp is the name of the context and Our_Context_Pkg is the PL/SQL package that is allowed to set values in the context. Application Contexts are an important feature in the Fine Grained Access Control implementation for two reasons:

Example 1; Implementing a security policy

If you wanted a security policy that would allow the user to see only rows they ‘owned’ unless you were the RLS_ADMIN user, you might code:

SQL> create function my_security_function( p_schema in varchar2,
  2                                        p_object in varchar2 ) return varchar2
  3  as
  4  begin
  5      if ( user = 'RLS_ADMIN' ) then
  6          return '';
  7      else
  8          return 'owner = USER';
  9      end if;
 10  end;
 11  /

Function created.

The predicate "where owner = USER" will be dynamically appended to all queries against the table to which this function is bound, effectively restricting the number of rows that would be available to the user. Only if the currently logged in user is RLS_ADMIN will a NULL (empty) predicate be returned. Returning an empty predicate is like returning "1=1" or "TRUE".

To tie this function to a table, one uses the PL/SQL procedure "dbms_rls.add_policy". For example we have the following table setup:

SQL> create table my_table
  2  (  data        varchar2(30),
  3     OWNER       varchar2(30) default USER
  4  )
  5  /
Table created.

SQL> grant all on my_table to public
  2  /
Grant succeeded.

SQL> insert into my_table ( data ) values ( 'Some Data' )
  2  /
1 row created.

SQL> insert into my_table ( data, owner )
  2  values ( 'Some Data Owned by SCOTT', 'SCOTT' )
  3  /
1 row created.

SQL> commit
  2  /
Commit complete.

SQL> select * from my_table
  2  /

DATA                           OWNER
------------------------------ ------------------------------
Some Data                      RLS
Some Data Owned by SCOTT       SCOTT 


We would attach the policy "My_Security_Policy" with the following:

SQL> begin
  2     dbms_rls.add_policy
  3     ( object_schema   => 'RLS',
  4       object_name     => 'MY_TABLE',
  5       policy_name     => 'MY_POLICY',
  6       function_schema => 'RLS',
  7       policy_function => 'My_Security_Function',
  8       statement_types => 'select, insert, update, delete' ,
  9       update_check    => TRUE );
 10  end;
 11  /

PL/SQL procedure successfully completed.

Now, all DML against the EMP table will have the predicate returned by my_security_function applied to it – regardless of the environment submitting the DML operation (i.e. regardless of the application accessing the data). To see this in action:

SQL> connect rls/rls
Connected.
SQL> select * from my_table
  2  /

DATA                           OWNER
------------------------------ ------------------------------
Some Data                      RLS

So, that shows that we have effectively filtered the rows – the current user RLS can only see its rows – the ones it owns. The row owned by SCOTT is no longer visible. Let’s connect as the RLS_ADMIN account now:

SQL> connect rls_admin/rls_admin
Connected.
SQL> select * from rls.my_table
  2  /

DATA                           OWNER
------------------------------ ------------------------------
Some Data                      RLS
Some Data Owned by SCOTT       SCOTT

 

That shows that the RLS_ADMIN account can see all of the data as we desired. Logging back in as the RLS account – we will see what happens when we attempt to create data we cannot ‘see’ (not owned by us):

SQL> connect rls/rls
Connected.
SQL> insert into my_table ( data ) values ( 'Some New Data' )
  2  /

1 row created.

SQL> insert into my_table ( data, owner )
  2  values ( 'Some New Data Owned by SCOTT', 'SCOTT' )
  3  /
insert into my_table ( data, owner )
            *
ERROR at line 1:
ORA-28115: policy with check option violation

The error ORA-28115 is raised since when we added the policy we specified

…
9       update_check    => TRUE );
…

This is analogous to creating a view with the "CHECK OPTION" enabled. This makes it so we can only create data we can also select. The default is to allow you to create data you cannot select.

Important Caveat

One important implementation feature of our security predicate function above is the fact that during a given session, this function returns a constant predicate – this is critical. If we look at the function we used above once more we see the logic is:

  3  as
  4  begin
  5      if ( user = 'RLS_ADMIN' ) then
  6          return '';
  7      else
  8          return 'owner = USER';
  9      end if;
 10  end;

This predicate function returns either no predicate or "owner = USER". During a given session it will consistently return the same predicate. There is no chance that we would retrieve the predicate "owner = USER" and later in that same session retrieve the empty predicate "". To understand why this is absolutely critical to a correctly designed Fine Grained Access Control application, we must understand when the predicate is associated with a query and how different environments handle this.

Lets say we wrote a predicate function that looked something like this:

 

SQL> create or replace function rls_examp
  2  ( p_schema in varchar2, p_object in varchar2 )
  3  return varchar2
  4  as
  5  begin
  6          if ( sys_context( 'myctx', 'x' ) is not null )
  7          then
  8                  return 'x > 0';
  9          else
 10                  return '1=0';
 11          end if;
 12  end;
/

That says that if the attribute "x" is set in the context, the predicate should be "x > 0". If the context attribute "x" is not set, the predicate is "1=0". If we create a table T, put data into it and add the policy and context as follows:

SQL> create table t ( x int );
Table created.

SQL> insert into t values ( 1234 );
1 row created.

SQL> begin
  2     dbms_rls.add_policy
  3     ( object_schema   => user, object_name => 'T',
  4       policy_name => 'T_POLICY', function_schema => user,
  5       policy_function => 'rls_examp', statement_types => 'select' );
  6  end;
  7  /
PL/SQL procedure successfully completed.

SQL> create or replace procedure set_ctx( p_val in varchar2 )
  2  as
  3  begin
  4          dbms_session.set_context( 'myctx', 'x', p_val );
  5  end;
  6  /
Procedure created.

SQL> create or replace context myctx using set_ctx;
Context created. 

It would appear that if the context is set, we would see 1 row. If the context is not set, we would see zero rows. In fact, if we test in SQLPLUS using just SQL – that would be the case:

SQL> exec set_ctx( null );
PL/SQL procedure successfully completed.

SQL> select * from t;
no rows selected

SQL> exec set_ctx( 1 ) ;
PL/SQL procedure successfully completed.

SQL> select * from t;

         X
----------
      1234

So, it would appear that we are set to go. The dynamic predicate is working as we expected. In fact, if we use PL/SQL (or Pro*C, or well coded OCI applications, as well as many other execution environments) we find that the above does not hold true. For example, lets code a small PL/SQL routine:

SQL> create or replace procedure dump_t
  2  ( some_input in number default NULL )
  3  as
  4  begin
  5          dbms_output.put_line
  6          ( '*** Output from SELECT * FROM T' );
  7  
  8          for x in (select * from t ) loop
  9                  dbms_output.put_line( x.x );
 10          end loop;
 11  
 12  
 13          if ( some_input is not null )
 14          then
 15                  dbms_output.put_line
 16                  ( '*** Output from another SELECT * FROM T' );
 17  
 18                  for x in (select * from t ) loop
 19                          dbms_output.put_line( x.x );
 20                  end loop;
 21          end if;
 22  end;
 23  /

Procedure created.

This routine simply issues a "select * from T" once in the procedure if no inputs are passed, two times in the procedure if some input is passed. Let’s execute this procedure and observe the outcome:

SQL> -- Make it so dbms_output.put_line works
SQL> set serveroutput on
 
SQL> -- unset the context -- make X have a NULL value
SQL> exec set_ctx( NULL )
PL/SQL procedure successfully completed.

SQL> -- lets run the procedure.  Note that we are letting
SQL> -- some_input default to NULL.     Only 1 select * from t
SQL> -- will execute.
SQL> --
SQL> -- As expected we get ZERO rows since the predicate 1=0
SQL> -- we used

SQL> exec dump_t
*** Output from SELECT * FROM T
PL/SQL procedure successfully completed.

SQL> -- Now, lets reset the context to have a non-null value.
SQL> exec set_ctx( 1 )
PL/SQL procedure successfully completed.

SQL> -- Since the table t contains 1 row with 1234 in it and
SQL> -- the predicate should be "x > 0" when this attribute is
SQL> -- set, we would expect queries on T to return data.
SQL> --
SQL> -- To show that this might not be the case, we run dump_t with
SQL> -- some input set to a NON-NULL value.     This will cause both
SQL> -- select * from T's to execute
SQL> --
SQL> -- You should notice that the first execution of "select * from T"
SQL> -- returns no data!  The second "select * from T" does!
SQL> --
SQL> -- Why? See below

SQL> exec dump_t( 0 )
*** Output from SELECT * FROM T
*** Output from another SELECT * FROM T
1234
PL/SQL procedure successfully completed.

So, we run the procedure with the context attribute "x" set to null and get the expected results (because it’s the first time in this session we are running this procedure). We set the context attribute "x" to a non-null value and find we get "ambiguous" results. The first select * from t in the procedure still returns no rows – it is apparently still using the predicate "1=0". The second query (which we did not execute the first time) returns what appears to be the correct results – it is apparently using the predicate "x > 0" as we expect.

Why did the first select in that procedure not use the predicate we anticipated? It is because of an optimization called "cursor caching". PL/SQL and many other execution environments do not really close a cursor when you close a cursor. The above example may be easily reproduced in Pro*C for example if the precompile option "release_cursor" is left to default to NO. If you take the same code and precompile with release_cursor=YES, the Pro*C program would behave more like queries in SQLPLUS would. The predicate used by DBMS_RLS is assigned to a query during the PARSE phase. The first query "select * from T" is getting parsed during the first execution of the stored procedure – when the predicate was in fact "1=0". The PL/SQL engine is caching this parsed cursor for you. The second time we execute the stored procedure, PLSQL simply reused the parsed cursor from the first "select * from T", this parsed query has the predicate "1=0" – the predicate function was not invoked at all this time around. Since we also passed some inputs to the procedure, PLSQL executed the second query. This query however did not already have an opened, parsed cursor for it so it parsed it during this execution – when the context attribute was NOT NULL. The second "select * from t" has the predicate "x>0" associated with it. This is the cause of the ambiguity. Since we have no control over the caching of these cursors in general, a security predicate function that may return more then 1 predicate per session should be avoided at all cost. Subtle hard to detect bugs in your application will be the result otherwise. Below, in the next example, I will demonstrate how to implement a security predicate function that cannot return more then one predicate per session. This will ensure that:

I suggest that all non-trivial predicate functions be implemented as they are in the next example. This will ensure well behaved, predicable Find Grain Access Control applications.

I should mention that there are cases where changing the predicate in the middle of a session may be desirable. The client applications that access objects that employ policies that can change predicates in the middle of a session must be coded in a specific fashion to take advantage of this. For example, in PLSQL we would have to code the application using dynamic sql entirely to avoid the cursor caching. If you are employing this dynamic predicate method – then you should bear in mind that the results will depend on how the client application is coded, therefore you should not be enforcing a security policy with this use of this feature. We will not be discussing this possible use of the DBMS_RLS feature but rather will concentrate on its intended use – to secure data.

Example 2; using application contexts

We would like to implement a Human Resources Security Policy. We will use the sample SCOTT/TIGER EMP and DEPT tables and add one additional table that allows us to designate people to be HR representatives for various departments. Our requirements for this are:

As stated, our application will use the existing EMP and DEPT tables from the SCOTT with the addition of a HR_REPS table to allow us to assign an HR representative to a department. The schema we will use is as follows:

SQL> -- create the demo schema.  it is based on the EMP & DEPT tables
SQL> -- owned by scott.  We add declaritive RI to the schema and
SQL> -- rename the ENAME's in the EMP table to match our database
SQL> -- user enames we created  (eg: there is a RLS_KING user and
SQL> -- RLS_KING ename in the emp table)

SQL> create table dept as select * from scott.dept;
Table created.

SQL> alter table dept add constraint dept_pk primary key(deptno);
Table altered.

SQL> create table emp_base_table as select * from scott.emp;
Table created.

SQL> update emp_base_table set ename = 'RLS_' || ename;
14 rows updated.

SQL> alter table emp_base_table add constraint emp_pk primary key(empno);
Table altered.

SQL> alter table emp_base_table add constraint emp_fk_to_dept
  2  foreign key (deptno) references dept(deptno);
Table altered.

SQL> -- create indexes that will be used by our application context
SQL> -- functions for performance.  We need to find if a specific
SQL> -- user is a mgr of a department quickly.
SQL> -- Also we need to convert a username into an empno quickly

SQL> create index emp_mgr_deptno_idx on emp_base_table(mgr);
Index created.

SQL> create unique index emp_ename_idx on emp_base_table(ename);
Index created.

SQL> -- Also, we create a view EMP as select * from the emp_base_table.
SQL> -- This VIEW is what we will place our policy on and what our
SQL> -- applications will use to query/insert/update etc.

SQL> create view emp as select * from emp_base_table;
View created.


SQL> -- create the table that manages our assigned HR_REPS.  We use
SQL> -- an INDEX ORGANIZED TABLE for this since we always just query
SQL> -- "select * from hr_reps where username = X and deptno = Y".
SQL> -- no need for a table, just need the index.

SQL> create table hr_reps
  2  (   username    varchar2(30),
  3      deptno        number,
  4      primary key(username,deptno)
  5  )
  6  organization index;
Table created.

SQL> -- Make our assignments of HR Reps to departments.  KING can see
SQL> -- all depts.

SQL> insert into hr_reps values ( 'RLS_JONES', 10 );
SQL> insert into hr_reps values ( 'RLS_BLAKE', 20 );
SQL> insert into hr_reps values ( 'RLS_CLARK', 30 );
SQL> insert into hr_reps values ( 'RLS_KING', 10 );
SQL> insert into hr_reps values ( 'RLS_KING', 20 );
SQL> insert into hr_reps values ( 'RLS_KING', 30 );
SQL> insert into hr_reps values ( 'RLS', 10 );
SQL> commit;
Commit complete.

 

Now that we have the application tables EMP, DEPT and HR_REPS created, let’s create a procedure that will let us set an application context. This application context will contain two pieces of information – the currently logged in users EMPNO and the role they are using (one of EMP, MGR, or HR_REP). Our dynamic predicate routine will use the role stored in application context to decide what the where clause should look like for the given user.

We use the EMP_BASE_TABLE and HR_REPs tables to make this determination. This leads us to the question "why do we have a table EMP_BASE_TABLE and a view EMP that is simply select * from emp_base_table?" Two reasons:

  1. We use the data in the employee table to enforce our security policy.
  2. We read that table while attempting to set an application context.

In order to read the employee data we need the application context to be set – in order to set the application context we need to read the employee data. It’s a chicken and egg problem. Our solution is to create a view that all applications will use (the EMP view) and enforce our security on that view. The original EMP_BASE_TABLE will be used by our security policy to enforce the rules. From the EMP_BASE_TABLE we can discover who is a manager of a given department and who works for a given user. The application and end users will never use the EMP_BASE_TABLE – only our security policy will. The procedure to set our context then is:

SQL> -- this is our 'trusted' procedure for setting the application
SQL> -- context
SQL> -- it enforces our policies by only setting the privileges you
SQL> -- have been granted in the application context.

SQL> create or replace
  2  procedure set_role( p_roleName in varchar2 default null )
  3  as
  4      l_empno       number;
  5      l_cnt      number;
  6      l_roleName varchar2(40) default upper(p_roleName);
  7  begin
  8      if ( sys_context( 'Hr_App_Ctx', 'RoleName' ) is NOT NULL )
  9      then
 10          -- fail the request.  Changing a ROLE requires changing
 11          -- the predicate associated with queries.  Due to cursor
 12          -- caching in client applications, we cannot ensure that
 13          -- there are not already parsed queries in place with
 14          -- predicates from the current role.
 15          -- If we had for example already set our role to MGR and
 16          -- parsed some queries and now attempted to change our role
 17          -- to EMP, our parsed queries would still be enforcing
 18          -- MGR predicates, not EMP predicates.
 19          raise_application_error( -20001, 'Role Already Set' );
 20      end if;
 21
 22      -- Because we need to use it so frequently, the empno for
 23      -- the current user will be stored in this context as well
 24      -- we use SESSION user, not CURRENT user here.  SESSION user
 25      -- is the name of the currently logged in user.  CURRENT user
 26      -- is the username of the person whose privs the query is
 27      -- executing with, it would be the owner of this procedure
 28      -- not the currently logged in user!!!
 29
 30      select empno into l_empno
 31        from emp_base_table
 32       where ename = sys_context( 'userenv', 'session_user');
 33
 34      dbms_session.set_context( 'Hr_App_Ctx', 'Empno', l_empno );
 35
 36      if ( l_roleName = 'EMP' )
 37      then
 38          -- everyone may use the EMP role
 39          dbms_session.set_context( 'Hr_App_Ctx', 'RoleName', 'EMP' );
 40      elsif ( l_roleName = 'MGR' )
 41      then
 42          -- lets verify this person is a MGR, if not, give them
 43          -- an error message and fail.  They can try again.
 44
 45          select count(*) into l_cnt
 46            from dual
 47           where exists
 48             ( select NULL
 49             from emp_base_table
 50                where mgr = to_number(sys_context('Hr_App_Ctx','Empno'))
 51             );
 52          if ( l_cnt = 0 )
 53          then
 54          raise_application_error( -20002, 'You are not a manager' );
 55          end if;
 56          dbms_session.set_context( 'Hr_App_Ctx', 'RoleName', 'MGR' );
 57      elsif ( l_roleName = 'HR_REP' )
 58      then
 59          -- lets verify this person is a HR_REP, if not, give them
 60          -- an error message and fail.  They can try again.
 61
 62          select count(*) into l_cnt
 63            from dual
 64           where exists
 65             ( select NULL
 66             from hr_reps
 67                where username = sys_context( 'userenv', 'session_user' )
 68             );
 69
 70          if ( l_cnt = 0 )
 71          then
 72          raise_application_error( -20002, 'You are not an HR Rep' );
 73          end if;
 74          dbms_session.set_context( 'Hr_App_Ctx', 'RoleName', 'HR_REP' );
 75      else
 76          raise_application_error( -20003, 'Role ' || l_roleName ||
 77                           ' not recognized' );
 78      end if;
 79  end;
 80  /
Procedure created.

SQL> grant execute on set_role to public
  2  /
Grant succeeded.

So, what we’ve done so far is create a procedure that accepts a role name as an in parameter. This procedure begins by ensuring the RoleName attribute has not already been set. Since we will return different predicates based on the value of RoleName in our security policy – we cannot permit a user to change their role after it has been set. If we did, we would have a potential problem with cached cursors and ‘old’ predicates. Next, we look up the current users EMPNO. This does two things for us

  1. It verifies the end user is an employee – if we get an error "NO DATA FOUND", we know the person is not an employee. Since their context never gets set – that person will see no data
  2. It puts frequently used value into the application context. We can now quickly access the EMP table by the current users EMPNO – which we will do in the predicate function below.

The procedure then goes onto verify the current user is allowed to assume the role they are requesting. Anyone who requests the EMP role may set it. Only people who actually manage other people may set the MGR role. Only people who have entries in the HR_REPS table may set the HR_REP role.

Next, we will create the database application context object and bind it to the SET_HR_APP_DEPT procedure we just created:

SQL> -- This is our application context.  the name of the
SQL> -- context is HR_APP_CTX.  The Procedure is it bound to in this case
SQL> -- is SET_ROLE

SQL> create or replace context Hr_App_Ctx using SET_ROLE
  2  /
Context created.

 

So, now we have a context called Hr_App_Ctx and a procedure to set it. It is important to note that since the context is bound to the procedure Set_Role – that is the only way to set values in this context. For example, if we attempt to set a RoleName in the context, in hopes of gaining access to data we should not, we will discover that we cannot:

SQL> REM the following will FAIL.  It is to show that
SQL> REM dbms_session.set_context will only work in the SET_ROLE
SQL> REM procedure for the Hr_App_Ctx context

SQL> exec dbms_session.set_context( 'Hr_App_Ctx', 'RoleName', 'MGR' );
BEGIN dbms_session.set_context( 'Hr_App_Ctx', 'RoleName', 'MGR' ); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 55
ORA-06512: at line 1

This attribute of contexts is what makes them secure. Only your procedure may set values in the context – ensuring you that if a value is there, it has been validated and assigned by you. Now, to test the logic of our procedure, we will attempt to use the stored procedure as various users and see what roles we can set and what values are placed into the context.

SQL> grant select on sys.v_$context to rls_smith;
Grant succeeded.

SQL> connect rls_smith/rls_smith
Connected.

SQL> set serveroutput on
SQL> show user
USER is "RLS_SMITH"

SQL> exec rls.set_role( 'Mgr' )
BEGIN rls.set_role( 'Mgr' ); END;

*
ERROR at line 1:
ORA-20002: You are not a manager
ORA-06512: at "RLS.SET_ROLE", line 53
ORA-06512: at line 1

So far, we can see that our routine is available to RLS_SMITH but it won’t let him set his context to ‘MGR’ since he is in fact not a manager. If we inspect his context right now via the dynamic view v$context, we see:

SQL> select * from v$context;

NAMESPACE  ATTRIBUTE  VALUE
---------- ---------- -----
HR_APP_CTX EMPNO      7369

that RLS_SMITH was in fact able to get his employee number set, but no RoleName attribute. Now, we retry the operation with a RoleName that is valid for RLS_SMITH:

SQL> exec rls.set_role( 'emp' )
PL/SQL procedure successfully completed.

SQL> select * from v$context;

NAMESPACE  ATTRIBUTE  VALUE
---------- ---------- -----
HR_APP_CTX ROLENAME   EMP
HR_APP_CTX EMPNO      7369

We can see that that works as expected then. RLS_SMITH gets his employee number and RoleName attribute set in the HR_APP_CTX context successfully. To further test the logic, we:

SQL> exec rls.set_role( 'emp' )
BEGIN rls.set_role( 'emp' ); END;

*
ERROR at line 1:
ORA-20001: Role Already Set
ORA-06512: at "RLS.SET_ROLE", line 18
ORA-06512: at line 1

SQL> select * from v$context;

NAMESPACE  ATTRIBUTE  VALUE
---------- ---------- -----
HR_APP_CTX ROLENAME   EMP
HR_APP_CTX EMPNO      7369

Showing us that our logic that does not permit a user to alter their role after setting it is in place, further is shows that the context just retains its previous values. This is a non-fatal error message, it just means you cannot change your role, your session is still OK.

Next, let’s connect as a different user and see how the procedure works and look at a different way to inspect a session’s context values:

SQL> connect rls_blake/rls_blake
Connected.

SQL> exec rls.set_role( 'mgr' )
PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> declare
  2      l_AppCtx     dbms_session.AppCtxTabTyp;
  3      l_size        number;
  4  begin
  5      dbms_session.list_context( l_AppCtx, l_size );
  6      for i in 1 .. l_size loop
  7          dbms_output.put( l_AppCtx(i).namespace || '.' );
  8          dbms_output.put( l_AppCtx(i).attribute || ' = ' );
  9          dbms_output.put_line( l_AppCtx(i).value );
 10      end loop;
 11  end;
/

HR_APP_CTX.ROLENAME = MGR
HR_APP_CTX.EMPNO = 7698

PL/SQL procedure successfully completed.

This time, we logged in as RLS_BLAKE who is the manager for department 30. When RLS_BLAKE calls Set_Role procedure with a RoleName of ‘MGR’, we see the context is set appropriately – he is a manager and his employee number is set. This also demonstrates how to list the attribute value pairs in a session’s context using the dbms_session.list_context package. This package is executable by the general public (as opposed to the sys.v$context view we used above), hence all users will be able to use this method to inspect their session’s context values.

Now that we have our session context being populated the way we want, we can set about to write our security policy procedure. This is the procedure that will be called by the database engine at run time to provide a dynamic predicate. The dynamic predicate will restrict what the user can read or write.

SQL> -- create the predicate package (package to build the where clause)
SQL> -- for the HR application.  We have individual functions to build
SQL> -- unique where clauses for each of SELECT/INSERT/UPDATE/DELETE.

SQL> create or replace package hr_predicate_pkg
  2  as
  3     function select_function( p_schema in varchar2,
  4                               p_object in varchar2 ) return varchar2;
  5
  6     function update_function( p_schema in varchar2,
  7                               p_object in varchar2 ) return varchar2;
  8
  9     function insert_delete_function( p_schema in varchar2,
 10                                      p_object in varchar2 ) return varchar2;
 11  end;
 12  /

We will be implementing a slightly different predicate for each of the Data Manipulation Language (DML) operations. Each DML operation will be subject to its rules. This allows for a DELETE statement to see a different (in this case a smaller) set of data then a SELECT would. The implementation of the HR_PREDICATE_PKG is as follows:

 

SQL> create or replace package body hr_predicate_pkg
  2  as
  3
  4  g_app_ctx constant varchar2(30) default 'Hr_App_Ctx';
  5
  6  g_sel_pred varchar2(1024) default NULL;
  7  g_upd_pred varchar2(1024) default NULL;
  8  g_ins_del_pred varchar2(1024) default NULL;
  9
 10
 11  function select_function( p_schema in varchar2,
 12                    p_object in varchar2 ) return varchar2
 13  is
 14  begin
 15
 16      if ( g_sel_pred is NULL )
 17      then
 18          if ( sys_context( g_app_ctx, 'RoleName' ) = 'EMP' )
 19          then
 20          g_sel_pred := 'empno = sys_context(''Hr_App_Ctx'',''EmpNo'')';
 21
 22          elsif ( sys_context( g_app_ctx, 'RoleName' ) = 'MGR' )
 23          then
 24          g_sel_pred :=
 25             ' empno in ( select empno
 26                    from emp_base_table
 27                   start with empno =
 28                          sys_context(''Hr_App_Ctx'',''EmpNo'')
 29                 connect by prior empno = mgr)';
 30
 31          elsif ( sys_context( g_app_ctx, 'RoleName' ) = 'HR_REP' )
 32          then
 33          g_sel_pred := 'deptno in
 34                   ( select deptno
 35                       from hr_reps
 36                      where username =
 37                      sys_context(''userenv'',''session_user'') )';
 38
 39          else
 40          raise_application_error( -20005, 'No Role Set' );
 41          end if;
 42      end if;
 43
 44      return g_sel_pred;
 45  end;
 46
 47  function update_function( p_schema in varchar2,
 48                    p_object in varchar2 ) return varchar2
 49  is
 50  begin
 51      if ( g_upd_pred is NULL )
 52      then
 53          if ( sys_context( g_app_ctx, 'RoleName' ) = 'EMP' )
 54          then
 55          g_upd_pred := '1=0';
 56
 57          elsif ( sys_context( g_app_ctx, 'RoleName' ) = 'MGR' )
 58          then
 59          g_upd_pred :=
 60             ' empno in ( select empno
 61                    from emp_base_table
 62                   where mgr =
 63                       sys_context(''Hr_App_Ctx'',''EmpNo'') )';
 64
 65          elsif ( sys_context( g_app_ctx, 'RoleName' ) = 'HR_REP' )
 66          then
 67          g_upd_pred := 'deptno in
 68                   ( select deptno
 69                       from hr_reps
 70                      where username =
 71              sys_context(''userenv'',''session_user'') )';
 72
 73          else
 74          raise_application_error( -20005, 'No Role Set' );
 75          end if;
 76      end if;
 77
 78      return g_upd_pred;
 79  end;
 80
 81  function insert_delete_function( p_schema in varchar2,
 82                       p_object in varchar2 ) return varchar2
 83  is
 84  begin
 85      if ( g_ins_del_pred is NULL )
 86      then
 87          if ( sys_context( g_app_ctx, 'RoleName' ) in ( 'EMP', 'MGR' ) )
 88          then
 89          g_ins_del_pred := '1=0';
 90          elsif ( sys_context( g_app_ctx, 'RoleName' ) = 'HR_REP' )
 91          then
 92          g_upd_pred := 'deptno in
 93                   ( select deptno
 94                       from hr_reps
 95                      where username =
 96                  sys_context(''userenv'',''session_user'') )';
 97          else
 98          raise_application_error( -20005, 'No Role Set' );
 99          end if;
100      end if;
101      return g_ins_del_pred;
102  end;
103
104  end;
/

Package body created.

In the past, before Fine Grained Access Control, having one table with the above three predicates could only have been achieved with the use of 3 views – one each to SELECT, UPDATE, and INSERT/DELETE from. Fine Grained Access Control simplifies this to just one table with a dynamic predicate.

Notice in the above code the logic:

 11  function select_function( p_schema in varchar2,
 12                    p_object in varchar2 ) return varchar2
 13  is
 14  begin
 15
 16      if ( g_sel_pred is NULL )
 17      then
		logic to set g_sel_pred, the global variable
           declared in the package body
 42      end if;
 43
 44      return g_sel_pred;
 45  end;

In this routine we set the variable g_sel_pred to a non-null value exactly once per session. If we have already set the predicate in a previous call to this predicate function – we simply return that predicate again. This has 2 advantages

The last step in the process is to associate our predicates with each of the DML operations and the EMP table itself. This is accomplished as follows:

SQL> -- Add our policy to the EMP view.  This associates each of the
SQL> -- HR_PREDICATE_PKG functions with the table for each of SELECT/INSERT
SQL> -- UPDATE/DELETE.
SQL> -- On INSERT and UPDATE, we set the 'update_check' flag to TRUE.  This
SQL> -- is very much like creating a view with the 'CHECK OPTION'.  It
SQL> -- ensures data we create in the database is data we can see in
SQL> -- the database.

SQL> begin
  2     dbms_rls.add_policy
  3     ( object_schema   => 'RLS',
  4       object_name     => 'EMP',
  5       policy_name     => 'HR_APP_SELECT_POLICY',
  6       function_schema => 'RLS',
  7       policy_function => 'HR_PREDICATE_PKG.SELECT_FUNCTION',
  8       statement_types => 'select' );
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> begin
  2     dbms_rls.add_policy
  3     ( object_schema   => 'RLS',
  4       object_name     => 'EMP',
  5       policy_name     => 'HR_APP_UPDATE_POLICY',
  6       function_schema => 'RLS',
  7       policy_function => 'HR_PREDICATE_PKG.UPDATE_FUNCTION',
  8       statement_types => 'update' ,
  9       update_check    => TRUE );
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> begin
  2     dbms_rls.add_policy
  3     ( object_schema   => 'RLS',
  4       object_name     => 'EMP',
  5       policy_name     => 'HR_APP_INSERT_DELETE_POLICY',
  6       function_schema => 'RLS',
  7       policy_function => 'HR_PREDICATE_PKG.INSERT_DELETE_FUNCTION',
  8       statement_types => 'insert, delete' ,
  9       update_check    => TRUE );
 10  end;
 11  /

PL/SQL procedure successfully completed.

So, for each of the DML operations, we have associated a different predicate function. When the user queries the EMP table, the predicate generated by the hr_predicate_pkg.select_function package will be invoked. When the user updates the table, the update function in that package will be used, and so on.

Now, to test the application. We will create a package HR_APP. This package represents our application. It has entry points to:

We will log in as various users, with different roles, and monitor the behavior of our application. This will show us Fine Grain Access Control at work.

The following is our specification for our application:

SQL> create or replace package hr_app
  2  as
  3      procedure listEmps;
  4
  5      procedure updateSal;
  6
  7      procedure deleteAll;
  8
  9      procedure insertNew( p_deptno in number );
 10  end;
 11  /

Package created.

And the package body is

SQL> create or replace package body hr_app
  2  as
  3
  4  procedure listEmps
  5  as
  6      l_AppCtx     dbms_session.AppCtxTabTyp;
  7      l_size       number;
  8  begin
  9      dbms_output.put_line( '------ Session Context ----------' );
 10      dbms_session.list_context( l_AppCtx, l_size );
 11      for i in 1 .. l_size loop
 12          dbms_output.put( l_AppCtx(i).namespace || '.' );
 13          dbms_output.put( l_AppCtx(i).attribute || ' = ' );
 14          dbms_output.put_line( l_AppCtx(i).value );
 15      end loop;
 16
 17      dbms_output.put_line( '------ Emp data you can see -----' );
 18      for x in ( select ename, sal, dname
 19               from emp, dept
 20              where emp.deptno = dept.deptno )
 21      loop
 22          dbms_output.put_line( x.ename || ',' || x.sal || ',' || x.dname );
 23      end loop;
 24  end;
 25
 26
 27  procedure updateSal
 28  is
 29  begin
 30      update emp set sal = 9999;
 31      dbms_output.put_line( sql%rowcount || ' rows updated' );
 32  end;
 33
 34  procedure deleteAll
 35  is
 36  begin
 37      delete from emp where empno <> sys_context( 'Hr_app_Ctx', 'EMPNO' );
 38      dbms_output.put_line( sql%rowcount || ' rows deleted' );
 39  end;
 40
 41  procedure insertNew( p_deptno in number )
 42  as
 43  begin
 44      insert into emp ( empno, deptno, sal ) values ( 123, p_deptno, 1111 );
 45  end;
 46
 47  end hr_app;
 48  /

Package body created.

SQL> grant execute on hr_app to public
  2  /
Grant succeeded.

So, that’s our "application". The listEmps routine shows every record we can see in the EMP view. The updateSal routine updates every record we are allowed to. The deleteAll routine deletes every record we are allowed to with the exception of our record. The insertNew routine tries to create a new employee in the department we request. This application simply tests all of the DML operations we might attempt on the EMP view.

Now, as different users, we will log in and test the functionality of our application.

SQL> @rls_adams
SQL> -- Log in as an employee with no management capability.

SQL> connect rls_adams/rls_adams
Connected.

SQL> set serveroutput on

SQL> -- First, lets try to become a manager
SQL> -- We are not a manager so it will not let
SQL> -- us become one
SQL> exec rls.set_role( 'mgr' )
BEGIN rls.set_role( 'mgr' ); END;

*
ERROR at line 1:
ORA-20002: You are not a manager
ORA-06512: at "RLS.SET_ROLE", line 53
ORA-06512: at line 1

So, that shows us we cannot assume a role we are not supposed to. To show that no data can be accessed, we’ll immediately try to access some and see what happens:

SQL> -- Now, lets see what happens when we try to do
SQL> -- stuff without setting the role

SQL> exec rls.hr_app.listEmps
------ Session Context ----------
HR_APP_CTX.EMPNO = 7876
------ Emp data you can see -----
BEGIN rls.hr_app.listEmps; END;

*
ERROR at line 1:
ORA-28112: failed to execute policy function
ORA-06512: at "RLS.HR_APP", line 18
ORA-06512: at line 1

We get an error message. This error message is occurring because we coded in our predicate routine:

 

function select_function( p_schema in varchar2,
                          p_object in varchar2 ) return varchar2
is
begin

    if ( g_sel_pred is NULL )
    then
        if ( sys_context( g_app_ctx, 'RoleName' ) = 'EMP' )
        then
            …
        elsif ( sys_context( g_app_ctx, 'RoleName' ) = 'MGR' )
        then
		…
        elsif ( sys_context( g_app_ctx, 'RoleName' ) = 'HR_REP' )
        then
…
        else
            raise_application_error( -20005, 'No Role Set' );
        end if;
    end if;

    return g_sel_pred;
end;

We are seeing the effects of issuing the raise_application_error in the predicate function. That is causing the end user to see the ORA-28112 error message. Below, in the next section, we will discuss how to track these errors and how to debug them.

Next, we’ll set our role to something we are able to and retry these operations:

SQL> -- Now, lets set our correct role and do stuff

SQL> exec rls.set_role( 'emp' );
PL/SQL procedure successfully completed.

SQL> -- this will show us our application context
SQL> -- and the data we are allowed to see - just
SQL> -- our record

SQL> exec rls.hr_app.listEmps
------ Session Context ----------
HR_APP_CTX.ROLENAME = EMP
HR_APP_CTX.EMPNO = 7876
------ Emp data you can see -----
RLS_ADAMS,1100,RESEARCH

PL/SQL procedure successfully completed.

SQL> -- this will show that even though we can 'see' data
SQL> -- we cannot 'update' any of it.

SQL> exec rls.hr_app.updateSal
0 rows updated

PL/SQL procedure successfully completed.

SQL> -- this will show we cannot delete any information

SQL> exec rls.hr_app.deleteAll
0 rows deleted

PL/SQL procedure successfully completed.

SQL> -- and this shows we cannot create any
SQL> exec rls.hr_app.insertNew(20)
BEGIN rls.hr_app.insertNew(20); END;

*
ERROR at line 1:
ORA-28115: policy with check option violation
ORA-06512: at "RLS.HR_APP", line 44
ORA-06512: at line 1

So, the above shows we can see only our record, we cannot update any data whatsoever, we cannot delete any records and inserting a new employee fails as well. This is exactly what we intended – and it happens transparently. The application, HR_APP, does nothing special to enforce these rules – the database is doing it for us now.

Next, we log in as a MGR and see what happens:

SQL> -- Log in as a manager
SQL> connect rls_jones/rls_jones
Connected.

SQL> -- Enable printing from PLSQL
SQL> set serveroutput on

SQL> -- First, lets try to become a manager
SQL> -- We are a manager so it will let
SQL> -- us become one this time

SQL> exec rls.set_role( 'mgr' )
PL/SQL procedure successfully completed.

SQL> -- this will show our context and data
SQL> -- we can see.  more then one row this
SQL> -- time.

SQL> exec rls.hr_app.listEmps
------ Session Context ----------
HR_APP_CTX.ROLENAME = MGR
HR_APP_CTX.EMPNO = 7566
------ Emp data you can see -----
RLS_SMITH,800,RESEARCH
RLS_JONES,2975,RESEARCH
RLS_SCOTT,3000,RESEARCH
RLS_ADAMS,1100,RESEARCH
RLS_FORD,3000,RESEARCH

PL/SQL procedure successfully completed.

SQL> -- this will show we can update some records
SQL> -- we will run listEmps again to see which
SQL> -- rows we updated (our direct reports only)

SQL> exec rls.hr_app.updateSal
2 rows updated

PL/SQL procedure successfully completed.

SQL> exec rls.hr_app.listEmps
------ Session Context ----------
HR_APP_CTX.ROLENAME = MGR
HR_APP_CTX.EMPNO = 7566
------ Emp data you can see -----
RLS_SMITH,800,RESEARCH
RLS_JONES,2975,RESEARCH
RLS_SCOTT,9999,RESEARCH
RLS_ADAMS,1100,RESEARCH
RLS_FORD,9999,RESEARCH

PL/SQL procedure successfully completed.


SQL> -- since we are not an HR REP we cannot
SQL> -- delete anyone given our rules

SQL> exec rls.hr_app.deleteAll
0 rows deleted

PL/SQL procedure successfully completed.

SQL> -- since we are not an HR REP we cannot
SQL> -- insert anyone given our rules
SQL> exec rls.hr_app.insertNew(20)
BEGIN rls.hr_app.insertNew(20); END;

*
ERROR at line 1:
ORA-28115: policy with check option violation
ORA-06512: at "RLS.HR_APP", line 44
ORA-06512: at line 1

So, this time as a MGR we can

Lastly, we’ll log in as a HR_REP and review the behavior of our application in that role:

SQL> -- Log in as a HR Rep
SQL> connect rls_king/rls_king
Connected.

SQL> -- Enable printing from PLSQL
SQL> set serveroutput on

SQL> -- First, lets try to become a HR_REP
SQL> -- We are a HR_REP so it will let
SQL> -- us become one

SQL> exec rls.set_role( 'hr_rep' )
PL/SQL procedure successfully completed.

SQL> -- this will show our context and data
SQL> -- we can see.  ALL rows this
SQL> -- time as an HR_REP.

SQL> exec rls.hr_app.listEmps
------ Session Context ----------
HR_APP_CTX.ROLENAME = HR_REP
HR_APP_CTX.EMPNO = 7839
------ Emp data you can see -----
RLS_CLARK,2450,ACCOUNTING
RLS_KING,5000,ACCOUNTING
RLS_MILLER,1300,ACCOUNTING
RLS_SMITH,800,RESEARCH
RLS_JONES,2975,RESEARCH
RLS_SCOTT,9999,RESEARCH
RLS_ADAMS,1100,RESEARCH
RLS_FORD,9999,RESEARCH
RLS_ALLEN,1600,SALES
RLS_WARD,1250,SALES
RLS_MARTIN,1250,SALES
RLS_BLAKE,2850,SALES
RLS_TURNER,1500,SALES
RLS_JAMES,950,SALES

PL/SQL procedure successfully completed.

SQL> -- this will show we can update ALL records
SQL> -- in ALL departments since we are an HR
SQL> -- rep for all
SQL> -- we will run listEmps again to see which
SQL> -- rows we updated (all of them)

SQL> exec rls.hr_app.updateSal
14 rows updated

PL/SQL procedure successfully completed.

SQL> -- since we are an HR REP we can
SQL> -- delete anyone given our rules
SQL> -- delete All will not delete 'me',
SQL> -- the current user

SQL> exec rls.hr_app.deleteAll
13 rows deleted

PL/SQL procedure successfully completed.

SQL> -- since we are an HR REP we can
SQL> -- insert anyone given our rules

SQL> exec rls.hr_app.insertNew(20)
PL/SQL procedure successfully completed.

SQL> -- lets see the effect of our update, delete
SQL> -- and subsequent insert

SQL> exec rls.hr_app.listEmps
------ Session Context ----------
HR_APP_CTX.ROLENAME = HR_REP
HR_APP_CTX.EMPNO = 7839
------ Emp data you can see -----
RLS_KING,9999,ACCOUNTING
,1111,RESEARCH

PL/SQL procedure successfully completed.

That completes the testing of our three roles. Our requirements have been met – we secured the data and did it transparently to the application.

Handling Errors and debugging

During the implementation of the above application, I ran into many errors and had to debug my application. Since Fine Grained Access Control happens in the server, it can be a little obtuse to diagnose errors and debug your application. The following sections will help you successfully debug and diagnose errors.

There are 4 major Oracle error codes you will encounter while developing Fine Grained Access Control routines. They are:

One utility I use frequently when writing predicate functions is a simple ‘debug’ package. This package, authored by Christopher Beck – also of Oracle (mailto:clbeck@oracle.com), allows us to instrument our code with ‘print’ statements. This package also allows us to liberally put statements like:

create function foo …
as
   …
begin
     debug.f( ‘Enter procedure foo’ );
     if ( some_condition ) then
         l_predicate := ‘x=1’;
     end if;
   
     debug.f( ‘Going to return the predicate "%s"’, l_predicate );
     return l_predicate;
end;

So, debug.f works similar to the C printf function and is implemented using UTL_FILE. It creates programmer managed trace files on the database server. These trace files contain your debug statements – things you can use to see what is happening in your code. Since the database kernel is invoking your code, debugging it can be hard. Having these trace files can save lots of time. The scripts you can download (see below for that link) contain this debug package and comments on setting it up and using it.

Pros and Cons

There are many Pros to this feature and very few Cons. In fact, it is hard to think of any Cons to this feature at all. In any event here they are:

Pros

Cons

Simplifies Application development – separates access control from the application and puts it with the data.

May be difficult to debug as Fine Grained Access Control happens in the background. Packages such as ‘debug’ referred to in the diagnosing and debugging section above make this much easier.

Ensures data in the database is always protected. No matter what tool accesses the data we are ensured our security policy is invoked and cannot be bypassed.

 

Allows for evolutionary changes to security policies with no impact on client applications.

 

Simplifies the management of database objects. It reduces the total number of database objects needed to support an application.

 

It performs well. The use of the application contexts allow us to reap the benefits of shared SQL.

 

 

Scripts

Download this tar file to get all scripts used in this article. Please be sure to read the README.TXT file included within. Tar files may be opened on Windows using WinZip6.0 or above.

Next Time…

In the next issue, we will explore the new database features: