Snowflake Secure Views

Snowflake's secure views let you abstract the logic and hide the underlying tables structures and their relations. These secure views help you manage data security and privacy.

In this video i reviewed all the details related to secure views and demo of using them.

Commands used in the video to demonstrate snowflake's secure views. Checkout the detailed video on my channel - https://www.youtube.com/watch?v=CRZQjxP10G0&list=PLUxVA4dlJgcz90Vbufh6o6p8Rrew-lnE3&index=3


--LIST PATIENTS
SELECT * FROM PATIENTS;


CREATE OR REPLACE SECURE VIEW ORG_PATIENTS AS
SELECT * FROM PATIENTS ;


SELECT * FROM ORG_PATIENTS ;

SHOW VIEWS;


SELECT FULLNAME,CITY,USER_ACCESS.ORG_ID,USER_ACCESS.ACCOUNT_ID FROM patients 
INNER JOIN USER_ACCESS ON PATIENTS.ORG_ID=USER_ACCESS.ORG_ID;

insert into USER_ACCESS values ('Baylor Hospital','');
insert into USER_ACCESS values ('Mayo Clinic','');




create table USER_ACCESS
(
    ORG_ID VARCHAR(50),
    ACCOUNT_ID varchar(50)
);

insert into USER_ACCESS values ('Baylor Hospital','MN30689');
insert into USER_ACCESS values ('Mayo Clinic','PO51568');

SELECT * FROM USER_ACCESS; 

TRUNCATE  TABLE USER_ACCESS SET ACCOUNT_ID='PO51568' WHERE 
ORG_ID='Mayo Clinic';

SELECT FULLNAME,CITY,USER_ACCESS.ORG_ID,USER_ACCESS.ACCOUNT_ID FROM patients 
INNER JOIN USER_ACCESS ON PATIENTS.ORG_ID=USER_ACCESS.ORG_ID;


select CURRENT_ACCOUNT();
select CURRENT_ROLE ();


--CREATE A SECURE VIEW
CREATE OR REPLACE SECURE VIEW ORG_PATIENTS AS
SELECT FULLNAME,CITY,USER_ACCESS.ORG_ID FROM patients 
INNER JOIN USER_ACCESS ON PATIENTS.ORG_ID=USER_ACCESS.ORG_ID
WHERE ACCOUNT_ID=CURRENT_ACCOUNT();



select * from ORG_PATIENTS;

SHOW VIEWS;
describe view ORG_PATIENTS;

describe view mycustomerview;
describe view ORG_PATIENTS;


select get_ddl('view', 'ORG_PATIENTS') view_defn;