0
0
During this post we will discuss about the Conditional Policy in snowflake. Conditional Policy is a type of Column Level Security policy to dynamically mask data at query time in one column based on the value of other column. In other words, a masking policy to selectively protect the column data in a table base on values in one or more different columns.
Conditional masking policy specifies two arguments, CARD and SERVICE_CD, and these arguments are column names. The first column always specifies the column to mask. The second column is a conditional column to evaluate whether the first column should be mask or not.
Consider a scenario where we have customer Info table. Table contains Credit Card details of the all banks i.e. HDFC,ICICI,AMEX,SBI along with customer data. However, The Same table is being accessed by the respective Credit card representative. As part of the security layer one Bank representative should not be authorized to access the details of other Bank credit card. Example HDFC representative should access ICICI or SBI or other banks. We have implemented conditional masking on the Customer Table.
Below table stores the Credit card details:
- Create the Masking Policy
- CARD should be mask.
- SERVICE_CD determines the condition if CARD to be mask or not.
create or replace masking policy card_visibility as
(CARD varchar, Service_Cd string) returns varchar ->
case
when current_role() = 'HDFCREP' and CONTAINS(Service_Cd,'HDFC') =TRUE then CARD
when current_role() = 'AMEXREP' and CONTAINS(Service_Cd,'AMEX') =TRUE then CARD
when current_role() = 'ICICIREP' and CONTAINS(Service_Cd,'ICIC') =TRUE then CARD
else '***MASKED***'
end;
- Apply Masking Policy on CARD Column.
alter table if exists user_info modify column CARD set masking policy card_visibility using (CARD, Service_Cd);
- Create the below Roles.
- HDFCRep
- AMEXRep
- Assign Roles to the User.
- Grant the required privilege’s to Select the table.
Login with HDFC and verify the result.
Login with AMEX and verify the result.
Hence we can see Data gets mask based on the Authorized Role.
