Redacting the data in 23ai Oracle Database

 In this post we will learn how to redact Salary column in employees table of 23ai Oracle database.

As Admin user configure the redact policy as -

PL/SQL Code

BEGIN
    DBMS_REDACT.ADD_POLICY(
        object_schema => 'ADMIN',
        object_name   => 'EMP',
        policy_name   => 'REDACT_SENSITIVE_DATA',
        expression    => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''HR'''
    );
END;
/
SET ECHO ON
BEGIN
    DBMS_REDACT.ALTER_POLICY(
        object_schema => 'ADMIN',
        object_name   => 'EMP',
        column_name   => 'SAL',
        policy_name   => 'REDACT_SENSITIVE_DATA',
        function_type => DBMS_REDACT.FULL
    );
END;
/

Original table Employee Records from ADMIN.EMP

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-12-80	800		20
7499	ALLEN	SALESMAN	7698	20-02-81	1600	300	30
7521	WARD	SALESMAN	7698	22-02-81	1250	500	30
7566	JONES	MANAGER	7839	02-04-81	2975		20
7698	BLAKE	MANAGER	7839	01-05-81	2850		30
7782	CLARK	MANAGER	7839	09-06-81	2450		10
7788	SCOTT	ANALYST	7566	19-04-87	3000		20
7839	KING	PRESIDENT		17-11-81	5000		10
7844	TURNER	SALESMAN	7698	08-09-81	1500	0	30
7934	MILLER	CLERK	7782	23-01-82	1300		10

10 rows selected.

Employee Records from app user after policy is configured from ADMIN.EMP

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-12-80	0		20
7499	ALLEN	SALESMAN	7698	20-02-81	0	300	30
7521	WARD	SALESMAN	7698	22-02-81	0	500	30
7566	JONES	MANAGER	7839	02-04-81	0		20
7698	BLAKE	MANAGER	7839	01-05-81	0		30
7782	CLARK	MANAGER	7839	09-06-81	0		10
7788	SCOTT	ANALYST	7566	19-04-87	0		20
7839	KING	PRESIDENT		17-11-81	0		10
7844	TURNER	SALESMAN	7698	08-09-81	0	0	30
7934	MILLER	CLERK	7782	23-01-82	0		10

10 rows selected.

So now you see the normal app user is seeing redacted output and salary as 0, however the original contents of table has salary, please note the ADMIN user will see the original data because he has EXEMPT REDACTION POLICY privilege. So if this privilege EXEMPT REDACTION POLICY is given to someone he or she will be able to see the original contents.