INSERT AS SELECT in package where table has redaction

Question

I've redacted columns on a table. When developer try to use a 'INSERT AS SELECT' command in a pkg they get :

ORA-28081: Insufficient privileges - the command references a redacted object.

Apart from granting exemption to the schema (defeating the use of redaction altogether) what can I do?

The developers use the package but shouldn't see the table contents

Here is the function :

FUNCTION Create****Transaction (******PackageUserId NUMBER)
   return NUMBER
   IS
   /*****************************
      Local Variable Definitions
   ******************************/
   v_****tId NUMBER(15);


   BEGIN
        v_****Id := ***_PKG.GETNEXTAMSSEQNUM();

        INSERT INTO ****_AGENCY_PACK_USER_TRANS ****
        (
         ****_ID,
         ****_****_ID,
         ****_BUS_ID,
         ****_ROLE_ID,
         ****_DATE_FROM,
         ****_DATE_TO,
         ****_ABBR,
         ****_NOTE,
         ****_CREATED_BY,
         ****_DATE_CREATED,
         ****_AUDIT_ACTION,
         ****_AUDIT_DATE,
         ****_AUDIT_LOCATION,
         ****_AUDIT_USER,
         ****_VER_NUM,
             ****_AMSS_ID,
             ****_WEBSERVICE,
         ****_APR_ID,
             ****_ASSIGN_RULE_ALLOWED,
         ****_SUP_TAG,
         ****_CPR
         )
        (
        select
           v_****Id,
         ****_ID,
         ****_BUS_ID,
         ****_ROLE_ID,
         ****_DATE_FROM,
         ****_DATE_TO,
         ****_ABBR,
         ****_NOTE,    (THIS IS REDACTED ON THE TABLE)
                 ****_CREATED_BY,
         ****_DATE_CREATED,
         ****_AUDIT_ACTION,
         ****_AUDIT_DATE,
         ****_AUDIT_LOCATION,
         ****_AUDIT_USER,
         ****_VER_NUM,
             ****_AMSS_ID,
             ****_WEBSERVICE,
         ****_APR_ID,
             ****_ASSIGN_RULE_ALLOWED,
             ****_SUP_TAG,
             ****_CPR
        FROM
                  ***********_PACKAGE_USERS
        WHERE ****_ID = *****PackageUserId
        );

        if SQL%ROWCOUNT = 0 THEN
           dbms_output.put_line('ERROR - no rows inserted!!');
           return 0;
          else
           return v_****Id;
        end if;

   END Create****ransaction;

So the statement is referencing a redacted column in the `select` part, either explicitly or through using `*` (which is bad form anyway generally)? Isn't that what's supposed to happen? Or is the package owner the same as the table owner so you're expecting that to be OK, but the caller is different and the package has `authid current_user`, maybe? It would be helpful to expand your question to show example code or even an MCVE. — Alex Poole, Feb 20 '19 at 17:03
The asterisks make that a little hard to read *8-) Is the package owner the same as the (redacted) table owner? Does the package have an `authid` clause? — Alex Poole, Feb 21 '19 at 13:07
When the package runs, should the inserted records include the actual data in the table or the redaction? — Matthew McPeak, Feb 21 '19 at 14:47
They should include the actual data. When read back it should be redacted. The developers write/edit the package and sometimes the tables. The DBAs adminisitor the redaction on the tables. — Alan, Feb 21 '19 at 15:34

score 0 · Answer 1 · answered Mar 02 '20 at 11:04

Purpose of Redaction is to make sure no one with not having access to data should be able to view it.

Once you have applied redaction with policy 1=1 then it will be applicable for all users except SYS and it will not allow CTAS or insert as select. There is workaround or you can say a different way of providing access to rightful users.

Let me know if you need more details on this.

INSERT AS SELECT in package where table has redaction

1 Answers1