Create Snowflake Stored Procedure : Understand Owner and Caller Stored Procedure

Understand Snowflake Stored Procedure  

Hello Guys,

Currently, there are two languages supported in snowflake to create stored procedure.

1. Javascript
2. SQL

and mostly Javascript is used language, lets understand this better.

Using $$ as the delimiter makes it easier to write stored procedures that contain single quotes.

Please follow the below steps, you would be able to understand 

1. How to create procedure in snowflake?
2. What is owner procedure? and 
3. what is caller procedure?

use role sysadmin;

--create a table 

Create or replace table Colour 

(ID int default SEQ_1.nextval,

Name varchar(20) null

);

--insert few records here 

insert into Colour 

(Name)

values

('Red'),

('Blue'),

('Orange'),

('Pink'),

('Black'),

('White');

--create a owner stored procedure 

create or replace procedure Delete_Color_Owner(NAME string)

returns float

language javascript

execute as owner 

As

$$

var statement = "Delete from snowstages_db.public.colour where Name = '"+NAME+"'";

snowflake.execute({sqlText: statement});

var sqlStatement = snowflake.execute({sqlText: 'Select count(*) from colour'});

sqlStatement.next();

var records = sqlStatement.getColumnValue(1);

return records;

$$;

--create a caller stored procedure 

create or replace procedure Delete_Color_Caller(NAME string)

returns float

language javascript

execute as caller

As

$$

var statement = "Delete from snowstages_db.public.colour where Name = '"+NAME+"'";

snowflake.execute({sqlText: statement});

var sqlStatement = snowflake.execute({sqlText: 'Select count(*) from colour'});

sqlStatement.next();

var records = sqlStatement.getColumnValue(1);

return records;

$$;

--execute the stored procedure 

call Delete_Color_Owner('Blue');

--create a role using securityadmin and provide all grants required to him

use role securityadmin;

Create or replace user JOHN;

Create or replace role taskadmin;

grant usage on database Snowstages_db to role taskadmin;

grant usage on schema snowstages_db.public to role taskadmin;

grant usage on procedure Delete_Color(string) to role taskadmin;

grant usage on warehouse compute_wh to role taskadmin;

--finally taskadmin role to user JOHN

grant role taskadmin to user JOHN;

As you can see we do not have provide any grant on table Colour to user JOHN, but still he can fetch records from this table, to check this you have to login using User JOHN.

Please follow the below my YouTube video so you can achieve it completely 😊

Regards,

Vidit - A data scientist in snowflake data warehouse