Dynamic Secure View Scenario: Snowflake Security

Read Time:2 Minute, 6 Second

Secure view is only exposed to authorized users (i.e. users who have been granted the role that owns the view). In other words, Unauthorized user uses any of the following commands or interfaces, the view definition is not displayed.

SHOW VIEWS and SHOW MATERIALIZED VIEWS commands. … VIEWS Account Usage view.

In addition, Views should be defined as secure or safe when they are specifically designated for data privacy (i.e. to limit access to sensitive data and that should not be exposed to all users of the underlying table(s)).

Above all, Snowflake query optimizer, when evaluating secure views it bypasses certain optimizations used for regular views. This might result in some impact on query performance for secure views.

For this use case, I am using the Trial account and we are allowed to create only one Reader Account per trial account
In my Trial account :
Created two tables:
o Entitlement table
o Fact table

CREATE TABLE ENTITLEMENT(cUSTOMER_NAME VARCHAR2(100),CUSTOMER_ID 
VARCHAR2(10), SNOW_aCCNT VARCHAR2(100))
CREATE TABLE FACT(PRODUCT VARCHAR2(100),CUSTOMER_ID 
VARCHAR2(10),SALES NUMBER)
INSERT INTO ENTITLEMENT(cUSTOMER_NAME,CUSTOMER_ID,SNOW_aCCNT)
VALUES
('RCOCA',1,NULL),
('RPEPSI',2,NULL),
('TCOCA',3,'hc55218'),
('TPEPSI',4,'hc55218')

SNOW_ACCNT column holds the ID of Trial and Reader Account,
my trial account = ‘’hc55218′

INSERT INTO FACT(PRODUCT,CUSTOMER_ID,SALES)
VALUES
('RCOCOABOTTLE',1,5),
('RPEPSIBOTTLE',2,10),
('TCOCABOTTLE',3,20),
('TPEPSIBOTTLE',4,30)

Now create the Reader account in your trail account:

Now update the Entitlement table with the Locator ID of reader account:

update ENTITLEMENT set SNOW_ACCNT = 'SD48623' where CUSTOMER_ID in('1','2')

so this way we have two records belong to Admin account and two records belong to Reader account.

CREATE or replace SECURE VIEW DYNAMIC_VIEW AS
SELECT 
CUSTOMER_NAME,E.CUSTOMER_ID 
ENT_CUST_ID, SNOW_ACCNT,PRODUCT, F.CUSTOMER_ID FACT_CUST_ID,SALES
FROM ENTITLEMENT E,FACT F
WHERE E.CUSTOMER_ID = F.CUSTOMER_ID
AND UPPER(SNOW_ACCNT) = CURRENT_ACCOUNT()

Share secure view with Reader account via Trial account:

 create user PEPSI password='abc123' default_role = PUBLIC must_change_password = true;
 create or replace warehouse my_wh warehouse_size=large initially_suspended=true;
 create or replace resource monitor limiter with credit_quota=5000
 triggers on 75 percent do notify
 on 100 percent do suspend
 on 110 percent do suspend_immediate;
grant usage on virtual warehouse my_wh to role public;