Hi,
I'm trying to use the Oracle Transparent Sensitive Data Protection feature on a database ver. 12.1.0.2.0.
The plan is to use the predefined REDACT_AUDIT policy in order to hide bind variables that refer sensitive columns.
I've followed the instructions explained in the Oracle doc
https://docs.oracle.com/database/121/DBSEG/tsdp.htm#DBSEG810 but it doesn't work (unless I missed something).
Here is what I've done:
1.
created a table MYTAB containing a CUST_ID column and populated with few thousand rows;
2.
created a sensitive type and associated it to the column CUST_ID:
BEGIN
DBMS_TSDP_MANAGE.ADD_SENSITIVE_TYPE (
sensitive_type => 'CUSTOMER_ID',
user_comment => 'Customer id type');
DBMS_TSDP_MANAGE.ADD_SENSITIVE_COLUMN(
schema_name => 'SCOTT',
table_name => 'MYTAB',
column_name => 'CUST_ID',
sensitive_type => 'CUSTOMER_ID',
user_comment => 'Bla bla bla');
END;
/
3.
Associated the predefined policy REDACT_AUDIT to my sensitive type:
begin
dbms_tsdp_protect.ASSOCIATE_POLICY('REDACT_AUDIT', 'CUSTOMER_ID', true);
end;
/4.
Enabled the policy
begin
dbms_tsdp_protect.ENABLE_PROTECTION_COLUMN(policy => 'REDACT_AUDIT');
end;
/
After executing a dynamic query on MYTAB with the where condition
where cust_id = :b
I was expecting, as stated in the documentation, not to be able to read any bind value from the system views (just the ones referring to my sensitive column) but I can perfectly see them for example by querying V$SQL_BIND_CAPTURE.
What am I doing wrong?
Many thanks!!!
From the docs:
"The REDACT_AUDIT transparent sensitive data protection policy displays the data as an asterisk (*) in audit records, trace files, and in V$SQL_BIND_DATA view queries"
My suspicion is that v$sql_bind_capture not being included was perhaps an oversight. For example, the FGA logs are redacted as expected
SQL> create table scott.mytab ( CUST_ID varchar2(10));
Table created.
SQL> insert into scott.mytab values ('Connor');
1 row created.
SQL>
SQL> BEGIN
2 DBMS_TSDP_MANAGE.ADD_SENSITIVE_TYPE (
3 sensitive_type => 'CUSTOMER_ID',
4 user_comment => 'Customer id type');
5
6 DBMS_TSDP_MANAGE.ADD_SENSITIVE_COLUMN(
7 schema_name => 'SCOTT',
8 table_name => 'MYTAB',
9 column_name => 'CUST_ID',
10 sensitive_type => 'CUSTOMER_ID',
11 user_comment => 'Bla bla bla');
12 END;
13 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> begin
2 dbms_tsdp_protect.ENABLE_PROTECTION_COLUMN(policy => 'REDACT_AUDIT');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_fga.add_policy
3 ( object_schema => 'SCOTT',
4 object_name => 'MYTAB',
5 policy_name => 'MYTAB_POL',
6 audit_condition => '1=1',
7 audit_column => 'CUST_ID' );
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> variable b1 varchar2(10)
SQL> exec :b1 := 'Connor';
PL/SQL procedure successfully completed.
SQL> select /*transdata*/ * from scott.mytab where cust_id = :b1;
CUST_ID
----------
Connor
SQL> @pt "select * from sys.fga_log$"
SESSIONID : 4020958
TIMESTAMP# :
DBUID : MCDONAC
OSUID : COMCDONA-AU\comcdona
OSHST : ORADEV\COMCDONA-AU
CLIENTID :
EXTID : COMCDONA-AU\comcdona
OBJ$SCHEMA : SCOTT
OBJ$NAME : MYTAB
POLICYNAME : MYTAB_POL
SCN : 118891021
SQLTEXT :
LSQLTEXT : select /*transdata*/ * from scott.mytab where cust_id = :b1
SQLBIND :
COMMENT$TEXT :
PLHOL :
STMT_TYPE : 1
NTIMESTAMP# : 19-FEB-17 03.57.17.397000 AM
PROXY$SID :
USER$GUID :
INSTANCE# : 0
PROCESS# : 9924:22908
XID :
AUDITID :
STATEMENT : 16
ENTRYID : 2
DBID : 1914458800
LSQLBIND : #1(1):*
OBJ$EDITION :
PL/SQL procedure successfully completed.
SQL>
I'd log a call with Support - it may be a bug.