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.

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.

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:

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:

John WatsonMessages: 8274Registered: 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.

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.