Fine-Grained Access Control (FGAC) hands-on

Posted on , updated on by Yannick Jaquier
Oracle , , ,

Table of contents

Preamble

How would you provide a selective access to columns and rows of one table to multiple database or applicative users ? One complex solution is to create a set of views and synonyms on this master tables and manage selective grants to those views and synonyms. But still the issue would be for applicative users as set of views and grants is restricted to pure Oracle accounts. Oracle answer to this problem is Virtual Private Database (VPD) or fine-grained access control (FGAC) that through policies and procedures will dynamically add predicates to all queries and with application contexts can recover applicative session properties.

FGAC is made of policies on objects and procedures that automatically and dynamically add predicates to queries. No need to say that execution time of these procedures is key in FGAC performance…

In most real life situation end users never connect directly to databases with their own dedicated Oracle user. A JDBC pooling connection is often initialized and all database sessions are made with same Oracle applicative user that has rights (select, update, delete) on Oracle data model owner account. Application identification is often home made but for convenience quite often rely on an enterprise LDAP authentication.

Then how to identify at database level who is the real end user accessing the figures and how to mix it with FGAC ?

I had difficulties to find it but it is there black on white in Official documentation in Preserving User Identity in Multitiered Environments chapter:

Using a Middle Tier Server for Proxy Authentication
Using Client Identifiers to Identify Application Users Not Known to the Database

Using proxy authentication would not provide, to me, enough security. As for example you behave into database as if you are end users, so initial complexity to model security is still there:

SQL> CREATE USER test3 IDENTIFIED BY test3;
 
USER created.
 
SQL> GRANT  CONNECT TO test3;
 
GRANT succeeded.
 
SQL> ALTER USER test1 GRANT CONNECT through test3;
 
USER altered.
 
SQL> SET lines 150
SQL> SELECT * FROM proxy_users;
 
PROXY                          CLIENT                         AUT FLAGS
------------------------------ ------------------------------ --- -----------------------------------
TEST3                          TEST1                          NO  PROXY MAY ACTIVATE ALL CLIENT ROLES
 
SQL> CONNECT test3[test1]/test3
Connected.
SQL> show USER
USER IS "TEST1"
SQL> SELECT * FROM employees;
 
        ID FIRSTNAME                                          LASTNAME                                               SALARY
---------- -------------------------------------------------- -------------------------------------------------- ----------
         1 Yannick                                            Jaquier                                                 10000
 
SQL> SELECT SYS_CONTEXT('USERENV', 'PROXY_USERID') FROM DUAL;
 
SYS_CONTEXT('USERENV','PROXY_USERID')
------------------------------------------------------------------------------------------------------------------------------------------------------
96
 
SQL> SELECT SYS_CONTEXT('USERENV', 'PROXY_USER') FROM DUAL;
 
SYS_CONTEXT('USERENV','PROXY_USER')
------------------------------------------------------------------------------------------------------------------------------------------------------
TEST3

Please note this proxy authentication is supported in Oracle Call Interface (OCI), JDBC/OCI, or JDBC Thin Driver..

Anyway using Client Identifiers sounds more logic to me:

SQL> SELECT SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') FROM DUAL;
 
SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')
------------------------------------------------------------------------------------------------------------------------------------------------------
 
 
SQL> EXEC dbms_session.set_identifier('yjaquier');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') FROM DUAL;
 
SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')
------------------------------------------------------------------------------------------------------------------------------------------------------
yjaquier
 
SQL> EXEC SYS.dbms_session.clear_identifier;
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') FROM DUAL;
 
SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')
------------------------------------------------------------------------------------------------------------------------------------------------------

Obviously in this small example I have set up CLIENT_IDENTIFIER for USERENV context with DBMS_SESSION package but you can also set it with OCI, JDBC/OCI, or Thin driver (in Java you would use connection.setClientIdentifier(“yjaquier”); function) ! Anyway we see that using a USERENV context we can create FGAC policies to filter what applicative users will see…

Then come in loop application context, to be honest I had huge difficulties to understand the added value of this SQL object. Concept in itself is really simple:

An application context is a set of name-value pairs that Oracle Database stores in memory. The application context has a label called a namespace. Inside the context are the name-value pairs (an associative array): the name points to a location in memory that holds the value

One context that is well known, as seen above, is USERENV:

SQL> SELECT SYS_CONTEXT('userenv','authenticated_identity') FROM dual;
 
SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
--------------------------------------------------------------------------------
yjaquier

So then what’s the added value of applicative contexts, from Oracle documentation:

Application contexts are useful for the following purposes:

  • Enforcing fine-grained access control (for example, in Oracle Virtual Private Database polices)
  • Preserving user identity across multitier environments
  • Enforcing stronger security for your applications, because the application context is controlled by a trusted procedure, not the user
  • Increasing performance by serving as a secure data cache for attributes needed by an application for fine-grained auditing or for use in PL/SQL conditional statements or loops
  • This cache saves the repeated overhead of querying the database each time these attributes are needed. Because the application context stores session data in cache rather than forcing your applications to retrieve this data repeatedly from a table, it greatly improves the performance of your applications.
  • Serving as a holding area for name-value pairs that an application can define, modify, and access

I easily understand the caching property and the security as the context name-value pairs can be modified only in context package but the real high level added value remains quite dark to me. One big added value I see is if you have a complex procedure (LDAP access and so on) to determine an applicative access control then calling this procedure only once and storing the result in a context has a clear performance benefit. I even rate the examples of application context quite misleading in Oracle official documentation:

Or maybe I have not understood at all the usage of context and unfortunately there are really few references on Internet about them… The context example I tried to setup is with FGAC but we can imagine, as shown in Oracle official documentation, to create a view based on value of an applicative context.

This fine-grained access control and context features are part of OCM exam (!!??). Testing of this blog post has been done using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 running on Oracle Linux Server release 6.4.

Test data model

First let’s check that default tablespaces are correctly set:

SQL> SET lines 150
SQL> SET pages 100
SQL> SELECT * FROM database_properties
     WHERE property_name LIKE 'DEFAULT%TABLESPACE';
 
PROPERTY_NAME
------------------------------
PROPERTY_VALUE
------------------------------------------------------------------------------------------------------------------------------------------------------
DESCRIPTION
------------------------------------------------------------------------------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE
TEMP
Name OF DEFAULT TEMPORARY TABLESPACE
 
DEFAULT_PERMANENT_TABLESPACE
USERS
Name OF DEFAULT permanent TABLESPACE

So USERS and TEMP which is fine, I can use the lazy approach to create users :-).

Then I create two users, one that will own my employees test object (test1) to which I grant execute on Fine-Grained Auditing package (DBMS_FGA) and a second one (test2) that will perform DML activities:

SQL> CREATE USER test1 IDENTIFIED BY test1;
 
USER created.
 
SQL> GRANT CONNECT, RESOURCE TO test1;
 
GRANT succeeded.
 
SQL> GRANT  EXECUTE ON dbms_fga TO test1;
 
GRANT succeeded.
 
SQL> CREATE USER test2 IDENTIFIED BY test2;
 
USER created.
 
SQL> GRANT CONNECT TO test2;
 
GRANT succeeded.

Then I connect with test1 account and create a test table (employees) with a typical confidential field (salary):

SQL> CONNECT test1/test1
Connected.
SQL> CREATE TABLE employees(
     id NUMBER,
     firstname VARCHAR2(50),
     lastname VARCHAR2(50),
     salary NUMBER);
 
TABLE created.
 
SQL> INSERT INTO employees VALUES(1,'Yannick','Jaquier',10000);
 
1 ROW created.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SET lines 130
SQL> SELECT * FROM employees WHERE id=1;
 
        ID FIRSTNAME                                          LASTNAME                                               SALARY
---------- -------------------------------------------------- -------------------------------------------------- ----------
         1 Yannick                                            Jaquier                                                 10000

Then I grant to test2 the update, select capability:

SQL> GRANT SELECT,UPDATE ON employees TO test2;
 
GRANT succeeded.

Fine-Grained Access Control (FGAC) implementation

Start by granting execute privilege on DBMS_RLS to test1:

SQL> GRANT EXECUTE ON dbms_rls TO test1;
 
GRANT succeeded.

Then I create a policy that will add rownum=0 with no criteria:

SQL> show USER
USER IS "TEST1"
SQL> CREATE OR REPLACE FUNCTION not_displaying_salary(object_schema IN VARCHAR2, object_name VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
RETURN 'rownum=0';
END;
/
 
FUNCTION created.

Then let’s add a static policy (criteria is not going to change over time nor by users):

SQL> show USER
USER IS "TEST1"
SQL> EXEC DBMS_RLS.ADD_POLICY(object_name=>'employees',policy_name=>'check_salary',policy_function =>'not_displaying_salary',statement_types=>'select, insert, update, delete',static_policy=>TRUE,sec_relevant_cols=>'salary');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT * FROM user_policies;
 
OBJECT_NAME                    POLICY_GROUP                   POLICY_NAME                    PF_OWNER
------------------------------ ------------------------------ ------------------------------ ------------------------------
PACKAGE                        FUNCTION                       SEL INS UPD DEL IDX CHK ENA STA POLICY_TYPE              LON
------------------------------ ------------------------------ --- --- --- --- --- --- --- --- ------------------------ ---
EMPLOYEES                      SYS_DEFAULT                    CHECK_SALARY                   TEST1
                               NOT_DISPLAYING_SALARY          YES YES YES YES NO  NO  YES YES STATIC                   NO
 
SQL> SELECT * FROM employees;
 
no ROWS selected

The funny thing is that now even the data model owner cannot see anymore the tables figure, for me it is mandatory to grant to data model owner the below system privilege:

SQL> GRANT EXEMPT ACCESS POLICY TO test1;
 
GRANT succeeded.

Then as test2 user, the query returns no row only when the salary column is selected:

SQL> show USER
USER IS "TEST2"
SQL> SELECT * FROM test1.employees;
 
no ROWS selected
 
SQL> SET lines 130
SQL> SELECT id,firstname,lastname FROM test1.employees;
 
        ID FIRSTNAME                                          LASTNAME
---------- -------------------------------------------------- --------------------------------------------------
         1 Yannick                                            Jaquier

Update is also controlled:

SQL> UPDATE test1.employees SET salary=5000 WHERE id=1;
UPDATE test1.employees SET salary=5000 WHERE id=1
             *
ERROR AT line 1:
ORA-01732: data manipulation operation NOT legal ON this VIEW

Here the error message is misleading and many users have encountered it (see Google) and is obviously linked to the weird nature of my security function. The one I would expect is ORA-28115 that I have been able to generate by changing the security function by displaying rows only when salary is below or equal to 10000 (RETURN ‘salary<=10000'). The below update would move rows out of my field of view and the update check option forbid me to perform the update::

SQL> UPDATE test1.employees SET salary=10001;
UPDATE test1.employees SET salary=10001
             *
ERROR AT line 1:
ORA-28115: policy WITH CHECK OPTION violation

One interesting option is to display null value on protected columns instead of not displaying rows at all:

SQL> show USER
USER IS "TEST1"
SQL> EXEC DBMS_RLS.DROP_POLICY(object_name=>'employees',policy_name=>'check_salary');
 
PL/SQL PROCEDURE successfully completed.
 
SQL>  EXEC DBMS_RLS.ADD_POLICY(object_name=>'employees',policy_name=>'check_salary',policy_function =>'not_displaying_salary',statement_types=>'select, insert, update, delete',static_policy=>TRUE,sec_relevant_cols=>'salary',sec_relevant_cols_opt=>DBMS_RLS.ALL_ROWS);
BEGIN DBMS_RLS.ADD_POLICY(object_name=>'employees',policy_name=>'check_salary',policy_function =>'not_displaying_salary',statement_types=>'select, insert, update, delete',static_policy=>TRUE,sec_relevant_cols=>'salary',sec_relevant_cols_opt=>DBMS_RLS.ALL_ROWS); END;
 
*
ERROR AT line 1:
ORA-28104: input VALUE FOR sec_relevant_cols_opt IS NOT valid
ORA-06512: AT "SYS.DBMS_RLS", line 20
ORA-06512: AT line 1
 
SQL> EXEC DBMS_RLS.ADD_POLICY(object_name=>'employees',policy_name=>'check_salary',policy_function =>'not_displaying_salary',statement_types=>'select',static_policy=>TRUE,sec_relevant_cols=>'salary',sec_relevant_cols_opt=>DBMS_RLS.ALL_ROWS);
 
PL/SQL PROCEDURE successfully completed.

Remark:
The DBMS_RLS.ALL_ROWS constant can be used only for a select policy, so here I would be obliged to create two policies to have same initial behavior…

Then as test2 user we now have all rows and salary is set to null when selected:

SQL> show USER
USER IS "TEST2"
SQL> SELECT * FROM test1.employees;
 
        ID FIRSTNAME                                          LASTNAME                                               SALARY
---------- -------------------------------------------------- -------------------------------------------------- ----------
         1 Yannick                                            Jaquier

Fine-Grained Access Control (FGAC) with application context

I grant to test1 (data model owner in my example) the needed privileges, funny thing is that CREATE CONTEXT privilege does not exist, you have to grant CREATE ANY CONTEXT:

SQL> GRANT CREATE CONTEXT TO test1;
GRANT CREATE CONTEXT TO test1
      *
ERROR AT line 1:
ORA-00990: missing OR invalid privilege
 
 
SQL> GRANT CREATE ANY CONTEXT TO test1;
 
GRANT succeeded.

Then I create my applicative context (contexts that can be global or session based) with its associated package. In my context I have chosen my_context1 as namespace and name-value pair will be salary_yes_no that can be set to DISPLAY_SALARY or DO_NOT_DISPLAY_SALARY:

SQL> CREATE OR REPLACE CONTEXT my_context1 USING my_context1_pkg;
 
CONTEXT created.
 
SQL> CREATE OR REPLACE PACKAGE my_context1_pkg IS
 PROCEDURE set_my_context1;
END;
/
PACKAGE created.
 
SQL> CREATE OR REPLACE PACKAGE BODY my_context1_pkg AS
  PROCEDURE set_my_context1 IS
  BEGIN
    CASE LOWER(SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER'))
    WHEN 'yjaquier' THEN
      DBMS_SESSION.SET_CONTEXT('my_context1','salary_yes_no','DISPLAY_SALARY');
    ELSE
      DBMS_SESSION.SET_CONTEXT('my_context1','salary_yes_no','DO_NOT_DISPLAY_SALARY');
    END CASE;
  END set_my_context1;
END;
/
 
PACKAGE BODY created.

Then I grant execute permission on context package to test2 account that is acting as my middletier user:

SQL> GRANT EXECUTE ON my_context1_pkg TO test2;
 
GRANT succeeded.

Then I associate the context to the object I plan to protect:

SQL> EXEC DBMS_RLS.ADD_POLICY_CONTEXT(object_schema=>'test1', object_name=>'employees',namespace=>'my_context1',attribute=>'salary_yes_no');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT * FROM all_POLICY_CONTEXTS;
 
OBJECT_OWNER                   OBJECT_NAME                    NAMESPACE                      ATTRIBUTE
------------------------------ ------------------------------ ------------------------------ ------------------------------
TEST1                          EMPLOYEES                      MY_CONTEXT1                    SALARY_YES_NO

I create a function that will display or not rows based of the name-value pair of my context, if the context is not set properly then nothing is displayed:

CREATE OR REPLACE FUNCTION not_displaying_salary(object_schema IN VARCHAR2, object_name VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
IF SYS_CONTEXT('my_context1','salary_yes_no')='DISPLAY_SALARY' THEN
  RETURN NULL;
ELSE
  RETURN '1=2';
END IF;
END;
/

I create two policy groups, one that display salary and one that does not:

SQL> EXEC DBMS_RLS.CREATE_POLICY_GROUP(object_schema=>'test1',object_name=>'employees',policy_group=>'display_salary');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC DBMS_RLS.CREATE_POLICY_GROUP(object_schema=>'test1',object_name=>'employees',policy_group=>'do_not_display_salary');
 
SQL> SELECT * FROM ALL_POLICY_GROUPS;
 
OBJECT_OWNER                   OBJECT_NAME                    POLICY_GROUP
------------------------------ ------------------------------ ------------------------------
TEST1                          EMPLOYEES                      DISPLAY_SALARY
TEST1                          EMPLOYEES                      DO_NOT_DISPLAY_SALARY

Then I finally add two policy based on policy groups created just above:

SQL> EXEC DBMS_RLS.ADD_GROUPED_POLICY(object_schema=>'test1',object_name=>'employees',policy_group=>'display_salary',policy_name=>'filter_salary',policy_function=>'not_displaying_salary',statement_types=>'select',policy_type=>DBMS_RLS.CONTEXT_SENSITIVE,sec_relevant_cols=>'salary',sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC DBMS_RLS.ADD_GROUPED_POLICY(object_schema=>'test1',object_name=>'employees',policy_group=>'do_not_display_salary',policy_name=>'filter_salary',policy_function=>'not_displaying_salary',statement_types=>'select',policy_type=>DBMS_RLS.CONTEXT_SENSITIVE,sec_relevant_cols=>'salary',sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT object_name,policy_group,policy_name,FUNCTION FROM all_policies;
 
OBJECT_NAME                    POLICY_GROUP                   POLICY_NAME                    FUNCTION
------------------------------ ------------------------------ ------------------------------ ------------------------------
EMPLOYEES                      DISPLAY_SALARY                 FILTER_SALARY                  NOT_DISPLAYING_SALARY
EMPLOYEES                      DO_NOT_DISPLAY_SALARY          FILTER_SALARY                  NOT_DISPLAYING_SALARY

Let’s test with different client identifiers (the driving value to display or not salaries):

SQL> EXEC DBMS_SESSION.SET_IDENTIFIER('test1');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC test1.my_context1_pkg.set_my_context1;
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT SYS_CONTEXT('my_context1','salary_yes_no') FROM dual;
 
SYS_CONTEXT('MY_CONTEXT1','SALARY_YES_NO')
--------------------------------------------------------------------------------
DO_NOT_DISPLAY_SALARY
 
SQL> SET lines 150
SQL> SELECT * FROM test1.employees;
 
        ID FIRSTNAME                                          LASTNAME                                               SALARY
---------- -------------------------------------------------- -------------------------------------------------- ----------
         1 Yannick                                            Jaquier
 
SQL> EXEC DBMS_SESSION.SET_IDENTIFIER('yjaquier');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC test1.my_context1_pkg.set_my_context1;
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SET lines 150
SQL> SELECT * FROM session_context;
 
NAMESPACE                         ATTRIBUTE                                         VALUE
--------------------------------- ------------------------------------------------- ---------------------------------------------
MY_CONTEXT1                       SALARY_YES_NO                                     DISPLAY_SALARY
 
SQL> SELECT SYS_CONTEXT('my_context1','salary_yes_no') FROM dual;
 
SYS_CONTEXT('MY_CONTEXT1','SALARY_YES_NO')
------------------------------------------------------------------------------------------------------------------------------------------------------
DISPLAY_SALARY
 
SQL> SELECT * FROM test1.employees;
 
        ID FIRSTNAME                                          LASTNAME                                               SALARY
---------- -------------------------------------------------- -------------------------------------------------- ----------
         1 Yannick                                            Jaquier                                                 10000

Remark:
The context is said safe because if you try to set it outside of the context package you get an error message (even with “owner” of the context):

SQL> EXEC DBMS_SESSION.SET_CONTEXT('my_context1','salary_yes_no','DISPLAY_SALARY');
BEGIN DBMS_SESSION.SET_CONTEXT('my_context1','salary_yes_no','DISPLAY_SALARY'); END;
 
*
ERROR AT line 1:
ORA-01031: insufficient PRIVILEGES
ORA-06512: AT "SYS.DBMS_SESSION", line 101
ORA-06512: AT line 1

References

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Share the knowledge!

Related Posts:

5 thoughts on “Fine-Grained Access Control (FGAC) hands-on”

  1. Mason says:

    One of the best explained page on internet for DBMS_RLS understanding. I will suggest only this page to everyone. I got confused by reading the oracle docs, but this page cleared everything.

  2. Andrew Wolfe says:

    Yannick, this is an excellent post. I searched for ADD_POLICY_CONTEXT and found this. Your page actually has better information than two versions of Oracle documentation on DBMS_RLS. I’m going to recommend this as supplemental reading for my database security students.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>