Not able to see the masked data even after adding policy successfully using DBMS_REDACT in oracle 12c

Question

I was able to add the data redaction policy without any errors to a column of the table but not able to see any masked data, its all like same before, not able to hide the original. I have tried accessing table from a different user still original data is not hidden. I was able to add policies from sql developer as well, but still no luck in hiding the data.

I have give all this permission to jag user from sys.

grant select on Sys.redaction_policies to jag;
grant select on Sys.redaction_columns to jag;
grant execute on dbms_redact to jag;


CREATE TABLE payment_details (
  id          NUMBER       NOT NULL,
  customer_id NUMBER       NOT NULL,
  card_no     NUMBER       NOT NULL,
  card_string VARCHAR2(19) NOT NULL,
  expiry_date DATE         NOT NULL,
  sec_code    NUMBER       NOT NULL,
  valid_date  DATE,
  CONSTRAINT payment_details_pk PRIMARY KEY (id)
);
INSERT INTO payment_details VALUES (2, 4001, 2345234523452345, '2345-2345-2345-2345', TRUNC(ADD_MONTHS(SYSDATE,12)), 234, NULL);
INSERT INTO payment_details VALUES (3, 4002, 3456345634563456, '3456-3456-3456-3456', TRUNC(ADD_MONTHS(SYSDATE,12)), 345, NULL);
INSERT INTO payment_details VALUES (4, 4003, 4567456745674567, '4567-4567-4567-4567', TRUNC(ADD_MONTHS(SYSDATE,12)), 456, NULL);
INSERT INTO payment_details VALUES (5, 4004, 5678567856785678, '5678-5678-5678-5678', TRUNC(ADD_MONTHS(SYSDATE,12)), 567, NULL);
COMMIT;


SELECT *
FROM   payment_details
ORDER BY id;

BEGIN
  DBMS_REDACT.add_policy(
    object_schema => 'jag',
    object_name   => 'payment_details',
    column_name   => 'card_no',
    policy_name   => 'redact_card_info',
    function_type => DBMS_REDACT.partial,
 function_parameters  => '7,1,5',
    expression    => '1=1'
  );
END;
/

Run this query as jag user `select * from session_roles`.SYSDBA and DBA are exempt from redaction policies or create another user grant select on this table and test and Redaction feature available only on enterprise edition. — Suresh, Mar 31 '21 at 20:52
I ran your script twice one with select and execute privilege and another just execute privilege ,script just with execute privilege worked as expected.Run another test commenting out first two lines and make sure those users don't have sysdba or dba privileges. — Suresh, Mar 31 '21 at 21:14
@Suresh can you put this in answer, this actually resolved my issue. — JagaSrik, Apr 10 '21 at 03:36

Suresh · Accepted Answer · 2021-04-11T02:56:11.183

--Revoke select privileges on redaction policies/columns and drop objects and rerun the script

    revoke select on Sys.redaction_policies from jag;
    revoke select on Sys.redaction_columns from jag;

or comment out first two lines and rerun the script

--grant select on Sys.redaction_policies to jag;
--grant select on Sys.redaction_columns to jag;
revoke select on Sys.redaction_policies from jag;
revoke select on Sys.redaction_columns from jag;
grant execute on dbms_redact to jag;

DROP TABLE payment_details purge;


BEGIN
  DBMS_REDACT.drop_policy(
    object_schema => 'jag',
    object_name   => 'payment_details',
    column_name   => 'card_no',
    policy_name   => 'redact_card_info')
END;

CREATE TABLE payment_details (
  id          NUMBER       NOT NULL,
  customer_id NUMBER       NOT NULL,
  card_no     NUMBER       NOT NULL,
  card_string VARCHAR2(19) NOT NULL,
  expiry_date DATE         NOT NULL,
  sec_code    NUMBER       NOT NULL,
  valid_date  DATE,
  CONSTRAINT payment_details_pk PRIMARY KEY (id)
);

INSERT INTO payment_details VALUES (2, 4001, 2345234523452345, '2345-2345-2345-2345', TRUNC(ADD_MONTHS(SYSDATE,12)), 234, NULL);
INSERT INTO payment_details VALUES (3, 4002, 3456345634563456, '3456-3456-3456-3456', TRUNC(ADD_MONTHS(SYSDATE,12)), 345, NULL);
INSERT INTO payment_details VALUES (4, 4003, 4567456745674567, '4567-4567-4567-4567', TRUNC(ADD_MONTHS(SYSDATE,12)), 456, NULL);
INSERT INTO payment_details VALUES (5, 4004, 5678567856785678, '5678-5678-5678-5678', TRUNC(ADD_MONTHS(SYSDATE,12)), 567, NULL);
COMMIT;


SELECT *
FROM   payment_details
ORDER BY id;

BEGIN
  DBMS_REDACT.add_policy(
    object_schema => 'jag',
    object_name   => 'payment_details',
    column_name   => 'card_no',
    policy_name   => 'redact_card_info',
    function_type => DBMS_REDACT.partial,
 function_parameters  => '7,1,5',
    expression    => '1=1'
  );
END;
/

Not able to see the masked data even after adding policy successfully using DBMS_REDACT in oracle 12c

1 Answers1