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:
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. |
There are many reasons to use this feature. Some of the most prevalent ones are:
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:
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.
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 worksSQL> 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.
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:
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
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_ROLESQL> 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 contextSQL> 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 stuffSQL> 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 managerSQL> 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 RepSQL> 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.
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.
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. |
|
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.
In the next issue, we will explore the new database features: