3
0
In a financial institution, sensitive information such as Customer Numbers, transaction details, and customer balances are often needed for internal analysis and reporting. However, due to compliance regulations, access to these fields needs to be restricted based on the user’s role. To solve this, we’ll apply Projection Policies to ensure that only certain roles can see sensitive columns like Customer numbers. However others can still use them for joins or aggregations but without viewing the data directly. Snowflake provides several layers of data security, including Projection Policies, Masking Policies, and Row Access Policies, that work together to restrict access based on roles.
In this article, we’ll explore how these policies can be combined to secure sensitive data while still enabling teams like Risk Analysts, Fraud Detection Teams, and Auditors to perform their tasks efficiently.
In our example, We’ll use the following tables:
- PROD_TBL: Contains customer details such as Customer Number, and CRID.
- ACCESSORY: Contains accessory details such as accessory details and status.
Challenge:
- The Audit Team requires full access to sensitive columns like Customer Number for compliance checks.
- Risk Analyst should access to customer Number but see only masked value. Hence implemented Dynamic Masking Policy.
- The Fraud Detection Team needs to aggregate or analyze data but should not see the actual Customer Number, so implemented Projection Policy.
- Additionally, the data needs to be filtered based on the CRID to restrict access to only relevant records. Hence Row Masking policy applied to the table.
Technical Implementation:
Technical Implementation:
Step 1: Applying a Row Access Policy
The first layer of protection comes from Row Access Policies. We’ll create a row access policy that restricts access to data based on the CRID. This will ensure that Fraud Detection and Risk Analysts only see transactions relevant to their assigned CRIDS.
create or replace table CRID_ROLE(CRID_DATA varchar,ROLE Varchar);
insert into CRID_ROLE(CRID_DATA,ROLE) values
('2Z','FRAUD_DETECTION'),('2Z','RISK_ANALYST'),('2Z','AUDIT_TEAM'),('34','RISK_ANALYST'),('34','AUDIT_TEAM'),('TY','AUDIT_TEAM');
create or replace row access policy CRID_PER_ROLE as (LOC varchar) returns boolean ->
exists (
select 1
from CRID_ROLE e
where substr(LOC,1,2) = e.CRID_DATA
and e.ROLE = current_role());
ALTER TABLE PROD_TBL ADD ROW ACCESS POLICY CRID_PER_ROLE ON (CRID);
Step 2: Implementing the Projection Policy
Next, we implement a Projection Policy to control which users can view the Customer Number column. This policy ensures that the Audit Team and Risk Analyst can project the column. However Fraud Detection can use the Customer_number for joins but cannot retrieve it in the result set.
CREATE OR REPLACE PROJECTION POLICY customer_number_policy
AS () RETURNS PROJECTION_CONSTRAINT ->
CASE
WHEN CURRENT_ROLE() in('AUDIT_TEAM','RISK_ANALYST')
THEN PROJECTION_CONSTRAINT(ALLOW => true)
ELSE
PROJECTION_CONSTRAINT(ALLOW => false)
END;
We apply this projection policy to the customer_number column in both the PROD_TBL and ACCESSORY_TBL tables.
ALTER TABLE PROD_TBL MODIFY COLUMN CUST_NUM SET PROJECTION POLICY customer_number_policy;
ALTER TABLE ACCESSORY_TBL MODIFY COLUMN CUST_ID SET PROJECTION POLICY customer_number_policy;
Step 3: Adding a Masking Policy
The final layer of security is a Masking Policy on the customer_number column. For users who are not part of the Audit Team, we will mask the Customer_number by replacing it with a hash. So Risk Analysts can access the customer number column but with masked value.
CREATE OR REPLACE MASKING POLICY mask_customer_number
AS (val STRING) RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() = 'AUDIT_TEAM' THEN val
ELSE SHA2(val, 256)
END;
ALTER TABLE PROD_TBL MODIFY COLUMN CUST_NUM SET MASKING POLICY mask_customer_number;
ALTER TABLE ACCESSORY_TBL MODIFY COLUMN CUST_ID SET MASKING POLICY mask_customer_number;
How These Policies Work Together
With Row Access, Projection, and Masking policies in place, Snowflake processes queries in the following order:
- Row Access Policy: Filters the rows based on the CRID values.
- Projection Policy: Checks if the user is authorized to project the Customer_number column. If not, the query is rejected.
- Masking Policy: If the user is not part of the Audit Team, the customer_number is replaced with a hash.
Testing
Testing the Policies with Queries
- Fraud Detection Team :The Fraud Detection Team needs to analyze suspicious transactions, but they should only see data for their CRID and not the actual customer numbers.
use role fraud_detection;
SELECT cust.cust_num,cust.cust_stat,cust.cust_bal,txn.accessory FROM PROD_TBL cust INNER JOIN ACCESSORY_TBL txn ON cust.cust_num = txn.cust_id;
Now if we remove the Customer number from SELECT query, able to execute successfully. Though the column can be query with filter condition.
Also Row Masking gets apply and restricts the base on the CRID for fraud detection team.
2.Risk Analyst Team :The Risk Analyst able to access the CUST_NUM but should not see any actual value due to masking.
- Row Access Policy restricts access to the CRID (2z and 34).
- Masking Policy allows the Customer Number to be hash for internal joins.
3.Audit Team : The Audit Team has full access to all rows and columns, including the Customer_number.
Conclusion:
In this example, we demonstrated how Snowflake’s Projection, Masking, and Row Access Policies can be combined to secure sensitive data like Customer_number. By controlling security access at multiple levels, we ensure that the right users can perform the necessary joins and aggregations without exposing sensitive information.
- Row Access Policies ensure that users can only see data relevant to their scope (e.g., by CRID).
- Projection Policies restrict which columns can be returned in the query results.
- Masking Policies allow for secure operations using hashed values, ensuring data security while retaining analytical functionality.
