Is It Possible To use regular expression redaction policy to NCHAR, NVARCHAR2 DATATYPE on Oracle Database 12c Release1 (12.1) ?
Last updated on MARCH 13, 2017
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.2 [Release 12.1]Information in this document applies to any platform.
Goal
Is it possible to use regular expression redaction policy to NCHAR, NVARCHAR2 DATATYPE on Oracle Database 12c Release1 (12.1) ?
When executing sample script, It seems that full redaction is performed instead of regular expression redaction.
SQL> connect scott/tiger
Connected.
SQL> drop table test;
Table dropped.
SQL> create table test (c1 nchar(5));
Table created.
SQL> insert into test values ('12345');
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> connect / as sysdba
Connected.
SQL>
SQL> BEGIN
2 DBMS_REDACT.ADD_POLICY(OBJECT_SCHEMA => 'SCOTT',
3 object_name => 'TEST',
4 policy_name =>'SYSTEM_REDACT',
5 COLUMN_NAME => 'C1', FUNCTION_TYPE => DBMS_REDACT.REGEXP,
6 FUNCTION_PARAMETERS => NULL,
7 expression => '1=1',
8 REGEXP_PATTERN => '(\d\d\d)(\d\d)',
9 REGEXP_REPLACE_STRING => '\2\1',
10 REGEXP_POSITION => 1);
11 END;
12 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> Rem In the above regexp redaction policy, we are replacing the pattern
SQL> Rem '(\d\d\d)(\d\d)' with '\2\1'. An input string '12345' should be replaced
SQL> Rem by '45123'.
SQL>
SQL> connect scott/tiger
Connected.
SQL> select c1 from test;
C1
--------------------------------------------------------------------------------
* Null data means that full redaction is performed.
Solution
Sign In with your My Oracle Support account |
|
Don't have a My Oracle Support account? Click to get started |
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms