Security – Oracle Way – FGAC

A good database security is that … a user must be able to access only what he is authorized to view ..and nothing more than that.

But, How this can be implemented?

Well!!! you can do so by appending a simple filtering predicate in the application queries – but practically this is impractical as there are large number of SQL statements inside the application. Another problem is that – what if a user directly access the database wihtout using the application itself, then your filtering technique becomes useless, allowing him to access full data.

So, there must be some kind of solution where the filtering predicate will be added to the statement(s) automatically, however the access is made i.e either through the application or direct access to the database.

The perfect solution comes from Oracle’s implementation of Fine Grained Access Control (FGAC) which is also known as Row Level Security (RLS) or Virtual Private Database (VPD). Now lets see what exactly this ‘FGAC’ means –


FGAC allows an existing database to be partitioned into several virtual databases that can be shown differently to different users. Oracle dynamically rewrites the query by appending the predicate to users SQL statements.

Here the question arises, will it be the same ‘predicate’ that will be appended to all sql queries?? Well the answer is ‘It depends’ – now another question arises .. ‘On what?’ – answer is ‘Application Context’ values – Then what’s this ‘Application Context’ ?

An Application Context is a mechanism for storing security related information (username, ipaddress, how user connected to database…) in the form of name – value pairs. This information is held in memory which are retrived by users and applications. This information is used by the Secuity Policies in controlling user access and build the run-time ‘where clause’ to append to the end user’s SQL statement. Each session/user will have different ‘Application Context’ values and so will have diffent results for the same SQL query.

Let us see with an example for better understanding of what we discussed till now –

For our understanding purpose – I created 3 database users SCHEMA_OWNER, SCHEMA_USER1, SCHEMA_USER2 – Assume that SCHEMA_OWNER holds all the relevant objects(our case only one table SCHEMA_INFO) for the application –

           SQL> select user from dual;

           SQL> select * from tab;

           TNAME                          TABTYPE  CLUSTERID
           ------------------------------ ------- ----------
           SCHEMA_INFO                    TABLE

           SQL> desc schema_info
            Name                     Null?    Type
           ----------------------------------------- -------
            SNO                      NOT NULL NUMBER
            SNAME                    NOT NULL VARCHAR2(20)
            SAL                               NUMBER(7,2)

        SQL> select * from schema_info;

           SNO SNAME                       SAL
           ---------- -------------------- ----------
            1 SCHEMA_OWNER              10000
            2 SCHEMA_USER1               6000
            3 SCHEMA_USER2               4000

So, without any security in place, the users SCHEMA_USER1 & SCHEMA_USER2 will display the same result for the above query (select * from schema_info) – But our intention is to display only those records that a user is intented to see, for the same query – If that kind of security is in place then, USER1 & USER2 will have different results for the same query, which means they only can retrieve the records that they are intended for.

Let us see how this happens – Here SCHEMA_OWNER wants to secure the data in the objects which he owns. In that sense he creates a ‘POLICY FUNCTION’, which will guard the objects that SCHEMA_OWNER owns. When an end user tries to access to any of SCHEMA_OWNER’s objects – the POLICY FUNCTION that was created for that object will get executed and generates a ‘PREDICATE’ which will be appended to the end user’s SQL Statement. Thus making the users to retrieve only what they are intended for.

Now lets create a policy function for the table SCHEMA_INFO that belongs to SCHEMA_OWNER –

SQL>connect schema_owner/schemaowner

SQL>create or replace function get_sname(schema_name in varchar2,
  schema_obj in varchar2) return varchar2 is
    s_name varchar2(1000);
    temp_name varchar2(100);
  select user into temp_name from dual;
     if (upper(schema_name) = temp_name) then
	s_name := ' 1 = 1';
         s_name:=' upper(sname) like sys_context(''userenv'',
     end if;

     return s_name;

Note:: All Policy functions are passed with two parameters by
       the database when they are invoked. The first is the
       name of the schema that owns the object(SCHEMA_OWNER)
       and the second is the name of the object(SCHEMA_INFO)
       for which the RLS policy is being invoked.

Once the fucntion is created, we need to mention what actions (like SELECT, INSERT, UPDATE ) will invoke this function – This can be done by adding a policy as shown –

SQL>connect schema_owner/schemaowner

object_schema => 'SCHEMA_OWNER',
object_name => 'SCHEMA_INFO',
policy_name => 'SCH_POLICY',
policy_function => 'GET_SNAME',
function_schema => 'SCHEMA_OWNER',
statement_types => 'SELECT, INSERT, UPDATE, DELETE',
update_check => true,
enable => true

Note ::  Whenever user tries to perform an action on SCHEMA_INFO,
         security policy modifies the user's SQL by adding a
         PREDICATE, that confines access to specific rows within the
         target tables.

Now lets us test what will be the result by running the same query for differnt users and what will be the result sets –

SQL>connect schema_owner/schemaowner

SQL> select * from schema_info;
       SNO SNAME                       SAL
---------- -------------------- ----------
         1 SCHEMA_OWNER              10000
         2 SCHEMA_USER1               6000
         3 SCHEMA_USER2               4000


SQL>connect schema_user1/schemauser1

SQL> select * from schema_owner.schema_info;
       SNO SNAME                       SAL
---------- -------------------- ----------
         2 SCHEMA_USER1               6000


SQL>connect schema_user2/schemauser2

SQL> select * from schema_owner.schema_info;
       SNO SNAME                       SAL
---------- -------------------- ----------
         3 SCHEMA_USER2               4000


So, they are diffent result sets for diffent users
for the same query. So, a predicate will be added
automatically whenever a user performs an action
on the table.

If we query the table v$vpd_policy we can see what
those predicates are - 

SQL>connect / as sysdba

SQL> select predicate from v$vpd_policy;
1 = 1
upper(sname) like sys_context('userenv','session_user')


In this way we can implement Security on the databse objects using FGAC.

This entry was posted in Oracle, Security. Bookmark the permalink.

Leave a Reply

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

You are commenting using your 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