Fine Grained Access Control in Oracle 11g

Introduction

In this article, I’ll demonstrate simple implementation of Fine Grained Access Control (FGAC) in Oracle 11g.
FGAC is based on dynamically (at execution time) modified SQL statements (primarily DMLs). FGAC feature allows us to define a function (user defined PL/SQL) that returns a predicate as a string.
When this function is applied as policy to table (or views / synonyms), Oracle at execution time will append (as AND) to the query issued by users.

When a query is issued on a table on which FGAC policy is applied (and enabled)…

·         If the predicate evaluates to TRUE (at run time) the data will be returned
·         If the predicate evaluates to FALSE the data will NOT be returned
HOWEVER
·         If the function returns NULL, no predicate will be applied and the result of the query will be returned.

I’ll use an example, outlined below, to demonstrate this feature.

On account of simplicity, I have used a very simple function. The predicate returned from the function can be much more complex.

Sample Requirement
1.    There is a table with following definition and data.

  • a.    Table T is owned by user HR
  • b.    Table T has 10 rows, with c1 value from 1 through 10.

2.    There are three users A, B and C who need selective access to the data in the table.

  • a.    User A access see only rows which have EVEN c1 value.
  • b.    User B access see only rows which have ODD c1 value.
  • c.    All other users (including C) can access ALL data in the table.

3.    Requirement is to enforce the following access rules at database level with minimal changes to the application components.

Required Setup

The following scripts can be used for setup.

Environment:

SQL> select * from v$version where rownum < 4;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
PL/SQL Release 11.1.0.7.0 – Production
CORE    11.1.0.7.0      Production
SQL> show user;
USER is “HR”
SQL>

Create & load table:

SQL> drop table T;
drop table T
           *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table hr.T (c1 int, c2 varchar2(20));
Table created.
SQL> insert into T select level c, ‘AAAA’ c2 from dual connect by level < 11;
10 rows created.
SQL> commit;
Commit complete.

Create users as SYSDBA:

SQL> connect / as sysdba;
Connected.
SQL> create user a identified by a;
User created.
SQL> create user b identified by b;
User created.
SQL> create user c identified by c;
User created.
SQL> grant create session to a;
Grant succeeded.
SQL> grant create session to b;
Grant succeeded.
SQL> grant create session to c;
Grant succeeded.
SQL>

Grant SELECT privilege on T to users (as HR):

SQL> connect hr/hr;
Connected.
SQL> grant select on T to a;
Grant succeeded.
SQL> grant select on T to b;
Grant succeeded.
SQL> grant select on T to c;
Grant succeeded.
SQL>

With this setup completed, all three users can access all the rows in table T.

SQL> connect a/a
Connected.
SQL> select count(*) from hr.t;
  COUNT(*)
———-
        10
SQL> connect b/b;
Connected.
SQL> select count(*) from hr.t;
  COUNT(*)
———-
        10
SQL> connect c/c;
Connected.
SQL> select count(*) from hr.t;
  COUNT(*)
———-
        10
SQL>

 Create and apply FGAC policy function

 Create Policy Function:

SQL> CREATE OR REPLACE FUNCTION hr.t_policy
     (oowner IN VARCHAR2, ojname IN VARCHAR2)
    RETURN VARCHAR2
    AS
    BEGIN
      case
        when user = ‘A’ then
          RETURN ‘ mod(hr.T.c1,2) = 1 ‘;
        when user = ‘B’ then
          RETURN ‘ mod(hr.T.c1,2) = 0 ‘;
    else
         RETURN null;
     end case;
   END;
   /
Function created.
SQL>

Apply policy to table T:
Note: HR user did not have right privilege to execute DBMS_RLS package, so we got error PLS-00201. Granting this privilege as shown may not be a great idea.
SQL>exec DBMS_RLS.ADD_POLICY
      (object_schema=>’HR’,
       object_name=>’T’,policy_name=>’tp’,
       function_schema=>’HR’,
       policy_function=>’T_POLICY’);
BEGIN
DBMS_RLS.ADD_POLICY
      (object_schema=>’HR’,
       object_name=>’T’,policy_name=>’tp’,
       function_schema=>’HR’,
       policy_function=>’T_POLICY’);
END;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier ‘DBMS_RLS’ must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> connect / as sysdba;
Connected.
SQL> grant execute on dbms_rls to hr;
Grant succeeded.
SQL> exec DBMS_RLS.ADD_POLICY
     (object_schema=>’HR’,
      object_name=>’T’,
      policy_name=>’tp’,
      function_schema=>’HR’,
      policy_function=>’T_POLICY’);
PL/SQL procedure successfully completed.
SQL>

Verify the policy:

SQL> select object_owner, policy_name, function from all_policies;
OBJECT_OWNER                   POLICY_NAME               FUNCTION
—————————— ————————- —————-
HR                             TP                        T_POLICY
SQL>

Verify the Solution

The following section show the result as per the requirement.

SQL> connect a/a;
Connected.
SQL> /
        C1 C2
———- ——————–
         1 AAAA
         3 AAAA
         5 AAAA
         7 AAAA
         9 AAAA
SQL> connect b/b;
Connected.
SQL> /
        C1 C2
———- ——————–
         1 AAAA
         3 AAAA
         5 AAAA
         7 AAAA
         9 AAAA
SQL> connect c/c;
Connected.
SQL> /
        C1 C2
———- ——————–
         1 AAAA
         2 AAAA
         3 AAAA
         4 AAAA
         5 AAAA
         6 AAAA
         7 AAAA
         8 AAAA
         9 AAAA
        10 AAAA
10 rows selected.
SQL>

 We can drop the policy using DROP_POLICY function:

SQL> connect hr/hr;
Connected.
SQL>exec dbms_rls.drop_policy(
         object_schema=>’HR’,
         object_name=>’T’,
         policy_name=>’tp’);
PL/SQL procedure successfully completed.
SQL>


With RLS policy not in effect now, all users can see all the rows from table T.

Row Level Security (RLS) can be enforced in production database with minimal changes to application code.
Till next time Bye!

Cleanup of DEMO objects

SQL> connect / as sysdba;
Connected.
SQL> drop user a cascade;
User dropped.
SQL> drop user b cascade;
User dropped.
SQL> drop user c cascade;
User dropped.
SQL> drop table hr.t;
Table dropped.
SQL> drop function hr.t_policy;
Function dropped.
SQL>

References

FGAC Documentation

http://docs.oracle.com/cd/B12037_01/network.101/b10773/apdvcntx.htm#1007183

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s