Table of contents
Preamble
Advanced Security enterprise edition paid option is made of two products:
Data Redaction conditionally hide on-the-fly sensitive data before it leaves the database. This picture from Oracle corporation says it all:
The condition to hide figures is really open as you write it in PL/SQL. The one I will take as an example is an application with its own security model (LDAP or whatever) connecting to a database using the same applicative account. This is a real life example with any Java or web application.
As I have already tested Virtual private database (VPD), that is the term used for combination of fine grained access control (FGAC) with application contexts, I have asked myself the difference with Data Redaction. Fortunately it is well explained in official documentation in Oracle Data Redaction and Oracle Virtual Private Database chapter.
Whatever Oracle says I have feeling that Data Redaction that is new in 12cR1 and back ported in 11gR2 (11.2.0.4 only) is the new tool to use to hide sensitive information. Unfortunately VPD and FGAC are free while Data Redaction is not…
Testing of this blog post has been done using Oracle database enterprise edition 12.1.0.2.0 – 64bit running on Oracle Linux Server release 7.2 in a virtual machine.
Data redaction implementation
I create my application schema owner, identified externally. I also provide execute on Data Redaction package called DBMS_REDACT and capability to create a context (still in 12cR1 the create context does not exist):
SQL> CREATE USER app IDENTIFIED externally DEFAULT TABLESPACE users; USER created. SQL> ALTER USER app quota unlimited ON users; USER altered. SQL> GRANT CONNECT,RESOURCE TO app; GRANT succeeded. SQL> GRANT EXECUTE ON dbms_redact TO app; GRANT succeeded. SQL> GRANT CREATE ANY CONTEXT TO app; GRANT succeeded. |
I create and provide grants to the password authenticated user that will be used in my application:
SQL> CREATE USER app_read IDENTIFIED BY secure_password; USER created. SQL> GRANT CONNECT TO app_read; GRANT succeeded. |
As app user I create my applicative table (really basic one). I also grant select and update to the account that will be used in my application:
SQL> CREATE TABLE employees ( id NUMBER, firstname VARCHAR2(50), lastname VARCHAR2(50), salary NUMBER); TABLE created. SQL> GRANT SELECT,UPDATE ON employees TO app_read; GRANT succeeded. SQL> INSERT INTO employees VALUES(1,'Yannick','Jaquier',10000); 1 ROW created. SQL> COMMIT; COMMIT complete. SQL> SELECT * FROM employees; ID FIRSTNAME LASTNAME SALARY ---------- -------------------------------------------------- -------------------------------------------------- ---------- 1 Yannick Jaquier 10000 |
As you might guess the column we want to redact (hide) to a part of applicative users is salary !
To create the Data Redaction policy I will use an application context that we have already seen when testing FGAC so going a little bit faster on this part. As app account I create a context and a package to change its value based on client_identifier parameter value of userenv context, necessary grants are also provided. The rule is that any supervisor (supervisorxx value) can see salary while the other accounts cannot:
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 IF LOWER(SYS_CONTEXT('userenv', 'client_identifier')) LIKE 'supervisor%' 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 IF; END set_my_context1; END; / PACKAGE BODY created. SQL> GRANT EXECUTE ON my_context1_pkg TO app_read; GRANT succeeded. |
When adding a policy with DBMS_REDACT.ADD_POLICY one of the most important parameter is expression. Means that the real time masking will be performed only if the expression is TRUE. Here the example I would like to simulate is to hide salary when parameter salary_yes_no of my my_context1 context is set to DO_NOT_DISPLAY_SALARY or is not set (NULL value).
EXEC dbms_redact.add_policy(object_schema => 'app', object_name => 'employees', column_name => 'salary', - policy_name => 'display_salary', function_type => dbms_redact.full, - expression => 'sys_context(''my_context1'',''salary_yes_no'')=''DO_NOT_DISPLAY_SALARY'' or sys_context(''my_context1'',''salary_yes_no'') is null', - policy_description => 'Hide salary column', - column_description => 'Column with sensitive salary information'); |
The redaction policy is enabled by default (enable parameter is set to TRUE by default). The function_type parameter set the redaction masking function. DBMS_REDACT.FULL will simply set salary column to 0 but many other options are available like changing only first digit of a credit card number or a social security number. Please refer the official documentation for a complete description.
In case you want to perform multiple tests you can drop the policy with:
SQL> EXEC dbms_redact.drop_policy(object_schema => 'app', object_name => 'employees', policy_name => 'display_salary'); PL/SQL PROCEDURE successfully completed. |
You have few dictionary tables to see what has been implemented:
SQL> SET lines 200 SQL> col object_owner FOR a10 SQL> col object_name FOR a10 SQL> col policy_description FOR a20 SQL> col policy_name FOR a15 SQL> SELECT object_owner,object_name,policy_name, enable,policy_description FROM redaction_policies; OBJECT_OWN OBJECT_NAM POLICY_NAME ENABLE POLICY_DESCRIPTION ---------- ---------- --------------- ------- -------------------- APP EMPLOYEES display_salary YES Hide salary COLUMN SQL> col column_name FOR a10 SQL> SELECT object_owner,object_name,column_name,function_type FROM redaction_columns; OBJECT_OWN OBJECT_NAM COLUMN_NAM FUNCTION_TYPE ---------- ---------- ---------- --------------------------- APP EMPLOYEES SALARY FULL REDACTION |
Data redaction testing
For testing I will connect with app_read account and set CLIENT_IDENTIFIER parameter value of USERENV context with DBMS_SESSION.SET_IDENTIFIER procedure. CLIENT_IDENTIFIER parameter value simulate the applicative account that has been used to identify inside your Java/Web application (LDAP or whatever).
If you do not set CLIENT_IDENTIFIER value then salary is not displayed:
SQL> SET lines 150 SQL> SELECT SYS_CONTEXT('my_context1','salary_yes_no') FROM dual; SYS_CONTEXT('MY_CONTEXT1','SALARY_YES_NO') ------------------------------------------------------------------------------------------------------------------------------------------------------ SQL> SELECT * FROM app.employees; ID FIRSTNAME LASTNAME SALARY ---------- -------------------------------------------------- -------------------------------------------------- ---------- 1 Yannick Jaquier 0 |
If you set CLIENT_IDENTIFIER value to an applicative account that is not allowed to see salaries:
SQL> EXEC DBMS_SESSION.SET_IDENTIFIER('operator01'); PL/SQL PROCEDURE successfully completed. SQL> EXEC app.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> SELECT * FROM app.employees; ID FIRSTNAME LASTNAME SALARY ---------- -------------------------------------------------- -------------------------------------------------- ---------- 1 Yannick Jaquier 0 |
If you set CLIENT_IDENTIFIER value to an applicative account that has privilege to see salaries:
SQL> EXEC DBMS_SESSION.SET_IDENTIFIER('supervisor01'); PL/SQL PROCEDURE successfully completed. SQL> EXEC app.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') ------------------------------------------------------------------------------------------------------------------------------------------------------ DISPLAY_SALARY SQL> SELECT * FROM app.employees; ID FIRSTNAME LASTNAME SALARY ---------- -------------------------------------------------- -------------------------------------------------- ---------- 1 Yannick Jaquier 10000 |
One funny thing you might notice is even APP user, owner of the object, is not able to see the value of salary column. This can be solved granting below system privileges:
SQL> GRANT exempt redaction policy TO app; GRANT succeeded. |
You would have chosen DBMS_REDACT.RANDOM as a masking function the salary would be different each time you perform a select onto the employees table.
Even if you are not able to see salary column you can still update it:
SQL> SELECT SYS_CONTEXT('userenv','client_identifier') FROM dual; SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') ------------------------------------------------------------------------------------------------------------------------------------------------------ operator01 SQL> SELECT SYS_CONTEXT('my_context1','salary_yes_no') FROM dual; SYS_CONTEXT('MY_CONTEXT1','SALARY_YES_NO') ------------------------------------------------------------------------------------------------------------------------------------------------------ DO_NOT_DISPLAY_SALARY SQL> UPDATE app.employees SET salary=20000 WHERE id=1; 1 ROW updated. SQL> COMMIT; COMMIT complete. SQL> SELECT * FROM app.employees; ID FIRSTNAME LASTNAME SALARY ---------- -------------------------------------------------- -------------------------------------------------- ---------- 1 yannick Jaquier 0 |
If you control with schema owner app you will see that salary has been well set to 20,000…
References
- Data Redaction (DBMS_REDACT) in Oracle Database 12c Release 1 (12.1)
- Encryption and Redaction in Oracle Database 12c with Oracle Advanced Security
- Data Redaction Demo for Oracle Advanced Security (Oracle Database 12c) – Part 2
- Oracle Data Redaction
- Part II Using Oracle Data Redaction
dinesh says:
how to exempt redaction policy for particular table only.
user can see other reaction table data.
only one table they should not see.
Yannick Jaquier says:
Do not add redaction for this table…
Shaiju says:
Dear Jannick,
Good day! I have a query in data redaction. Oracle database masks data for all users except SYS user privilege. We can exempt service account users from data redaction by running below sql.
SQL>GRANT exempt redaction policy TO service_account;
Is it possible to revoke the user from exemption of data redaction and include the user to view data in masked values as regular user.
SQL>Revoke grant exempt redaction policy to service_account
Yannick Jaquier says:
Hi Shaiju,
Should be possible with a revoke from: have u tried ?
Shaiju says:
Hi Yannick,
I tried with revoke, but error is thrown.
Possible to share sql statement please.
Yannick Jaquier says:
Hi Shaiju
Have you tried something like:
revoke exempt redaction policy from
Thomas says:
Hi Yannick Jaquier
We have a requirement
The requirement is
1.customer data remains hidden from db developers/admin ,if developers/qa/ query select customer data, he should see it unreadable or obfuscated/hidden/encrypted
[dbms_redact can work here]
2.The database fields that need to be covered , we have list of them , they are static.
[dbms_redact can work here]
3.when application queries data , it travels hidden / encrypted /hidden.
[dbms_redact can work here]
4.At application if user is eligible to see that data , it should be shown in readable form to allow him to make changes. and it will again encrypted/hidden/obfuscated once user makes changes and submits and stored in the database.
In the case of updates from application to UI to database , suppose customer updates email from abc@gmail.com to def@gmail , the text def@gmail will go readable from application to database , does dbms redact comes with any additional feature to handle this scenario ?
regards
Thomas
Yannick Jaquier says:
Hi Thomas,
Not 100% sure to understand the request but…
I think what you are looking for is network encryption, see my other post:
Restricting and securing your database network – part 2
Good news is that it is available for free starting with 12cR1…
Thanks