Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_REDACT with MERGE (Oracle 12.1)
DBMS_REDACT with MERGE [message #674261] Mon, 14 January 2019 22:00 Go to next message
Michael.van.Uffelen
Messages: 1
Registered: January 2019
Junior Member
Hi I have a question related to DBMS_REDACT in combination with MERGE statement on the table that has redacted columns. I have the following policy applied to one of my tables:
BEGIN
DBMS_REDACT.add_policy(
object_schema => 'ods',
object_name => 'cust_dim',
column_name => 'cust_last_name',
policy_name => 'mask_cust_last_name',
function_type => DBMS_REDACT.partial,
function_parameters => 'VVVVVVVVVVVV,VVVVVVVVVVVV,#,2,6',
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''MVANUFFE'''
);
END;

Then when I run an update statement on this table it works:
update cust_dim
set cust_last_name = 'Michael'
where cust_idnt = '75046116'
and cust_last_name <> 'ichael'
-- 2 rows updated

Also when doing an insert it works without any errors:
insert into cust_dim (cust_key,cust_idnt,cust_last_name,process_key) values (-999,'75046116','Michael',-123);
-- 1 row inserted

However when trying to run a merge statement I get an error:
merge into cust_dim trg
using
(
select -999 cust_key
, '75046116' cust_idnt
, 'Michael' cust_last_name
, -123 process_key
from dual
)src on (src.cust_idnt = trg.cust_idnt)
when matched then update
set
trg.cust_last_name = src.cust_last_name
where trg.cust_last_name <> src.cust_last_name
;
--ORA-28081: Insufficient privileges - the command references a redacted object.
--28081. 00000 - "Insufficient privileges - the command references a redacted object."
--*Cause: The command referenced a redacted column in an
-- object protected by a data redaction policy.
--*Action: If possible, modify the command to avoid referencing any
-- redacted columns. Otherwise, drop the data redaction policies that
-- protect the referenced tables and views, or ensure that the user issuing
-- the command has the EXEMPT REDACTION POLICY system privilege, then
-- retry the operation. The EXEMPT REDACTION POLICY system privilege
-- is required for creating or refreshing a materialized view when the
-- materialized view is based on an object protected by a data redaction
-- policy. The EXEMPT REDACTION POLICY system privilege is required for
-- performing a data pump schema-level export including any object
-- protected by a data redaction policy. All data redaction policies are
-- listed in the REDACTION_COLUMNS catalog view.

Is this an known bug in Oracle?
Re: DBMS_REDACT with MERGE [message #674288 is a reply to message #674261] Wed, 16 January 2019 06:45 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2846
Registered: January 2010
Location: Connecticut, USA
Senior Member
I don't know if it is a known bug, but it definitely sounds like a bug. Issue is that, unlike UPDATE, MERGE creates so-called "stable set of rows" by selecting source and target rows, applying ON clause and making sure result set is "stable" (same row is not updated multiple times with different set of values, etc.) which becomes a driver when updating. And it looks like such set is produced by selecting all source/target columns regardless if they are used in MERGE or not. I suggest creating SR with MOS.

SY.
Re: DBMS_REDACT with MERGE [message #674289 is a reply to message #674288] Wed, 16 January 2019 07:23 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2846
Registered: January 2010
Location: Connecticut, USA
Senior Member
And here are some tests:

1. As user SCOTT:

drop table emp1 purge
/
create table emp1
  as
    select  *
      from  emp
/
grant all
  on emp1
  to u1
/
BEGIN
DBMS_REDACT.add_policy(
object_schema => 'scott',
object_name => 'emp1',
column_name => 'ename',
policy_name => 'mask_ename',
function_type => DBMS_REDACT.partial,
function_parameters => 'VVVVVVVVVVVV,VVVVVVVVVVVV,#,2,6',
expression => '1 = 1'
);
END;
/

Now I login as user who has all privileges on table SCOTT.EMP1 but doesn't have EXEMPT REDACTION POLICY privilege (user U1 in my case):

select  *
  from  scott.emp1
/

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 S####      CLERK           7902 17-DEC-80        800                    20
      7499 A####      SALESMAN        7698 20-FEB-81       2600        300         30
      7521 W###       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 J####      MANAGER         7839 02-APR-81       2975                    20
      7654 M#####     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 B####      MANAGER         7839 01-MAY-81       2850                    30
      7782 C####      MANAGER         7839 09-JUN-81       2450                    10
      7788 S####      ANALYST         7566 19-APR-87       3000                    20
      7839 K###       PRESIDENT            17-NOV-81       5000                    10
      7844 T#####     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 A####      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 J####      CLERK           7698 03-DEC-81        950                    30
      7902 F###       ANALYST         7566 03-DEC-81       3000                    20
      7934 M#####     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> update scott.emp1
   set ename = 'MOORE'
 where empno = 7900
/

1 row updated.

SQL> merge into scott.emp1 trg
using
(
select 7900 empno,
 'MOORE' ename
from dual
)src on (src.empno = trg.empno)
when matched then update
set
trg.ename = src.ename
where trg.ename <> src.ename
/
when matched then update
*
ERROR at line 8:
ORA-28081: Insufficient privileges - the command references a redacted object.


SQL> 

Now I issue MERGE that doesn't even reference redacted column ENAME:

merge into scott.emp1 trg
using
(
select 7900 empno,
 12345 sal
from dual
)src on (src.empno = trg.empno)
when matched then update
set
trg.sal = src.sal
where trg.sal <> src.sal
/
when matched then update
*
ERROR at line 8:
ORA-28081: Insufficient privileges - the command references a redacted object.


SQL> 

And based on this I suspect "stable set of rows" always validates all columns regardless if they are used or not. But no matter what, that SELECT from redacted table is internal and transparent to end user, so MERGE should be bypassing redaction regardless if end user has EXEMPT REDACTION POLICY privilege or not.
Also, we face same issue if we update view which is referencing redacted table:

SQL> create or replace
  2    view emp1_vw
  3      as
  4        select  *
  5          from  scott.emp1
  6  /

View created.

SQL> update emp1_vw
  2     set ename = 'MOORE'
  3   where empno = 7900
  4  /
   set ename = 'MOORE'
 *
ERROR at line 2:
ORA-28081: Insufficient privileges - the command references a redacted object.


SQL> update emp1_vw
  2     set sal = 12345
  3   where empno = 7900
  4  /
   set sal = 12345
 *
ERROR at line 2:
ORA-28081: Insufficient privileges - the command references a redacted object.


SQL> 

As you can see, update fails even when we aren't referencing redacted column.

But personally I don't see much value in redaction - I can still figure out redacted column value - it just takes longer. For example, I see there is ename that starts with K and is 4 characters long. So I issue:

with letters as (select chr(ascii('A') + level - 1) letter from dual connect by level <= 26)
select  letters.letter,
        emp1.*
  from  scott.emp1,
        letters
  where upper(emp1.ename) like 'K' || letter || '%'
/

LETT      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
I          7839 K###       PRESIDENT            17-NOV-81       5000                    10

SQL> 

Now we know ename is KI and issue:

SQL> with letters as (select chr(ascii('A') + level - 1) letter from dual connect by level <= 26)
  2  select  letters.letter,
  3          emp1.*
  4    from  scott.emp1,
  5          letters
  6    where upper(emp1.ename) like 'KI' || letter || '%'
  7  /

LETT      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
N          7839 K###       PRESIDENT            17-NOV-81       5000                    10

SQL> with letters as (select chr(ascii('A') + level - 1) letter from dual connect by level <= 26)
  2  select  letters.letter,
  3          emp1.*
  4    from  scott.emp1,
  5          letters
  6    where upper(emp1.ename) like 'KIN' || letter || '%'
  7  /

LETT      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
G          7839 K###       PRESIDENT            17-NOV-81       5000                    10

SQL> 

As you can see, it wasn't that difficult to get ename and it wouldn't be that difficult having adequate horse power (using bots if it would be a hacker) to get even credit card numbers.

SY.
Re: DBMS_REDACT with MERGE [message #674292 is a reply to message #674289] Wed, 16 January 2019 08:00 Go to previous messageGo to next message
John Watson
Messages: 7811
Registered: January 2010
Location: Global Village
Senior Member
A nice exposition of the limitation of redaction, SY. However, I don't see that he product is devalued. Oracle itself says in the docs something like "it is not intended to defend against users who can issue ad-hoc SQLs". I've seen value in it because you can retro-fit redaction to software where you have no (or difficult) access to the source code, or do not know in what reports or screens certain columns might be used, or have a database accessed by many applications.
Re: DBMS_REDACT with MERGE [message #674294 is a reply to message #674292] Wed, 16 January 2019 08:41 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2846
Registered: January 2010
Location: Connecticut, USA
Senior Member
Fair enough. But then why not simply add masking options to DBMS_RLS? DBMS_RLS does same as DBMS_REDACT except it always shows NULL as masked column value.

SY.
Re: DBMS_REDACT with MERGE [message #674295 is a reply to message #674294] Wed, 16 January 2019 09:00 Go to previous message
Solomon Yakobson
Messages: 2846
Registered: January 2010
Location: Connecticut, USA
Senior Member
More research:

BEGIN
DBMS_REDACT.drop_policy(
object_schema => 'scott',
object_name => 'emp1',
policy_name => 'mask_ename'
);
END;
/
BEGIN
DBMS_REDACT.add_policy(
object_schema => 'scott',
object_name => 'emp1',
column_name => 'ename',
policy_name => 'mask_ename',
function_type => DBMS_REDACT.partial,
function_parameters => 'VVVVVVVVVVVV,VVVVVVVVVVVV,#,2,6',
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''U1'''
);
END;
/

So we are masking ename when session user is U1. I am logged in as user SCOTT who, in my case, has DBA role and therefore has role based EXEMPT REDACTION POLICY privilege and issue MERGE:

merge into scott.emp1 trg
using
(
select 7900 empno,
 'MOORE' ename
from dual
)src on (src.empno = trg.empno)
when matched then update
set
trg.ename = src.ename
where trg.ename <> src.ename;

1 row merged.

SQL> rollback;

Rollback complete.

SQL> 

It works but we don't know if it worked because SCOTT is excluded from redaction policy or because SCOTT has EXEMPT REDACTION POLICY privilege. So I create stored procedure:

create or replace
  procedure p1
    is
    begin
dbms_output.put_line('Session user is ' || SYS_CONTEXT('USERENV','SESSION_USER'));
merge into scott.emp1 trg
using
(
select 7900 empno,
 'MOORE' ename
from dual
)src on (src.empno = trg.empno)
when matched then update
set
trg.ename = src.ename
where trg.ename <> src.ename;
end;
/

Procedure created.

SQL> 

It is definer rights procedure and therefore roles based privileges aren't honored. Now I execute stored procedure while connected as SCOTT:

SQL> show user
USER is "SCOTT"
SQL> set serveroutput on
SQL> exec p1
Session user is SCOTT
BEGIN p1; END;

*
ERROR at line 1:
ORA-28081: Insufficient privileges - the command references a redacted object.
ORA-06512: at "SCOTT.P1", line 5
ORA-06512: at line 1


SQL> 

Now I grant SCOTT EXEMPT REDACTION POLICY directly:

SQL> grant EXEMPT REDACTION POLICY to scott
  2  /

Grant succeeded.

SQL> exec p1
Session user is SCOTT

PL/SQL procedure successfully completed.

SQL>  

As you can see, it sounds like assumption in my first reply wasn't right. It looks like MERGE doesn't even look if redaction applies - it burps as soon as it runs into redacted table and there is no EXEMPT REDACTION POLICY privilege.

SY.

[Updated on: Wed, 16 January 2019 09:05]

Report message to a moderator

Previous Topic: Spreadsheet-like Totals and Subtotals
Next Topic: Why Do You Need to File a DBA and the Simplest Way of Filing It.
Goto Forum:
  


Current Time: Wed Mar 20 18:19:06 CDT 2019