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;
Select * from dynamic_view;
You will see two records belongs to only Reader account………..