Rem: This explains how to restrict others to not view data for sensitive columns except some IPs (DBA)
Rem: DBA have exception to this rule
Rem: REDACT only hide data. The actual data remains intact
Rem: Code mentioned in this blog is only for knowledge purpose. Don't execute in production without taking prior verification
Rem: 06-December-2023
Rem: Hayat Mohammad Khan @ hayathk@hotmail.com
Note: Advance data security licenses is a must to use this feature.
STEP#1
grant execute on sys.dbms_redact to HR;
STEP#2
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
column_name => 'FULL_NAME',
policy_name => 'redact_EMPLOYEES_name',
function_type => DBMS_REDACT.FULL,
expression => 'SYS_CONTEXT(''USERENV'',''IP_ADDRESS'') NOT IN (''192.168.1.101'',''192.168.1.102'',''192.168.1.103')'
);
END;
/
BEGIN
DBMS_REDACT.alter_policy(
object_schema => 'HR',
object_name => 'EMPLOYEES',
action => dbms_redact.add_column,
column_name => 'FIRST_NAME',
policy_name => 'redact_EMPLOYEES_name',
function_type => DBMS_REDACT.FULL,
expression => 'SYS_CONTEXT(''USERENV'',''IP_ADDRESS'') NOT IN (''192.168.1.101'',''192.168.1.102'',''192.168.1.103')'
);
END;
/
BEGIN
DBMS_REDACT.alter_policy(
object_schema => 'HR',
object_name => 'EMPLOYEES',
column_name => 'LAST_NAM',
policy_name => 'redact_EMPLOYEES_name',
function_type => DBMS_REDACT.FULL,
expression => 'SYS_CONTEXT(''USERENV'',''IP_ADDRESS'') NOT IN (''192.168.1.101'',''192.168.1.102'',''192.168.1.103')'
);
END;
/
If policy needs to be disable / Drop
BEGIN
DBMS_REDACT.DISABLE_POLICY
(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'redact_EMPLOYEES_name'
);
END;
/
BEGIN
DBMS_REDACT.DROP_POLICY
(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'redact_EMPLOYEES_name'
);
END;
/
Example 2: DBA and HR user can view, other will see #### in Full_Name Column
BEGIN
DBMS_REDACT.alter_policy (
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'redact_TEST_NAME',
action => DBMS_REDACT.modify_expression,
column_name => 'FULL_NAME',
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''HR'''
);
/
Options available:
-- function_type => dbms_redact.full,
-- function_type => dbms_redact.partial,
-- function_parameters => DBMS_REDACT.REDACT_CCN16_F12,
Useful Views:
select * from REDACTION_POLICIES;
select * from REDACTION_COLUMNS;
SET LINESIZE 250
COLUMN char_value FORMAT A10
COLUMN varchar_value FORMAT A10
COLUMN nchar_value FORMAT A10
COLUMN nvarchar_value FORMAT A10
COLUMN timestamp_value FORMAT A27
COLUMN timestamp_with_time_zone_value FORMAT A32
COLUMN blob_value FORMAT A20
COLUMN clob_value FORMAT A10
COLUMN nclob_value FORMAT A10
SELECT * FROM redaction_values_for_type_full;
Credit Given to below URL: Knowledge of this blog is taken from below websites. Thanks to them for sharing knowledge
https://oracle-base.com/articles/12c/data-redaction-12cr1
https://www.morganslibrary.org/reference/sys_context.html
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/asoag/configuring-oracle-data-redaction-policies.html#GUID-4ACBFFF5-175D-4A05-B4B4-A18C12B2E325