Data Redaction (DBMS_REDACT) with 12cR1 (12.1.0.2)

Posted on , updated on by Yannick Jaquier
Oracle ,

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:

data_redaction01
data_redaction01

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

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Share the knowledge!

Related Posts:

8 thoughts on “Data Redaction (DBMS_REDACT) with 12cR1 (12.1.0.2)”

  1. 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.

  2. 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

  3. 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

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>