Masking Policy in Cloning and Sharing: In Continuation of the previous Dynamic Data masking post, one of the question comes to our mind whether Cloning the table to the different Database or Schema in same account will unset the Masking policy.
The answer is ‘NO’ which means all the masking policy defined on the table will remain intact while cloning to the different DB in same Account. User belongs to the Cloned DB will not be able to view the mask data in the table.
In below screenshot, I have created a Database clone of DEMO_DB and this way our Employee table also got available in the new Database.
When we queried the table, we found no values in SSN and Phone column as there was Masking policies were applied in the original DEMO_DB on these two columns and same has been migrated while cloning.
To make the data visible in Cloned Schema, FLM and OPS roles needs to be create and grant the privileges to ROLE in below way:
grant usage on schema DEMO_DB_CLON.public to role FLM;
grant usage,monitor on database DEMO_DB_CLON to role FLM;
GRANT SELECT,INSERT ON DEMO_DB_CLON.PUBLIC.Employee TO ROLE FLM
Now Login to the FLM and query the table.
In the similar fashion we need to grant the privileges to Role OPS in Cloned DB to view the masked data.
Users in a reader account can query data that has been shared with it. But cannot perform any of the DML tasks like in a full account (data loading, insert, update, etc.).
Create the Reader Account:
Create the Share:
create share sales_s;
grant usage on database DEMO_DB to share sales_s;
grant usage on schema DEMO_DB.PUBLIC to share sales_s;
grant select on table DEMO_DB.PUBLIC.EMPLOYEE to share sales_s;
ALTER SHARE sales_s ADD ACCOUNTS=KDA13524
SHOW GRANTS OF SHARE sales_s
Add the Reader Account as Consumer in your Trial/Admin account.
Complete all the steps to setup the Reader Account, Import DB in Reader Account and Create DB in Reader Account.
Now login to the Reader Account:
As we can see even sharing the Table to the Another account, Masking polices remain intact.
Masking Policy in Cloning and Sharing remains intact and need to create the ROLE with same name available in Originated DB in order to view the masked data.
In next discussion we will try to understand the Masking Policies behavior during the Replication of Database/Table to another Account.