Data Redaction in Oracle Database 12c - Part 1

Version 3

    Data Redaction in Oracle Database 12c – Part I

     

    Introduction

     

    Oracle 12c introduced a new feature of redacting column data which is the result set of the SELECT queries run by the applications. There has always been a need to hide the sensitive data which the applications or the application users access. This can be achieved by using the Data Redaction feature. The feature comes in as a part of Advanced Security and is achievable only with Oracle Enterprise Edition.

     

    Oracle Data Redaction Feature and Functionality:

     

    Data Redaction is done at the last minute of the query execution and thus does not hamper the data processing happening at the back end. This makes the redaction seen only on the result set of the query. Data redaction can be done in different methods and is achieved by defining a redaction policy using the DBMS_REDACT package against the desired table. A redaction policy redacts the data at the runtime based on the conditions that match the expression values or the SYS_CONTEXT values.

     

    img1

     

    To create a policy, the user creating it must have the EXECUTE privilege on the DBMS_REDACT PL/SQL package. A redaction policy does not apply to the SYS user and the users with the EXEMPT REDACTION POLICY privilege. In other words, these users will be able to view the actual data in the result set of the query. Below is a summary of the list of procedures and their functionalities defined in the DBMS_REDACT package has a few procedures defined which are listed below.

     

    Types of procedures under DBMS_REDACT package:

     

     

    DBMS_REDACT.ADD_POLICY        -     To add a new policy

    DBMS_REDACT.ALTER_POLICY      -     To alter an existing policy

    DBMS_REDACT.DROP_POLICY       -     To drop an existing policy

    DBMS_REDACT.ENABLE_POLICY     -     To enable an existing policy

    DBMS_REDACT.DISABLE_POLICY    -     To disable an existing policy

    DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES - To update the default displayed values of the redacted data for the FULL redaction type.

     

     

    Methods to redact different types of data:

     

    Full redaction:

     

    This is the default method that redacts all the contents of the column data of the table and the redacted data being displayed as a result set of the query depends on the data type of the column. A column with NUMBER data type will have the redacted data as 0, character data type will have the redacted data as a single space and date type column will have the redacted data as the first day of January 2001.

     

    To create a policy for FULL Redaction, set the function_type parameter of the DBMS_REDACT.ADD_POLICY to DBMS_REDACT.FULL To change the default displayed redacted values, we can make use of the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure.

     

    Example for creating a policy for Full Redaction on column of data type Number:

     

     

    BEGIN

    DBMS_REDACT.ADD_POLICY (

           object_schema         => 'HR',

           object_name           => 'full_redact_num',

           column_name                  => 'code',

           policy_name                  => 'Full_num_redact',

           function_type         => DBMS_REDACT.FULL,

           policy_description           => 'Full redaction on num col',

           expression                   => '1=1');

    END;

    /

     

     

    The above sections include:

     

    object_schema: schema name of the object on which the redaction will be applied.

     

    object_name: name of table or view on which the redaction will be applied.

     

    column_name: name of the column whose data will be redacted.

     

    policy_name: specify a name for the policy to be created.

     

    function_type: specify the type of the redaction to be applied on the column.

     

    policy_description: brief description of the policy

     

    expression: a boolean expression to apply the policy. Redaction will occur only if the expression evaluates to TRUE. An expression can also be based on the SYS_CONTEXT function.

     

    For example: expression =>'SYS_CONTEXT(''USERENV'',''SESSION_USER'')=''JOHN''' ---signifies that the policy will be applied only to the session username JOHN.

     

     

    SQL> select * from full_redact_num;

     

          CODE NAME

    ---------- --------------------

             0 JOHN

             0 STEVE

             0 SMITH

     

     

    In the above example, we see that the column CODE of data type number is redacted and the result set querying the column shows the value as 0.

     

    Following example shows how the full redacted data appears for a character type column on table 'full_redact_char'. Redacted column ""NAME" of character type displays the result set as a single blank space.

     

     

    SQL> select * from full_redact_char;

     

            ID NAME

    ---------- --------------------

           250

           251

           252

           253

     

     

    This shows how full redaction displays the result set for a data type column on table 'full_redact_date'. Column DOJ of type date has been redacted with the default value first day of January 2001.

     

     

    SQL> select * from full_redact_date;

     

            ID DOJ       NAME

    ---------- --------- --------------------

           300 01-JAN-01 SMITH

           301 01-JAN-01 JOHN

           302 01-JAN-01 STEVE

     

     

    Partial redaction:

     

    This method redacts only a portion of the column data displayed as a result set of a query. Partial redaction can be achieved by setting the "function_type" parameter of DBMS_REDACT.ADD_POLICY procedure to DBMS_REDACT.PARTIAL and using the parameter "function_parameters" of the procedure DBMS_REDACT.ADD_POLICY to define how the redaction appears. We can specify the starting position of the column data from which the redaction will take place along with the number of characters to be redacted from the starting position with the character to be used for displaying the redacted data. This method again holds good for Character Data Type, Number Data Type and Date-Time Data Type.

     

    When redacting a column of character type, the "function_parameters" is used with a set of values. It specifies the input data format, the output format in which the redacted data will be displayed as a result, the mask character to be used for redaction display, the start position from which the redaction begins and the last portion signifies the ending digit position until which the redaction will be done.

     

    Example to perform partial redaction on a column of character data type:

     

     

    BEGIN

    DBMS_REDACT.ADD_POLICY(

       object_schema       => 'HR',

       object_name         => 'partial_redact_char',

       column_name         => 'name',

       policy_name         => 'partial_char_redact',

       function_type       => DBMS_REDACT.PARTIAL,

       function_parameters => 'VVVVV,VVVVV,*,2,4',

       expression          => '1=1',

       policy_description  => 'Partially redacts Names');

    END;

    /

     

    SQL> select * from partial_redact_char;

     

            ID NAME

    ---------- --------------------

           250 S***A

           251 K***

           252 P***N

     

     

    The above example shows a partial redaction the NAME column of character type. The function_parameters include the set of values wherein the first part signify the input format represented by V, followed by the output format, the masking character "*", the starting digit position of the input word from where the redaction should begin which is the from the second digit and finally the end digit position until which redaction should take place - indicating up to fourth digit position.

     

    Partial redaction on a column of type Number depends on the set of values passed to the parameter "function_parameters" in the DBMS_REDACT.ADD_POLICY procedure. The values passed are in the order of the digit to be used for redaction display, the starting position from where the partial redaction takes place and finally the ending digit position for the redaction.

     

    Example to perform partial redaction on a column of NUMBER data type:

     

     

    BEGIN

    DBMS_REDACT.ADD_POLICY(

       object_schema       => 'HR',

       object_name         => 'partial_redact_num',

       column_name         => 'code',

       policy_name         => 'partial_num_redact',

       function_type       => DBMS_REDACT.PARTIAL,

       function_parameters => '5,2,3',

       expression          => '1=1',

       policy_description  => 'Partially redacts Code');

    END;

    /

                     

     

     

    SQL> select * from partial_redact_num;

     

          CODE NAME

    ---------- --------------------

           155 JOHN

           155 STEVE

           155 SMITH

     

     

    The above example redacts the column CODE of number using the masking digit "5" from the 2nd digit position until the third digit position.

     

    Partial redaction on a column of date-time type would appear as different dates than the actual date. There are a set of values passed to the parameter "function_parameter" of the procedure DBMS_REDACT.ADD_POLICY. The values include:

     

      • m - To redact a month, specify a lowercase "m" followed by 1-12. For example, m12 will redact the data and display the month as DEC. To skip redaction, use an uppercase M.

     

      • d - To redact a day, specify a lowercase "d" followed by 1-31. For example, d25 will redact the data and display the day as 25. If a greater value is   specified than the number of days the month has, then the value would be considered as the last day of the month. For example, if the value is passed as d31 for the month April, then the day displayed will be 30. To skip redaction, use an uppercase D.

     

      • y - To redact a year, specify a lowercase "y" followed by 1-9999. To skip redaction, use an uppercase Y.

     

     

      

    Example to perform partial redaction on a column of DATE data type:

     

     

    BEGIN

    DBMS_REDACT.ADD_POLICY(

       object_schema       => 'HR',

       object_name         => 'partial_redact_date',

       column_name         => 'DOJ',

       policy_name         => 'partial_date_redact',

       function_type       => DBMS_REDACT.PARTIAL,

       function_parameters => 'Md8y2015',

       expression          => '1=1',

       policy_description  => 'Partially redacts Date');

    END;

    /

     

    SQL> select * from partial_redact_date;

     

            ID DOJ         NAME

    ---------- ----------- --------------------

           300 08-DEC-2015 SMITH

           301 08-MAR-2015 JOHN

    1. 30208-JUN-2015 STEVE

     

     

    In the above example, we are redacting the values for the column DOJ by explicitly specifying that the days of the month should be displayed as 08 (d8) and the year values to be displayed as 2015 (y2015).

     

    Conclusion

     

    Data redaction can be used based on the style of the redaction required and for the data type of column in use. Since there are no actual changes done to the data at the block level or buffer level, data redaction has no impact on the backup/restore, upgrades, patching, import/export. In a data guard environment, the redaction policies created on the primary database gets replicated on the standby database too when the corresponding redo is applied.

     

     

    About the Authors

     

      

    Nassyam Basha Oracle DBA, OCM 11g, Oracle ACE Director, Author of Data Guard 11gR2 Book, Blogger, OTN Moderator and Super Hero, MOSC Guru,  Writer with OTN, DELL and  having around 9 years of hands on experience in High Availability technologies Like Oracle RAC, Data Guard, Exadata and much more. Currently working with The Pythian Group, In the past i have worked for AT&T(Bell Labs), dbaDirect, SLK software services.

     

     

    Shivananda Rao is an Oracle ACE Associate and working as a Senior Oracle DBA. He has good knowledge on Oracle technologies specifically with High Availability, Disaster Recovery, Upgrades and RMAN. He has been actively participating in the OTN forum and maintains Oracle technical blog (www.shivanandarao-oracle.com) which has more than 50 articles published by him. He has an expertise of working on Dataguard and RMAN issues with much concentrated on High Availability topics.