Scribd
Upload
5 views2 pages

Dynamic Data Masking in SQL Database

The document discusses setting up dynamic data masking in a database. It creates a database and table, inserts sample data, defines roles for full and masked access, applies a masking policy to the phone column to hide numbers from non-privileged roles, and validates that the policy works as intended.

Uploaded by

clouditlab9
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
5 views2 pages

Dynamic Data Masking in SQL Database

The document discusses setting up dynamic data masking in a database. It creates a database and table, inserts sample data, defines roles for full and masked access, applies a masking policy to the phone column to hide numbers from non-privileged roles, and validates that the policy works as intended.

Uploaded by

clouditlab9
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

//// DYNAMIC DATA MASKING ///////

USE ROLE ACCOUNTADMIN;


CREATE OR REPLACE DATABASE DEMO_DB;

-- Prepare table --
create or replace table customers(
id number,
full_name varchar,
email varchar,
phone varchar,
spent number,
create_date DATE DEFAULT CURRENT_DATE);

-- insert values in table --


insert into customers (id, full_name, email,phone,spent)
values
(1,'Lewiss MacDwyer','lmacdwyer0@[Link]','262-665-9168',140),
(2,'Ty Pettingall','tpettingall1@[Link]','734-987-7120',254),
(3,'Marlee Spadazzi','mspadazzi2@[Link]','867-946-3659',120),
(4,'Heywood Tearney','htearney3@[Link]','563-853-8192',1230),
(5,'Odilia Seti','oseti4@[Link]','730-451-8637',143),
(6,'Meggie Washtell','mwashtell5@[Link]','568-896-6138',600);

-- set up roles

CREATE OR REPLACE ROLE ANALYST_MASKED;


CREATE OR REPLACE ROLE ANALYST_FULL;

-- grant usage on database and schema


GRANT USAGE ON DATABASE DEMO_DB TO ROLE ANALYST_MASKED;
GRANT USAGE ON DATABASE DEMO_DB TO ROLE ANALYST_FULL;

-- grant usage on schema


GRANT USAGE ON SCHEMA DEMO_DB.PUBLIC TO ROLE ANALYST_MASKED;
GRANT USAGE ON SCHEMA DEMO_DB.PUBLIC TO ROLE ANALYST_FULL;

-- grant select on table


GRANT SELECT ON TABLE DEMO_DB.[Link] TO ROLE ANALYST_MASKED;
GRANT SELECT ON TABLE DEMO_DB.[Link] TO ROLE ANALYST_FULL;

-- grant warehouse access to roles


GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE ANALYST_MASKED;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE ANALYST_FULL;

-- assign roles to a user


GRANT ROLE ANALYST_MASKED TO USER NIKOLAI;
GRANT ROLE ANALYST_FULL TO USER NIKOLAI;

-- Set up masking policy


create or replace masking policy demo_db.[Link]
as (val varchar) returns varchar ->
case
when current_role() in ('ANALYST_FULL', 'ACCOUNTADMIN') then val
else '##-###-##'
end;

-- Apply policy on a specific column


ALTER TABLE IF EXISTS demo_db.[Link] MODIFY COLUMN phone
SET MASKING POLICY demo_db.[Link];

-- Validating policies

USE ROLE ANALYST_FULL;


SELECT * FROM CUSTOMERS;

USE ROLE ANALYST_MASKED;


SELECT * FROM CUSTOMERS;

alter masking policy phone


set body ->
case
when current_role() in ('ANALYST_FULL', 'ACCOUNTADMIN') then val
else concat(LEFT(val,1),'##-###-##')
end;

You might also like