Row level access security on Oracle Database

Translate: a powerful Oracle PL/SQL function enabling fine grained security on a row level access

Chapter 1 : The security problem

This technical note deals with security aspects in the access control area. It's my own oracle implementation ensuring that user view data are explicitly granted to them on a row level, on any table. There is of course many methods to enforce a proper security policy. This is a few of them as a reminder:

1 Access control using the mainframe

2 On the Unix side

3 On the oracle side

4 A need for better control

Even with all those methods, access control to a database is sometimes too Boolean. Basically you can see a table or you can't see it. The problem arises when you want to give a partial view to a table. A distributor can see the client he owns but he mustn't see other clients accounts. On the other hand, your company wants to manage all the clients.

The only centralised, manageable and secured solution is to grant rights on the row level.

Chapter 2 : A presentation of the access control method

Even though access right is managed on the table level, there are real difficulties to grant rights on the row level. You can use Trusted Oracle to do the job, but:

It is not available in every country due to a restraint on US security export laws

It's no longer maintained by Oracle after version 7.2

But you can mimic the same basic function using the SQL function "translate" used as an operator.

Access control are maintained using a 2D matrices: Role and Level.

You can grant the following rights on a specific row like this:
 
Role Exploitation Technical Admin. Commercial Teller
Level Required User Administrator User
Those rights are explicitly given: if any user doesn't have the role required and minimum level in that role, he won't see the row (to update it or even to select it).

For example one application might require the following roles :

Marketing
Sales,
Sales Support,
Teller,
Production,
Hot line,
Accounting,
IS On the other hand, such application would require this security clearance level. Public
Client
Internal
Partner
User
Expert
Administrator
Super User One user will have the following role and level attributes:

i.e. :
 
Role Marketing Sales Teller Production Sales support Accounting IS
Level granted User User Expert User Administrator Expert Internal

Chapter 3 : "Translate"; a sql function that can be used as an operator

translate have the following properties:

SQL> def x='abefg' SQL> def y='fbcd' filter :
translate(´abc´,´ a´,´ ´) -> ´bc´
filter a group from a string : SQL> / old 1: select translate('&&x','a',' ') from dual new 1: select translate('abefg','a',' ') from dual TRAN ---- befg minus(x,y)
translate(x,´ ´||y,´ ´)
delete all the tuples y from the x set

SQL> select translate('&&x',' '||'&&y',' ') from dual; old 1: select translate('&&x',' '||'&&y',' ') from dual new 1: select translate('abefg',' '||'fbcd',' ') from dual TRA --- aeg

complement(x)

translate(alphabet,´ ´||x,´ ´)
will extract the complement of x=> every letter except the one included in the x set SQL> def alphabet='abcdefghijklmnopqrstuvwxyz' SQL> select translate('&&alphabet',' '||'&&x',' ') from dual; old 1: select translate('&&alphabet',' '||'&&x',' ') from dual new 1: select translate('abcdefghijklmnopqrstuvwxyz',' '||'abefg',' ') from dual SQL> col complement_x new_value complement_x 1* select translate('&&alphabet',' '||'&&x',' ') complement_x from dual SQL> / old 1: select translate('&&alphabet',' '||'&&x',' ') complement_x from dual new 1: select translate('abcdefghijklmnopqrstuvwxyz',' '||'abefg',' ') complement_x from dual COMPLEMENT_X --------------------- cdhijklmnopqrstuvwxyz
SQL> col complement_y new_value complement_y SQL> select translate('&&alphabet',' '||'&&y',' ') complement_y from dual; old 1: select translate('&&alphabet',' '||'&&y',' ') complement_y from dual new 1: select translate('abcdefghijklmnopqrstuvwxyz',' '||'fbcd',' ') complement_y from dual COMPLEMENT_Y ---------------------- aeghijklmnopqrstuvwxyz

intersection :
intersection(x,y)= translate(x,y||complement(y),y)
return all the tupples belonging to both set

which can be rewritten as

intersection(x,y)=translate(x,´ ´||complement(y),´ ´)

SQL> select translate('&&x',' '||'&&complement_y',' ') intersection from dual; old 1: select translate('&&x',' '||'&&complement_y',' ') intersection from dual new 1: select translate('abefg',' '||'aeghijklmnopqrstuvwxyz',' ') intersection from dual IN -- bf sort : sort the common tuples of x and y following the order of y:
sort(y,x)=translate(x,y||complément(y),y)
Let say:
  • extraction of an ACL
  • y looks like : ´acd´
  • right is: ´142´
  • x looks like ´dae´
translate(sort(x,y),y,right) gives ´12´
which is the same as
translate(y,intersection(y,y),right) which gives ´12´ Matching

1) determination of Y privileges

SQL> select translate('&&intersection','&&y','&&right_y') from dual; old 1: select translate('&&intersection','&&y','&&right_y') from dual new 1: select translate('bf','fbcd','421') from dual TR -- 24
SQL> col sort new_value sort SQL> select translate('&&alphabet',' '||'&&intersection',' ') sort from dual; old 1: select translate('&&alphabet',' '||'&&intersection',' ') sort from dual new 1: select translate('abcdefghijklmnopqrstuvwxyz',' '||'bf',' ') sort from dual SORT ------------------------ acdeghijklmnopqrstuvwxyz
SQL> col intersect_sort_y new_value intersect_sort_y SQL> c/from/intersect_sort_y from/ 1* select translate('&&y',' '||'&&tri',' ') intersect_sort_y from dual SQL> / old 1: select translate('&&y',' '||'&&sort',' ') intersect_sort_y from dual new 1: select translate('fbcd',' '||'acdeghijklmnopqrstuvwxyz',' ') intersect_sort_y from dual IN -- fb
SQL> def right_y='7654' SQL> col granted_y new_value granted_y SQL> select translate('&&intersect_sort_y',' '||'&&y',' '||'&&right_y') granted_y from dual 2 ; old 1: select translate('&&intersect_sort_y',' '||'&&y',' '||'&&right_y') granted_y from dual new 1: select translate('fb',' '||'fbcd',' '||'7654') granted_y from dual GR -- 76

2) X privileges determination

SQL> col granted_x new_value granted_x SQL> select translate('&&intersection',' '||'&&x',' '||'&&droit_x') granted_x from dual; old 1: select translate('&&intersection',' '||'&&x',' '||'&&droit_x') granted_x from dual new 1: select translate('bf',' '||'abefg',' '||'93400') granted_x from dual GR -- 30

3) Output

DEFINE _EDITOR = "/bin/vi" (CHAR) DEFINE _O_VERSION = "Oracle7 Server Release 7.3.4.0.0 - Production With the parallel query option PL/SQL Release 2.3.4.0.0 - Production" (CHAR) DEFINE _O_RELEASE = "703040000" (CHAR) DEFINE X = "abefg" (CHAR) DEFINE Y = "fbcd" (CHAR) DEFINE _RC = "0" (CHAR) DEFINE ALPHABET = "abcdefghijklmnopqrstuvwxyz" (CHAR) DEFINE COMPLEMENT = "cdhijklmnopqrstuvwxyz" (CHAR) DEFINE INTERSECTION = "bf" (CHAR) DEFINE COMPLEMENT_Y = "aeghijklmnopqrstuvwxyz" (CHAR) DEFINE COMPLEMENT_X = "cdhijklmnopqrstuvwxyz" (CHAR) DEFINE RIGHT_X = "93400" (CHAR) DEFINE RIGHT_Y = "7654" (CHAR) DEFINE SORT = "acdeghijklmnopqrstuvwxyz" (CHAR) DEFINE INTERSECT_SORT_Y = "fb" (CHAR) DEFINE GRANTED_X = "30" (CHAR) DEFINE GRANTED_Y = "76" (CHAR)

Chapter 4 : The Next step

It's quite easy to write a PL/SQL function comparing the ACL, to base a view on such  a function, to associate letters to people, group and habitation level. The last step is to base a Forms 5 on this view. Such screen works perfectly under select/insert/update/query mode.


Last Update : $Date: Dec 02 2001 23:34:54 $