Strict Redaction Semantics

A DBMS_REDACT issue has recently popped up on SQL.RU. I constructed a simple test case to demonstrate the problem in 19.9:

set echo on lin 120

conn tc/tc@localhost/pdb

grant create table to scott identified by tiger;
alter user scott quota unlimited on users;

drop table scott.emp;
create table scott.emp(client_name varchar2(30));

insert into scott.emp values ('Larry Ellison');
insert into scott.emp values ('Jeff Bezos');
insert into scott.emp values ('Elon Musk');
insert into scott.emp values ('Vladimir Putin');
insert into scott.emp values (null);

exec dbms_redact.add_policy( -
       object_schema         => 'SCOTT', -
       object_name           => 'emp', -
       column_name           => 'CLIENT_NAME', -
       policy_name           => 'client_fio', -
       function_type         => dbms_redact.regexp, -
       regexp_pattern        => '(\S{3})(\S+)', -
       regexp_replace_string => '\1***', -
       expression            => '1 = 1' -
)

col client_name for a16
col c1 for a16
col c2 like c1
col c3 like c1
create or replace view scott.vw as
select client_name
     , nvl(client_name, 'zyzy') c1
     , case when client_name='Vla*** Put***' then 'y' else client_name end c2
     ,'Hello '||client_name c3
  from scott.emp;

grant alter session, create session to tc2 identified by tc2;

grant select on scott.vw to tc2;

conn tc2/tc2@localhost/pdb

select * from scott.vw;

In a nutshell, there is a table that has a data redaction policy. There is a view built on top of it. However, when a user tries to select from the view, it returns an error:

SQL> select * from scott.vw;
select * from scott.vw
                    *
ERROR at line 1:
ORA-28094: SQL construct not supported by data redaction

This restriction is mentioned in the Advanced Security Guide:

To avoid the ORA-28094 error, ensure that the query has the following properties:
In a CREATE VIEW definition or an inline view, there cannot be any SQL expression that involves a redacted column.

When I come across such restrictions, it is always interesting to explore if there is a way of getting around it. Let us enable Data Redaction tracing and rerun the query:

SQL> alter session set events 'trace[radm]';

Session altered.

SQL> select * from scott.vw;
select * from scott.vw
                    *
ERROR at line 1:
ORA-28094: SQL construct not supported by data redaction

Here is the trace file:

kzdmpci: Table obj# is: 23656
kzdmpci: Policy Name is: client_fio
kzdmpci: Policy Expression ID radm_mc$.pe# = 5001
kzdmpci: intcol# is: 1
kzdmpci: Masking Function is: 5
kzdmpci: RegExp pattern: (\S{3})(\S+) (len=12)
kzdmpci: RegExp replace: \1*** (len=5)
kzdmpci: RegExp match  :  (len=0), mpl=17
kzdmpci: no RADM policies exist, starting new RADM Policy Chain.
kzdmpci: new RADM Policy Chain ci->kkscdmpc=0x12da37ec started with  obj#=23656
kzdmpci:2: radm_pe$ lookup: for Policy Expression ID pe# 5001, pe_pexpr is [1 = 1], pe_name = [], pe_obj# = 23656
kzdmpci: added column 1 for object 23656
kzradm_vc_replace_expr_allowed: policy on table EMP

kzdmrfs: entered with opntyp of OPNTCOL
kzdmrfs: found redacted col CLIENT_NAME at 7
kzradm_vc_replace_expr_allowed: 0

kzdmchkerpna: kzpcxp(KZSERP) gave FALSE, no bypass.

kzdmchkerpna: kzpcxp(KZSERP) gave FALSE, no bypass.

kzdmchkerpna: kzpcxp(KZSERP) gave FALSE, no bypass.

kzdmchkerpna: kzpcxp(KZSERP) gave FALSE, no bypass.

kzdmchkerp: kzpcsp(KZSERP) gave FALSE, no bypass.
kzdmprqb: entered
kzdminsc: entered with opntyp=OPNTCOL

kzdminsc: view column CLIENT_NAME with KCCF2MASKDEP detected, calling kzdmpvc
kzdmpvc: level 0, column 'CLIENT_NAME' has KCCF2MASKDEP flag
kzdmpvc: level 0, calling kzdmcrmo to insert MASK operator around view column with name 'CLIENT_NAME'

kzdmcrmo: the pe# is 5001
kzdmcrmo: entered, creating MASK operator for column CLIENT_NAME of table EMP
kzdmcrmo: policy chain = 0x6319aa30
kzdmcrmo: obj# = 23656, intcol# = 1
kzdmcrmo: New ctxmask chain pgactx->ctxmask=0x62485db0 started with obj#=23656
kzdmcrmo: found object 23656
kzdmcrmo: found column 1, metadata=0x6319a988
kzdmcrmo: New ctxmask_re chain 0x62485d80 started with col=(23656,1)
kzdmcrmo: finished creating MASK operator
kzdmpvc: level 0, kzdmcrmo inserted MASK operator.
kzdminsc: entered with opntyp=OPNTCOL

kzdminsc: view column C1 with KCCF2MASKDEP detected, calling kzdmpvc
kzdmpvc: level 0, column 'C1' has KCCF2MASKDEP flag
kzdmpvc: level 0, processing SQL expression...
kzdmpvc: strict semantics disallow expression in VIEW


kzdmchkerpna: kzpcxp(KZSERP) gave FALSE, no bypass.

This strict semantics disallow expression in VIEW bit sounded intriguing, so that I decided to research what it is and how to change it:

SQL> select ksppinm, ksppdesc from x$ksppi where ksppinm like '%redaction%';

KSPPINM                        KSPPDESC
------------------------------ ----------------------------------------
_strict_redaction_semantics    Strict Data Redaction semantics

I set the parameter to FALSE and bounced the database:

SQL> alter system set "_strict_redaction_semantics"=false scope=spfile;

System altered.

$ srvctl stop database -db orcl ; srvctl start database -db orcl

Sure enough, that is what I got after selecting the data from the view:

SQL> select * from scott.vw;

CLIENT_NAME      C1                  C2               C3
---------------- ------------------- ---------------- -------------------
Lar*** Ell***    Lar*** Ell***       Lar*** Ell***    Hello Lar*** Ell***
Jef*** Bez***    Jef*** Bez***       Jef*** Bez***    Hello Jef*** Bez***
Elo*** Mus***    Elo*** Mus***       Elo*** Mus***    Hello Elo*** Mus***
Vla*** Put***    Vla*** Put***       y                Hello Vla*** Put***
                 zyzy                                 Hello

Conclusion

Oracle obviously makes such restrictions on purpose. There is no much information about this parameter, but there should be some corner cases due to which Oracle decided to keep the redaction semantics as strict by default. It might be the case that Oracle will make this functionality officially available in a next release once it is ready for production use.

Oracle to PostgreSQL Replication: Numbers Truncated to 6 Decimal Places

I configured an initial load from Oracle to PostgreSQL, and found that some numbers were truncated after the load, e.g. Oracle number 123.0123456789 happened to be 123.012346 in PostgreSQL.

Setup

Source: Oracle Goldengate 19.1.0.0.4

GGSCI (ogg-hub as c##ggadmin@orcl/CDB$ROOT) 9> versions

Operating System:
Linux
Version #2 SMP Fri Mar 29 17:05:02 PDT 2019, Release 4.1.12-124.26.7.el7uek.x86_64
Node: ogg-hub
Machine: Linux

Database:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Target: Oracle GoldenGate 19.1.0.0.200714 for PostgreSQL

GGSCI (ogg-hub as ggadmin@testdb) 8> versions

Operating System:
Linux
Version #2 SMP Fri Mar 29 17:05:02 PDT 2019, Release 4.1.12-124.26.7.el7uek.x86_64
Node: ogg-hub
Machine: Linux

Database:
PostgreSQL
Version 12.06.0000 PostgreSQL 12.6
ODBC Version 03.52.0000
Driver Information:
GGpsql25.so
Version 07.16.0353 (B0519, U0369)
ODBC Version 03.52

odbc.ini

[ODBC Data Sources]
testdb=PostgreSQL on testdb

[ODBC]
IANAAppCodePage=106
InstallDir=/u01/app/oracle/product/19/ogg_pg_home1

[testdb]
Driver=/u01/app/oracle/product/19/ogg_pg_home1/lib/GGpsql25.so
Description=PostgreSQL 12.6
Database=testdb
HostName=postgres
PortNumber=5432
TransactionErrorBehavior=2

Initial Data

There is a table in the Oracle database:

SQL> create table tc.test(
  2    id int primary key,
  3    n1 number,
  4    n2 number);

Table created.

SQL>
SQL> insert into tc.test values (0, 123.0123456789, 123.0123456789);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select * from tc.test;

             ID              N1              N2
--------------- --------------- ---------------
              0  123.0123456789  123.0123456789

The corresponding PostgreSQL table is this:

testdb=# create table tc.test(
testdb(#   id bigint primary key,
testdb(#   n1 numeric,
testdb(#   n2 numeric(38,10));
CREATE TABLE
testdb=# \d tc.test
                     Table "tc.test"
 Column |      Type      | Collation | Nullable | Default
--------+----------------+-----------+----------+---------
 id     | bigint         |           | not null |
 n1     | numeric        |           |          |
 n2     | numeric(38,10) |           |          |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

GoldenGate Initial Load Processes

Extract

GGSCI (ogg-hub) 1> view params einit

EXTRACT einit
USERIDALIAS oggadmin
RMTHOST ogg-hub, MGRPORT 7810
RMTTASK REPLICAT, GROUP rinit

TABLE pdb.tc.test;

Replicat

GGSCI (ogg-hub) 1> view params rinit

REPLICAT rinit
SETENV (PGCLIENTENCODING="UTF8")
SETENV (ODBCINI="/home/ogg/pg/odbc.ini" )
TARGETDB testdb, USERIDALIAS ggadmin
DISCARDFILE ./dirrpt/rinit.dsc, PURGE

MAP pdb.*.*, TARGET *.*;

Performing Initial Load

Extract

GGSCI (ogg-hub) 1> start extract einit

Sending START request to MANAGER ...
EXTRACT EINIT starting

Replicat Report File

***********************************************************************
               Oracle GoldenGate Delivery for PostgreSQL
 Version 19.1.0.0.200714 OGGCORE_19.1.0.0.0OGGBP_PLATFORMS_200628.2141
   Linux, x64, 64bit (optimized), PostgreSQL on Jun 29 2020 04:06:46

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.

                    Starting at 2021-03-08 18:05:04
***********************************************************************

Operating System Version:
Linux
Version #2 SMP Fri Mar 29 17:05:02 PDT 2019, Release 4.1.12-124.26.7.el7uek.x86_64
Node: ogg-hub
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 15745

Description:

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************

2021-03-08 18:05:09  INFO    OGG-03059  Operating system character set identified as UTF-8.

2021-03-08 18:05:09  INFO    OGG-02695  ANSI SQL parameter syntax is used for parameter parsing.

2021-03-08 18:05:09  INFO    OGG-02095  Successfully set environment variable PGCLIENTENCODING=UTF8.

2021-03-08 18:05:09  INFO    OGG-02095  Successfully set environment variable ODBCINI=/home/ogg/pg/odbc.ini.

2021-03-08 18:05:09  INFO    OGG-01360  REPLICAT is running in Remote Task mode.
REPLICAT rinit
SETENV (PGCLIENTENCODING="UTF8")
SETENV (ODBCINI="/home/ogg/pg/odbc.ini" )
TARGETDB testdb, USERIDALIAS ggadmin

2021-03-08 18:05:09  INFO    OGG-03036  Database character set identified as UTF-8. Locale: en_US.UTF-8.

2021-03-08 18:05:09  INFO    OGG-03037  Session character set identified as UTF-8.
DISCARDFILE ./dirrpt/rinit.dsc, purge
MAP pdb.*.*, TARGET *.*;

2021-03-08 18:05:09  INFO    OGG-01815  Virtual Memory Facilities for: COM
    anon alloc: mmap(MAP_ANON)  anon free: munmap
    file alloc: mmap(MAP_SHARED)  file free: munmap
    target directories:
    /u01/app/oracle/product/19/ogg_pg_home1/dirtmp.

2021-03-08 18:05:09  INFO    OGG-25340
Database Version:
PostgreSQL
Version 12.06.0000 PostgreSQL 12.6
ODBC Version 03.52.0000

Driver Information:
GGpsql25.so
Version 07.16.0353 (B0519, U0369)
ODBC Version 03.52.

2021-03-08 18:05:09  INFO    OGG-25341
Database Language and Character Set:
SERVER_ENCODING = "UTF8"
LC_CTYPE        = "en_US.UTF-8"
TIMEZONE        = "Europe/Dublin".

***********************************************************************
**                     Run Time Messages                             **
***********************************************************************


2021-03-08 18:05:10  INFO    OGG-06506  Wildcard MAP resolved (entry pdb.*.*): MAP "PDB"."TC"."TEST", TARGET "TC"."TEST".

2021-03-08 18:05:10  INFO    OGG-02756  The definition for table PDB.TC.TEST is obtained from the trail file.

2021-03-08 18:05:10  INFO    OGG-06511  Using following columns in default map by name: id, n1, n2.

2021-03-08 18:05:10  INFO    OGG-06510  Using the following key columns for target table tc.test: id.

PostgreSQL

testdb=# select * from tc.test;
 id |     n1     |       n2
----+------------+----------------
  0 | 123.012346 | 123.0123456789
(1 row)

testdb=# \d tc.test
                     Table "tc.test"
 Column |      Type      | Collation | Nullable | Default
--------+----------------+-----------+----------+---------
 id     | bigint         |           | not null |
 n1     | numeric        |           |          |
 n2     | numeric(38,10) |           |          |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

It can be seen that the column n1 has value 123.012346 rather than the expected value 123.0123456789.
It is also worth noting that the column n2 defined as numeric(38,10) has the proper value as it is in the source Oracle database.

Thoughts and Fix

The issue itself is that the number was truncated to 6 decimal places. It is quite easy to find the same issue was already reported in 2018, but it is still not resolved: Golden Gate truncating decimal places for number data type post migration.

Firstly, when I encountered that issue, I tried to identify what component is causing this: GoldenGate for Oracle, GoldenGate for PostgreSQL, or may be PostgreSQL itself. GoldenGate for PostgreSQL uses Progress DataDirect driver that has an interesting setting: Unbounded Numeric Scale that has a default value of 6. Well it looked like something. I added that parameter to odbc.ini:

[testdb]
Driver=/u01/app/oracle/product/19/ogg_pg_home1/lib/GGpsql25.so
Description=PostgreSQL 12.6
Database=testdb
HostName=ora
PortNumber=5432
TransactionErrorBehavior=2
UnboundedNumericScale=10

Having rerun the load from scratch, I finally got the correct number in PostgreSQL:

testdb=# select * from tc.test;
 id |       n1       |       n2
----+----------------+----------------
  0 | 123.0123456789 | 123.0123456789
(1 row)

Conclusion

The ODBC driver used by GoldenGate truncates unbounded numeric columns to 6 decimal places by default. Thankfully, there is an option to alter it: Unbounded Numeric Scale. I am disposed to think that it is usually more reliable to specify a certain scale in the target database than relying on such settings.