hi people , suppose i have a user dba1 has sensitive data in emps table so he used dbms_redact.add_policy to prevent another user (user1) from seeing that sensitive data
SQL> SHOW USER
USER is "DBA1"
SQL> SHOW CON_NAME
CON_NAME
------------------------------
PDB1
SQL> DESC EMPS
Name Null? Type
---------------------------------------------------------------------------------- -------- -------------------------------------------------------
ID NUMBER
NAME VARCHAR2(10)
BANK_ACCOUNT NUMBER
SQL> SELECT * FROM EMPS;
ID NAME BANK_ACCOUNT
---------- ---------- ------------
1 JOHN 123456
2 SAM 234567
1 begin
2 dbms_redact.add_policy (
3 OBJECT_SCHEMA => 'DBA1' ,
4 OBJECT_NAME => 'EMPS' ,
5 POLICY_NAME => 'DBA1EMPS_POL1' ,
6 COLUMN_NAME => 'BANK_ACCOUNT' ,
7 FUNCTION_TYPE => DBMS_REDACT.RANDOM ,
8 EXPRESSION => '1=1'
9 );
10* END;
SQL> /
GRANT SELECT ,INSERT ,UPDATE,DELETE ON EMPS TO USER1;
Grant succeeded.
now user1 who has delete object privilege on dba1.emps table (not EXEMPT DML REDACTION POLICY) - get connected
SQL> show user
USER is "USER1"
SQL> show con_name
CON_NAME
------------------------------
PDB1
select * from dba1.emps;
ID NAME BANK_ACCOUNT
---------- ---------- ------------
1 JOHN 104051
2 SAM 89302
delete from dba1.emps;
2 rows deleted.
SQL> commit;
Commit complete.
so he could delete (and other dml operations) without having EXEMPT DML REDACTION POLICY
so what is exempt dml redaction policy made for ?
thank you all