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:
- Tutorial: Implementing a Policy with a Database Session-Based Application Context: They use an AFTER LOGON ON DATABASE trigger to set the context, but again more or less no application let end users directly connect to database with database accounts.
- Tutorial: Implementing an Oracle Virtual Private Database Policy Group: They use an applicative context but the function to display or not figures is not using it, they still use the USERENV generic context instead…
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
- Database Security Enhancements in Oracle Database 10g
- Master Note For Oracle Virtual Private Database ( VPD / FGAC / RLS ) (Doc ID 1352641.1)
- Oracle Database Security Guide 11g Release 2 (11.2)
- Introducing Application Context – from the PL/SQL Potpourri
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.
Yannick Jaquier says:
Thanks a lot for enthusiastic comment !
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.
Yannick Jaquier says:
Andrew,
What else to say other than a big thank you for your nice comment !
Thanks, Yannick.