HAPPY NEW YEAR 2021

For Any queries, please mail us at support@funoracleapps.com

What is Oracle Data Redaction with Examples (Mask your sensitive Data)

2 comments

What is Oracle Data Redaction with Examples (Mask your sensitive Data)


Oracle Data Redaction is one of the new features introduced in Oracle Database Oracle 12c and back-ported to 11.2.0.4 via patch set(Some examples of implementing Data Redaction using DBMS_REDACT (Doc ID 1588270.1)). This new feature is part of the Advanced Security option and enables the protection of data shown to the user in real-time, without requiring changes to the application.
Oracle Data Redaction enables you to mask (redact) data that is returned from queries issued by applications.









You can redact column data by using one of the following methods:


Full redaction. You redact all of the contents of the column data. The redacted value returned to the querying application user depends on the data type of the column. For example, columns of the NUMBER data type are redacted with a zero (0), and character data types are redacted with a single space.


Partial redaction. You redact a portion of the column data. For example, you can redact a Social Security number with asterisks (*), except for the last 4 digits.


Regular expressions. You can use regular expressions to look for patterns of data to redact. For example, you can use regular expressions to redact email addresses, which can have varying character lengths. It is designed for use with character data only.


Random redaction. The redacted data presented to the querying application user appears as randomly generated values each time it is displayed, depending on the data type of the column.


No redaction. The None redaction type option enables you to test the internal operation of your redaction policies, with no effect on the results of queries against tables with policies defined on them. You can use this option to test the redaction policy definitions before applying them to a production environment.








Oracle Database applies the redaction at runtime when users access the data (that is, at query-execution time). This solution works well in a production system. During the time that the data is being redacted, all of the data processing is performed normally, and the back-end referential integrity constraints are preserved.

How It Works?
We can create redaction policies that specify conditions that must be met before the data gets redacted and returned to the user. During the definition of such policies, the DBA can specify which columns and the type of protection that must be applied.

The package used to create protection rules is called DBMS_REDACT. The package includes five procedures to manage the rules and an additional procedure to change the default value for the full redaction policy.


When to Use Oracle Data Redaction

Use Oracle Data Redaction when you must disguise sensitive data that your applications and application users must access.

Data Redaction enables you to easily disguise the data using several different redaction styles.

Oracle Data Redaction is ideal for situations in which you must redact specific characters out of the result set of queries of Personally Identifiable Information (PII) returned to certain application users. For example, you may want to present a Social Security number that ends with the numbers 4320 as ***-**-4320.

Important Procedures related to DBMS_REDACT

ProcedureDescription

DBMS_REDACT.ADD_POLICY

Adds a Data Redaction policy to a table or view

DBMS_REDACT.ALTER_POLICY

Modifies a Data Redaction policy

DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL

Applies a Data Redaction policy expression to a table or view column

DBMS_REDACT.CREATE_POLICY_EXPRESSION

Creates a Data Redaction policy expression

DBMS_REDACT.DISABLE_POLICY

Disables a Data Redaction policy

DBMS_REDACT.DROP_POLICY

Drops a Data Redaction policy

DBMS_REDACT.DROP_POLICY_EXPRESSION

Drops a Data Redaction policy expression

DBMS_REDACT.ENABLE_POLICY

Enables a Data Redaction policy

DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES

Globally updates the full redaction value for a given data type. You must restart the database instance before the updated values can be used.

DBMS_REDACT.UPDATE_POLICY_EXPRESSION

Updates a Data Redaction policy expression






It supports the following column data types:

NUMBER, BINARY_FLOAT, BINARY_DOUBLE, CHAR, VARCHAR2, NCHAR, NVARCHAR2, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, BLOB, CLOB, and NCLOB.


How to check Redaction policies

select * from redaction_policies;

We need to make sure the respective user (in my case apps user) has access to the DBMS_REDACT package.

GRANT EXECUTE ON sys.dbms_redact TO apps;

Example:

let's create a table

CREATE TABLE credit_payment (
id NUMBER NOT NULL,
customer_id NUMBER NOT NULL,
card_no NUMBER NOT NULL,
card_string VARCHAR2(19) NOT NULL,
expiry_date DATE NOT NULL
);

Add some data

INSERT INTO credit_payment VALUES (1, 4000, 1234123412341234, '1234-1234-1234-1234', TRUNC(ADD_MONTHS(SYSDATE,2)));

INSERT INTO credit_payment VALUES (2, 4001, 2345234523452345, '2345-2345-2345-2345', TRUNC(ADD_MONTHS(SYSDATE,4)));

INSERT INTO credit_payment VALUES (3, 4002, 3456345634563456, '3456-3456-3456-3456', TRUNC(ADD_MONTHS(SYSDATE,6)));

INSERT INTO credit_payment VALUES (4, 4003, 4567456745674567, '4567-4567-4567-4567', TRUNC(ADD_MONTHS(SYSDATE,12)));


INSERT INTO credit_payment VALUES (5, 4004, 5678567856785678, '5678-5678-5678-5678', TRUNC(ADD_MONTHS(SYSDATE,9)));


Check the data






Add a new policy

Connect as apps and run

BEGIN
  DBMS_REDACT.add_policy(
    object_schema => 'apps',
    object_name   => 'credit_payment',
    column_name   => 'card_no',
    policy_name   => 'redact_card_mask',
    function_type => DBMS_REDACT.full,
    expression    => '1=1'
  );
  END;
  /

 Expression of "1=1" means the redaction will always take place.

Now verify the payment_details table the credit card number would have been masked to 0.

and verify the policy from 

select * from redaction_policies;

You can see the default values by querying the REDACTION_VALUES_FOR_TYPE_FULL view

These default values can be altered using the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure, but you will need to restart the instance for the updates to be visible.

Alter an Existing Policy
The ALTER_POLICY procedure allows you to make changes to an existing policy. The type of change being made is controlled using the ACTION parameter. Depending on the action required, the relevant parameters must be specified.

The following example changes the previously created redaction policy so that it uses partial redaction. Notice the FUNCTION_PARAMETERS are now specified to give instructions on how the partial redaction should take place. For a numeric data type, we specify a comma-separated list of three elements (value to redact to, start point, end point), so in this case, we want the first 12 characters of the number to always display as "111111111111".

  BEGIN
  DBMS_REDACT.alter_policy (
    object_schema       => 'apps',
    object_name         => 'credit_payment',
    policy_name         => 'redact_card_mask',
    action              => DBMS_REDACT.modify_column,
    column_name         => 'card_no',
    function_type       => DBMS_REDACT.partial,
    function_parameters => '1,1,12'
  );
END;
/



We can add another column to the redaction policy to protect the string representation of the card number.

BEGIN
  DBMS_REDACT.alter_policy (
    object_schema       => 'apps',
    object_name         => 'credit_payment',
    policy_name         => 'redact_card_mask',
    action              => DBMS_REDACT.add_column,
    column_name         => 'card_string',
    function_type       => DBMS_REDACT.partial,
    function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,#,1,12'
  );
END;
/

Now till now what we did has affected all users and no one can see the data. But now I want to authorize apps user to see the data and no-one else.

BEGIN
  DBMS_REDACT.alter_policy (
    object_schema       => 'apps',
    object_name         => 'credit_payment',
    policy_name         => 'redact_card_mask',
    action              => DBMS_REDACT.modify_expression,
    column_name         => 'card_no',
    expression          => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''APPS'''
  );
END;
/

SQL> COLUMN card_no FORMAT 9999999999999999
SQL> connect apps/apps
Connected.
SQL> COLUMN card_no FORMAT 9999999999999999
SQL> set lines 200 pages 200
SQL> show user
USER is "APPS"
SQL> SELECT *
FROM   apps.payment_details;  2  

	ID CUSTOMER_ID		 CARD_NO CARD_STRING	     EXPIRY_DATE
---------- ----------- ----------------- ------------------- ------------------
	 1	  4000	1234123412341234 1234-1234-1234-1234 05-MAR-21
	 2	  4001	2345234523452345 2345-2345-2345-2345 05-MAY-21
	 3	  4002	3456345634563456 3456-3456-3456-3456 05-JUL-21
	 4	  4003	4567456745674567 4567-4567-4567-4567 05-JAN-22
	 5	  4004	5678567856785678 5678-5678-5678-5678 05-OCT-21

SQL> grant select on apps.payment_details to ap;

Grant succeeded.

SQL> connect ap/ap 
Connected.
SQL>  SELECT *
FROM   apps.payment_details;  2  

	ID CUSTOMER_ID		 CARD_NO CARD_STRING	     EXPIRY_DATE
---------- ----------- ----------------- ------------------- ------------------
	 1	  4000	1111111111111234 ####-####-####-1234 05-MAR-21
	 2	  4001	1111111111112345 ####-####-####-2345 05-MAY-21
	 3	  4002	1111111111113456 ####-####-####-3456 05-JUL-21
	 4	  4003	1111111111114567 ####-####-####-4567 05-JAN-22
	 5	  4004	1111111111115678 ####-####-####-5678 05-OCT-21


Drop an Existing Policy

The DROP_POLICY procedure is used to remove an existing redaction policy. The following example drops the redaction policy and queries the data, showing the redaction is no longer taking place.


BEGIN

  DBMS_REDACT.drop_policy (

    object_schema => 'apps',

    object_name   => 'credit_payment',

    policy_name   => 'redact_card_mask'

  );

END;

/


Note: Redaction will not take place if the user has the EXEMPT REDACTION POLICY system privilege.

Read oracle doc for more options

Reference:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/asoag/introduction-to-oracle-data-redaction.html#GUID-57C07734-6D33-497B-A990-1E8F327488B1

If you like please follow and comment

2 comments :

  1. It is not a 12c feature. It was introduced in 11.2.0.4.

    ReplyDelete
    Replies
    1. Oracle has introduced redaction feature in database 12c and added later to 11g in 11.2.0.4 patchset.

      Delete