Data Management
Project Management
Archived stuff
Cryptic crossword.
A bit about me.
Read my blog
Recent additions
Home
Oracle Articles

Oracle Fine Grained Access Control:

Applications often require row-level access control to data - i.e. users should be able to see only rows that they are authorized to. As a concrete example: consider an order processing system where a number of clerks enter orders into a single table. In such a situation one would expect that a clerk should see only her orders, and have no access to those entered by her colleagues. The traditional way to deal with this is to use views. These views can be set up to filter data based on the user name or, in more elaborate cases, through a join with a permissions table. Oracle offers a better way to achieve this through its built-in row level security (RLS) mechanism (also known as fine grained access control or FGAC). This article is an introduces FGAC, through a non-trivial, yet simple, example.

Row level security essentially rests on setting an application role automatically when the user logs in, and then setting an appropriate SQL predicate based on the role. The best way to see this working is to walk through an example. So here we go:

Our example is based on the situation mentioned in the opening lines of this article. A bunch of clerks enter orders into an ORDER_TABLE. Clerks can see and modify only their orders - which are flagged by their userid and department. Department heads form another class of users. Heads can manipulate orders belonging to any clerk in their department. The tables DEPT_HEAD and DEPT_CLERK maintain application user (heads and clerks) information. These tables are not accessed by directly by end users; they are used to build SQL predicates (this will be made clearer later). With this background we can now move on to the nitty-gritty details.

Login to Oracle as any user with DBA privileges and create the following users:

--user TEST owns the application

create user test identified by test;

--grant permissions to test

grant connect, resource to test;
grant create any context to test;
grant administer database trigger to test;

--the following privilege may need to be granted by SYS

grant execute on dbms_rls to test;

--ALLEN and WARD are application users
create user allen identified by allen;

grant connect to allen;

create user ward identified by ward;

grant connect to ward;

Notes:
1. User TEST needs ADMINISTER DATABASE TRIGGER privilege in order to create a trigger that will fire whenever a user logs into the database. The trigger will setup the application security. More on this later
2. User TEST needs execute on the built-in package DBMS_RLS in order to bind the security policy to the table in question - ORDER_TABLE in our case. Again, this will become clearer below.
3. ALLEN and WARD are application users - we'll use these ids to check that the row level security we set up actually works.

Next we create the application schema and populate the tables. To do this, login as the application owner - i.e . the user TEST and execute the following:

--main table to store orders

create table order_table
(order_id number,
dept_id number,
description varchar2(64),
request_date date,
completion_date date,
username varchar2(32) default user,
primary key (order_id));

--application lookup table to store department heads

create table dept_head
(dept_id number,
head_name varchar2(32),
primary key (dept_id));

--application lookup table to store department clerks

create table dept_clerk
(dept_id number,
clerk_name varchar2(32),
primary key(dept_id, clerk_name));

--populate ORDER_TABLE

insert into ORDER_TABLE
values
(1, 20, 'CUSTOMER 1', sysdate, sysdate+1,'ADAMS');

insert into ORDER_TABLE
values
(2, 30, 'CUSTOMER 2',sysdate, sysdate+1,'ALLEN');

insert into ORDER_TABLE
values
(3, 30, 'CUSTOMER 3', sysdate, sysdate+1,'ALLEN');

insert into ORDER_TABLE
values
(4, 20, 'CUSTOMER 4', sysdate, sysdate+1,'ADAMS');

insert into ORDER_TABLE
values
(5, 30, 'CUSTOMER 5', sysdate, sysdate+1,'ALLEN');

insert into ORDER_TABLE
values
(6, 30, 'CUSTOMER 6', sysdate, sysdate+1,'ALLEN');

insert into ORDER_TABLE
values (7, 10, 'CUSTOMER 7', sysdate, sysdate+1,'CLARK');

insert into ORDER_TABLE
values
(8, 20, 'CUSTOMER 8', sysdate, sysdate+1,'ADAMS');

insert into ORDER_TABLE
values
(9, 10, 'CUSTOMER 9', sysdate, sysdate+1,'CLARK');

insert into ORDER_TABLE
values
(10, 30, 'CUSTOMER 10',sysdate, sysdate+1,'ALLEN');

insert into ORDER_TABLE
values
(11, 20, 'CUSTOMER 11', sysdate, sysdate+1,'ADAMS');

insert into ORDER_TABLE
values
(12, 30, 'CUSTOMER 12', sysdate, sysdate+1,'ALLEN');

insert into ORDER_TABLE
values
(13, 20, 'CUSTOMER 13',sysdate, sysdate+1,'ADAMS');

insert into ORDER_TABLE
values
(14, 10, 'CUSTOMER 14', sysdate, sysdate+1,'CLARK');

insert into ORDER_TABLE
values
(15, 30, 'CUSTOMER 15',sysdate, sysdate+1,'THOMPSON');

insert into ORDER_TABLE
values
(16, 30, 'CUSTOMER 16', sysdate, sysdate+1,'THOMPSON');

--populate DEPT_HEAD

insert into DEPT_HEAD
values
(10, 'MILLER');

insert into DEPT_HEAD
values
(20, 'SMITH');

insert into DEPT_HEAD
values
(30, 'WARD');

--populate DEPT_CLERK

insert into DEPT_CLERK
values
(10, 'CLARK');

insert into DEPT_CLERK
values
(20, 'ADAMS');

insert into DEPT_CLERK
values
(30, 'ALLEN');

insert into DEPT_CLERK
values
(30, 'THOMPSON');

--don't forget the commit!

commit;

ORDER_TABLE is the only table that will be accessed by end users of the application. It contains details of orders entered by clerks and heads. The tables DEPT_HEAD and DEPT_CLERK are supporting tables, used only by the security framework - more on this below.

The next step is to create a procedure that sets the appropriate application role when any user logs on to the database. The procedure defines the following roles: APP_USER (corresponding to a clerk), APP_ADMIN (corresponding to a dept head), APP_OWNER (corresponding to the application owner -i.e. the user TEST) and NOT_AUTHORIZED (corresponding to any other user). These roles are defined within the context of the present application. The context itself is created after the procedure, since we want to ensure that context variable values can be set only through the procedure. Here's the procedure code - it should be compiled in the TEST schema:

create or replace procedure set_testapp_role(p_user varchar2 default sys_context('userenv', 'session_user')) is

--the username can be passed as a parameter, if not we use the built-in function sys_context()
--to set a default username corresponding to the current session user

--v_ctx holds the application context name. The context itself (testapp_ctx,)
--is created in the next step (following this procedure).

v_ctx varchar2(16) 'testapp_ctx';

v_is_head number;
v_dept_id number;
v_is_clerk number;

begin

--set the context variable "username" to current user

dbms_session.set_context(v_ctx, 'username', p_user);

select
count(*)
into
v_is_head
from
dept_head
where
head_name=p_user;

select
count(*)
into
v_is_clerk
from
dept_clerk
where
clerk_name=p_user;

--if the user is TEST, then assign the role APP_OWNER (remember that this
--procedure is owned by TEST, so, within the procedure, the current schema
--is always TEST, irrespective of who is executing the procedure).

if (p_user=sys_context('userenv','current_schema')) then

dbms_session.set_context(v_ctx,'rolename','APP_OWNER');

elsif (v_is_head=1) then

--if the user is a dept head, assign the APP_ADMIN role and capture the
--department id

select
dept_id
into
v_dept_id
from
dept_head
where
head_name=p_user;

dbms_session.set_context(v_ctx,'rolename','APP_ADMIN');
dbms_session.set_context(v_ctx,'deptid',v_dept_id);

elsif (v_is_clerk=1) then

--if the user is a cleark, assign the APP_USER role

dbms_session.set_context(v_ctx,'rolename','APP_USER');

else

--the user is not authorised to access the application

dbms_session.set_context(v_ctx,'rolename','NOT_AUTHORIZED');

end if;

end;


Next we create a new context, testapp_ctx, whose parameters can be set only through the above procedure:

create or replace context testapp_ctx using set_testapp_role;

The "using set_testapp_role" clause binds the context to the procedure ensuring that context parameters cannot be set via any other method.

Next we set up a database logon trigger that executes set_testapp_role whenever a new session is initiated:

create or replace trigger test_logon_trigger after logon on database

begin
set_testapp_role;
end;

Now whenever a user logs on to the database, set_testapp_role will be executed and the appropriate role will be set.

The next step is to create a security function that sets the appropriate "where" clause to restrict access to ORDER_TABLE, the clause being determined by the role that the user has in the application context. This, again, is compiled in the TEST schema

create or replace function testapp_security_function (p_schema varchar2, p_object varchar2) return varchar2 is

begin

if (sys_context('testapp_ctx','rolename')='APP_OWNER') then

--no where clause - i.e. the app owner should be able to see all
--rows in ORDER_TABLE

return '';

elsif (sys_context('testapp_ctx','rolename')='APP_ADMIN') then

--where clause restricts APP_ADMIN accessible rows to those entered
--by head or by clerks in head's dept

return 'dept_id=sys_context(''testapp_ctx'',''deptid'') and '||
'(username in (select clerk_name from dept_clerk where '||
'dept_id=sys_context(''testapp_ctx'',''deptid'')) or username='||
'(select head_name from dept_head where dept_id=sys_context(''testapp_ctx'',''deptid'')))';

elsif ( sys_context('testapp_ctx','rolename')='APP_USER') then

--where clause restricts APP_USER accessible rows to those entered
--by the given clerk

return 'username=sys_context(''testapp_ctx'',''username'') and '||
'dept_id = (select dept_id from dept_clerk
where clerk_name=sys_context(''testapp_ctx'',''username''))';

else

--where clause returns no rows if user is NOT_AUTHORIZED

return '1=2';

end if;

end;

The embedded comments explain each clause returned by the security function. The above code also makes clear the necessity of the lookup tables DEPT_CLERK and DEPT_HEAD - these are used only by the security function, and are never accessed directly by any users.

The final step in implementing RLS is to associate the security function with ORDER_TABLE, so that the appropriate predicate is dynamically created whenever the table is accessed. This is done using the DBMS_RLS package. We have already granted TEST execute on this package. In the code below, the procedure is executed in an anonymous PL/SQL block. You can also execute it directly in SQL Plus.

declare

begin

DBMS_RLS.ADD_POLICY (
object_schema => 'TEST',
object_name => 'ORDER_TABLE', policy_name => 'TESTAPP_POLICY',
function_schema => 'TEST',
policy_function => 'TESTAPP_SECURITY_FUNCTION',
statement_types => 'SELECT,UPDATE,INSERT,DELETE',
update_check => TRUE,
enable => TRUE,
static_policy => FALSE);

end;

This binds the testapp_security_function to ORDER_TABLE for the specified statement types. Now whenever any user accesses ORDER_TABLE, a where clause returned by testapp_security_function is automatically tacked on to the DML statement! By setting UPDATE_CHECK to TRUE, we ensure that users can only modify, add or delete rows that they can or will be able to see. Note that, for more complex security requirements, one can have separate security functions for SELECT, INSERT, UPDATE and DELETE statements. The most convenient way to do this is to bundle the functions in a package.

Now to test that all this really works. Login as TEST and grant the following privileges on ORDER_TABLE to ALLEN and WARD.

grant select, insert, update, delete on order_table to allen;

grant select, insert, update, delete on order_table to ward;

Next, log on as ALLEN (clerk), and issue the following SQL statements:
--this returns only those rows accessible to ALLEN
select * from test.order_table;

--this insert will not work - wrong user
insert into test.order_table
values (10000, 30, 'CUSTOMER 212',sysdate, null,'THOMPSON');

--neither will this one - wrong user
insert into test.order_table values
(10000, 30, 'CUSTOMER 212',sysdate, null,'WARD');

--nor this one - wrong user, dept
insert into test.order_table values
(10000, 20, 'ORDER FOR CUSTOMER 212',sysdate, null,'ADAMS');

--this one works - function user returns 'ALLEN' insert into test.order_table values (10000, 30, 'ORDER FOR CUSTOMER 212',sysdate, null,user);

--this one doesnt work - wrong dept
insert into test.order_table values
(10001, 20, 'ORDER FOR CUSTOMER 212',sysdate, null,'ALLEN');

--this delete doesnt work - row exists but cannot be accessed by ALLEN
delete from test.order_table where order_id =16;

--this delete works
delete from test.order_table where order_id =2;

Finally, log on as WARD (dept head) and issue the following SQL:

--this returns data for dept 30 only
select * from test.order_table;

--this insert doesnt work - wrong dept insert into test.order_table values
(10002, 20, 'CUSTOMER 212',sysdate, null,user);

--this one works
insert into test.order_table values
(10002, 30, 'CUSTOMER 212',sysdate, null,user);

--this insert works - WARD can impersonate her subordinate
insert into test.order_table values
(10003, 30, 'CUSTOMER 212',sysdate, null,'ALLEN');

This completes our demonstration of Oracle's row-level security framework. Note that RLS applies to all users who log on to the database except SYS, SYSTEM and those granted the system privilege EXEMPT ACCESS POLICY. Since RLS is implemented right in the database, it is extremely secure, and you are guaranteed that normal users cannot bypass it, whatever be the access method they choose.

Back to the top