Home » SQL & PL/SQL » SQL & PL/SQL » data masking (Oracle 12c 2)
data masking [message #677328] Thu, 12 September 2019 06:31 Go to next message
sss111ind
Messages: 608
Registered: April 2012
Location: India
Senior Member

Hi All,

I am trying to execute the example provided in the local database, but the column card_no is not getting masked but in example it has been displayed as 0. What is going wrong . So any configuration need to be done to enable same ,please help.

https://oracle-base.com/articles/12c/data-redaction-12cr1

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 (1, 4000, 1234123412341234, '1234-1234-1234-1234', TRUNC(ADD_MONTHS(SYSDATE,12)), 123, NULL);
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;


 

BEGIN
  DBMS_REDACT.add_policy(
    object_schema => 'test',
    object_name   => 'payment_details',
    column_name   => 'card_no',
    policy_name   => 'redact_card_info',
    function_type => DBMS_REDACT.full,
    expression    => '1=1'
  );
END;
/


select * from payment_details;





Thank you.
Re: data masking [message #677329 is a reply to message #677328] Thu, 12 September 2019 06:37 Go to previous messageGo to next message
John Watson
Messages: 8022
Registered: January 2010
Location: Global Village
Senior Member
Perhaps your account has the EXEMPT REDACTION POLICY privilege, directly or through a role.

You could ask the author of the article, you know. It is his code, not mine.
Re: data masking [message #677331 is a reply to message #677329] Thu, 12 September 2019 07:27 Go to previous messageGo to next message
cookiemonster
Messages: 13685
Registered: September 2008
Location: Rainy Manchester
Senior Member
You make it sound like that web-page contains a load of custom code.
It's just got examples of calls to dbms_redact.
Re: data masking [message #677334 is a reply to message #677329] Thu, 12 September 2019 07:42 Go to previous messageGo to next message
sss111ind
Messages: 608
Registered: April 2012
Location: India
Senior Member

Thank you all,

How to make the user to include EXEMPT REDACTION POLICY, So that i can apply the policies. Because production sensitive data which were exported into UAT requires to be masked.

Re: data masking [message #677335 is a reply to message #677334] Thu, 12 September 2019 07:45 Go to previous messageGo to next message
John Watson
Messages: 8022
Registered: January 2010
Location: Global Village
Senior Member
I don't understand what you are asking now.

Do you have Tim Hall's example working yet? Redaction is all straightforward.
Re: data masking [message #677336 is a reply to message #677334] Thu, 12 September 2019 07:54 Go to previous messageGo to next message
cookiemonster
Messages: 13685
Registered: September 2008
Location: Rainy Manchester
Senior Member
sss111ind wrote on Thu, 12 September 2019 13:42
Thank you all,

How to make the user to include EXEMPT REDACTION POLICY, So that i can apply the policies. Because production sensitive data which were exported into UAT requires to be masked.

As John already said EXEMPT REDACTION POLICY is a privilege (like CREATE TABLE) if a user has it then that user is unaffected by any redaction policies. So if you want redaction to work you need to revoke that privilege not include it.
So you need to check if the priv is assigned either to the user directly or to a role that's been granted to the user (the EXP_FULL_DATABASE role has by default for example (and DBA is given EXP_FULL_DATABASE)).

Once you've found where that priv is assigned you need to then do one these as appropriate:
a) revoke the priv from the user
b) revoke the priv from the role
c) revoke the role from the user.
Re: data masking [message #677355 is a reply to message #677336] Fri, 13 September 2019 03:25 Go to previous message
sss111ind
Messages: 608
Registered: April 2012
Location: India
Senior Member

Thank you so much, CookieMonster,John Watson for detailed explanation.

From MAINUSER i executed the whole script and then given grant on the table to SLAVEUSER.
When executed the select statement from SLAVEUSER data redaction applied as per tim hall's example.
Previous Topic: Sql query to group the values in comma seperated values
Next Topic: help around LIST_AGG function
Goto Forum:
  


Current Time: Mon Oct 14 03:06:55 CDT 2019