12.1.0.2
Oracle Solaris on SPARC (64-bit)
10
The DBMS_REDACT package has one issue. When a policy has been created on a table the following error/exception is thrown when attempting to edit a record from a view with and instead of trigger (updateable view), ORA-28081: Insufficient privileges - the command references a redacted object. The users have no extra roles or permissions that would prohibit the redact policy from working, just a simple select, insert, update, and delete. The redact policy works fine and as expected directly with the table, however, will not work when editing data from a view with an instead of trigger (updateable view).
Some users can see PII information and they should not have access- Is this expected or unexpected behavior?
-- Create table
create table REDACT_TEMP
(
cont_cn VARCHAR2(40) default sys_guid() not null,
last_name VARCHAR2(30) not null,
middle_name VARCHAR2(30),
first_name VARCHAR2(30)
)
tablespace USERS
pctfree 1
initrans 1
maxtrans 255
storage
(
initial 80K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table REDACT_TEMP
add constraint CN_PK primary key (CONT_CN)
using index
tablespace INDEXES
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
CREATE OR REPLACE TRIGGER REDACT_TEMP_BIU
BEFORE INSERT OR UPDATE
ON REDACT_TEMP REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
-- construct a CN
IF inserting AND
:NEW.cont_cn is null THEN
:NEW.cont_cn := Sys_Guid();
END IF;
END;
/
-- Grant/Revoke object privileges
grant select, insert, update, delete on REDACT_TEMP to II_CNTCT_MGR;
grant select on REDACT_TEMP to PUBLIC;
--
create or replace view redact_temp_vw as
select CONT_CN,
LAST_NAME,
MIDDLE_NAME,
FIRST_NAME
from redact_temp;
grant select, insert, update, delete on redact_temp_vw to ii_cntct_mgr; (NOTE: This is a role)
grant select on redact_temp_vw to public;
--
create redact policy
declare
ncount number;
begin
select count(*) into ncount from redaction_columns
where column_name = 'LAST_NAME'
and object_name = (select object_name from redaction_policies
where policy_name = 'redact_lastname');
if ncount = 0 then
begin
-- Test statements here
DBMS_REDACT.ADD_POLICY(
object_schema => 'fsdba',
object_name => 'redact_temp',
column_name => 'last_name',
policy_name => 'redact_lastname',
function_type => DBMS_REDACT.FULL,
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''IWEB_READ_ONLY''');
end;
end if;
end;
/
grant the role to a basic user and try to update or insert from the view.
Referenced the following docs thus far yet issue still persists:
Error 'ORA-28081: Insufficient privileges - the command references a redacted object' occurs when performing DML/DDL by a schema on a table where one of the column has a redaction policy enabled (Doc ID 1922132.1)
https://docs.oracle.com/database/121/ASOAG/redaction_config.htm#ASOAG10591