Understanding Caller’s Rights and Owner’s Rights in Snowflake Stored Procedures скачать в хорошем качестве

Understanding Caller’s Rights and Owner’s Rights in Snowflake Stored Procedures

A stored procedure runs with either the caller’s rights or the owner’s rights. It cannot run with both at the same time. This video describes the differences between a caller’s rights stored procedure and an owner’s rights stored procedure. Documentation: ------------------------------- https://docs.snowflake.com/en/sql-ref... Prerequisite: ------------------------------- Working with Roles and Users in Snowflake    • Working with Roles and Users in Snowflake   SQL Queries in AccountAdmin role: -------------------------------------------------------------------- DROP DATABASE IF EXISTS RAMU; DROP DATABASE IF EXISTS DB2; CREATE OR REPLACE DATABASE RAMU; create or replace table source_table( emp_no int,emp_name text,salary int, hra int ); INSERT INTO source_table VALUES (100, 'A' ,2000, 100), (101, 'B' ,5000, 300), (102, 'C' ,6000, 400), (103, 'D' ,500, 50), (104, 'E' ,15000, 3000), (105, 'F' ,150000, 20050); SELECT * FROM source_table; CREATE OR REPLACE DATABASE DB2; drop role if exists analyst_group; drop user if exists data_analyst ; create role analyst_group; grant usage on warehouse compute_wh to role analyst_group; GRANT USAGE ON DATABASE DB2 TO ROLE analyst_group; GRANT USAGE ON SCHEMA DB2.PUBLIC TO ROLE analyst_group; create user data_analyst password='abc123' default_role = analyst_group; grant role analyst_group to user data_analyst; CREATE or replace PROCEDURE RAMU.PUBLIC.copy_table_owner(SRC_TABLE_NAME STRING,TARGET_TABLE_NAME STRING) RETURNS STRING LANGUAGE JAVASCRIPT EXECUTE AS OWNER AS $$ var sql_stmt = "CREATE TRANSIENT TABLE DB2.PUBLIC."+TARGET_TABLE_NAME+" as select * from RAMU.PUBLIC."+SRC_TABLE_NAME var clone_stmt = snowflake.createStatement( {sqlText: sql_stmt} ); clone_stmt.execute(); var grant = "GRANT SELECT ON TABLE DB2.PUBLIC."+TARGET_TABLE_NAME +" TO ROLE analyst_group" //return grant var grant_usage = snowflake.createStatement( {sqlText: grant} ); grant_usage.execute(); $$ ; grant usage on procedure RAMU.PUBLIC.copy_table_owner(VARCHAR,VARCHAR) to role analyst_group; GRANT USAGE ON DATABASE RAMU TO ROLE analyst_group; GRANT USAGE ON SCHEMA RAMU.PUBLIC TO ROLE analyst_group; CREATE or replace PROCEDURE RAMU.PUBLIC.copy_table_caller(SRC_TABLE_NAME STRING,TARGET_TABLE_NAME STRING) RETURNS STRING LANGUAGE JAVASCRIPT EXECUTE AS CALLER AS $$ var sql_stmt = "CREATE TRANSIENT TABLE DB2.PUBLIC."+TARGET_TABLE_NAME+" as select * from RAMU.PUBLIC."+SRC_TABLE_NAME var clone_stmt = snowflake.createStatement( {sqlText: sql_stmt} ); clone_stmt.execute(); var grant = "GRANT SELECT ON TABLE DB2.PUBLIC."+TARGET_TABLE_NAME +" TO ROLE analyst_group" //return grant var grant_usage = snowflake.createStatement( {sqlText: grant} ); grant_usage.execute(); $$ ; grant usage on procedure RAMU.PUBLIC.copy_table_caller(VARCHAR,VARCHAR) to role analyst_group; GRANT CREATE TABLE ON SCHEMA DB2.PUBLIC TO ROLE analyst_group; GRANT SELECT ON TABLE RAMU.PUBLIC.SOURCE_TABLE TO ROLE analyst_group; SQL Queries in analyst_group role: -------------------------------------------------------------------- CALL RAMU.PUBLIC.copy_table_owner('source_table','tb1'); select * from db2.public.tb1; CALL RAMU.PUBLIC.copy_table_caller('source_table','tb2'); select * from db2.public.tb2; Check this playlist for more Data Engineering related videos:    • Demystifying Data Engineering with Cloud C...   Snowflake Complete Course from scratch with End-to-End Project with in-depth explanation-- https://doc.clickup.com/37466271/d/h/... 🙏🙏🙏🙏🙏🙏🙏🙏 YOU JUST NEED TO DO 3 THINGS to support my channel LIKE SHARE & SUBSCRIBE TO MY YOUTUBE CHANNEL