What is Oracle Data Redaction with Examples (Mask your sensitive Data)
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
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
Now verify the payment_details table the credit card number would have been masked to 0.
If you like please follow and comment
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
| Procedure | Description |
|---|---|
| Adds a Data Redaction policy to a table or view |
| Modifies a Data Redaction policy |
| Applies a Data Redaction policy expression to a table or view column |
| Creates a Data Redaction policy expression |
| Disables a Data Redaction policy |
| Drops a Data Redaction policy |
| Drops a Data Redaction policy expression |
| Enables a Data Redaction policy |
| Globally updates the full redaction value for a given data type. You must restart the database instance before the updated values can be used. |
| 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;
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.
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
2 Comments
It is not a 12c feature. It was introduced in 11.2.0.4.
ReplyDeleteOracle has introduced redaction feature in database 12c and added later to 11g in 11.2.0.4 patchset.
DeletePost a Comment