目次
Snowflakeのプロシージャを作成するとき、パラメータに 「EXECUTE AS OWNER/CALLER」というものがあります。
所有者権限で実行するプロシージャの場合は、所有者が必要な権限を持っていれば、呼び出す側はなんの権限もなくてもいいのかな～？の答えがわからなかったので、実験してみました。
所有者権限で実行するプロシージャの場合でも、呼び出すロールにDatabaseとSchemaとProcedureへの使用権限は必要でした。
しかし、プロシージャがアクセスするテーブル等のオブジェクトに対する権限はなくてもOKでした。
言葉で書くとややこしいので図にしました。（あまりうまくない）
また、実際にSnowflakeでやったSQLを添付いたします。
まずはロール、DB、ユーザーの準備です。
--=====================================
--sysadmin業務
--=====================================
--ウェアハウス作成
use role sysadmin;
CREATE OR REPLACE WAREHOUSE XS
WITH
WAREHOUSE_SIZE = 'XSMALL'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE
INITIALLY_SUSPENDED = TRUE;
--DB作成
use role sysadmin;
CREATE OR REPLACE DATABASE db_A;
---------------------------------------
--=====================================
--useradmin業務
--=====================================
--ロール作成
use role useradmin;
CREATE OR REPLACE ROLE role_A;
CREATE OR REPLACE ROLE role_B;
--ユーザー作成
use role useradmin;
CREATE OR REPLACE USER user_A
PASSWORD = 'truestar1234'
LOGIN_NAME = user_A
DEFAULT_ROLE = role_A;
CREATE OR REPLACE USER user_B
PASSWORD = 'truestar1234'
LOGIN_NAME = user_B
DEFAULT_ROLE = role_B;
--ユーザーにロールを付与
use role useradmin;
GRANT ROLE role_A TO USER user_A;
GRANT ROLE role_B TO USER user_B;
---------------------------------------
--=====================================
--WH・DBの所有者業務(今回はsysadmin作成したからsysadmin業務)
--=====================================
--ロールにウェアハウスの使用権限を付与
use role sysadmin;
GRANT USAGE ON WAREHOUSE XS TO ROLE role_A;
GRANT USAGE ON WAREHOUSE XS TO ROLE role_B;
--ロールにDBの所有権を付与
use role sysadmin;
GRANT OWNERSHIP ON DATABASE db_A TO ROLE role_A;
---------------------------------------
use role accountadmin;
show grants to role role_A;
/*
created_on privilege granted_on name granted_to grantee_name grant_option granted_by
2024-04-07 17:26:31.297 -0700 OWNERSHIP DATABASE DB_A ROLE ROLE_A true ROLE_A
2024-04-07 17:26:10.895 -0700 USAGE WAREHOUSE XS ROLE ROLE_A false SYSADMIN
*/
--=====================================
--これ以降、user_Aで検証
--=====================================
user_Aで入り直し、プロシージャを作成
/*-----------------------
role_a は db_a の所有者
role_a は user_a に付与されている
role_b は db_a へのアクセス権なし
role_b は user_b に付与されている
-----------------------*/
use database db_a;
create schema sh;
use schema sh;
--user_a の role_a で db_a に tbl_a を作成する
CREATE OR REPLACE TABLE db_a.sh.tbl_a (
col1 NUMBER
);
insert into tbl_a (col1) values (1),(2),(3);
select * from db_a.sh.tbl_a;
--user_a の role_a で db_a に、 tbl_a のレコードを削除する proc_a を「所有者権限実行」で作成する
CREATE OR REPLACE PROCEDURE db_a.sh.proc_a ()
RETURNS varchar
LANGUAGE SQL
EXECUTE AS OWNER
AS
BEGIN
delete from tbl_a where col1 = 1;
RETURN 'OK';
END;
--user_bで検証
--user_bでproc_aの実行がエラーになったので、実行権限付与
GRANT USAGE ON PROCEDURE db_a.sh.proc_a() TO ROLE role_b;
show grants to role role_b;
/*
created_on privilege granted_on name granted_to grantee_name grant_option granted_by
2024-04-07 18:33:54.166 -0700 USAGE PROCEDURE DB_A.SH."PROC_A():VARCHAR(16777216)" ROLE ROLE_B false ROLE_A
2024-04-07 17:26:19.564 -0700 USAGE WAREHOUSE XS ROLE ROLE_B false SYSADMIN
*/
--user_bで検証
--user_bでまだエラーになる
--databaseとschemaの使用権限つける
GRANT USAGE ON DATABASE db_a TO ROLE role_b;
GRANT USAGE ON ALL SCHEMAS IN DATABASE db_a TO ROLE role_b;
--user_bで検証
--user_bに tbl_a の参照権限がないのに削除できた
--======================================
--user_a の role_a で db_a に、 tbl_a のレコードを削除する proc_b を「呼び出し元権限実行」で作成する
CREATE OR REPLACE PROCEDURE db_a.sh.proc_b ()
RETURNS varchar
LANGUAGE SQL
EXECUTE AS CALLER
AS
BEGIN
delete from tbl_a where col1 = 1;
RETURN 'OK';
END;
--user_bで検証
--user_bでproc_bの実行がエラーになったので、実行権限付与
GRANT USAGE ON PROCEDURE db_a.sh.proc_b() TO ROLE role_b;
show grants to role role_b;
--user_bで検証
--user_bでまだエラーになる
--databaseとschemaの使用権限は前につけている
--呼び出し元(role_b)は tbl_a の削除権限がないからエラー
user_Bで入り直し、プロシージャを実行
/*-----------------------------------
1? user_a の role_a で db_a に、 tbl_a のレコードを削除する proc_a を「所有者権限実行」で作成する
user_b が proc_a を実行
-- → proc_a 実行権限がないのでエラー
role_b に proc_a の実行権限を付与して実行
→ user_b は tbl_a の削除権限がなくても実行できる
-----------------------------------*/
call db_a.sh.proc_a();
--Unknown user-defined function DB_A.SH.PROC_A
--user_aで、実行権限付与
--GRANT USAGE ON PROCEDURE db_a.sh.proc_a() TO ROLE role_b;
show grants to role role_b;
/*
created_on privilege granted_on name granted_to grantee_name grant_option granted_by
2024-04-07 17:26:19.564 -0700 USAGE WAREHOUSE XS ROLE ROLE_B false SYSADMIN
*/
call db_a.sh.proc_a();
--Unknown user-defined function DB_A.SH.PROC_A
--user_aで、databaseとschemaの使用権限つける
call db_a.sh.proc_a();
--走った
select * from db_a.sh.tbl_a;
--Object 'DB_A.SH.TBL_A' does not exist or not authorized.
--呼び出し元(role_b)は tbl_a の削除権限がないが、所有者は権限があるので成功
--======================================
/*-----------------------------------
user_a の role_a で db_a に、 tbl_a のレコードを削除する proc_b を「呼び出し元権限実行」で作成する
user_b が proc_b を実行
→ proc_b 実行権限がないのでエラー
role_b に proc_a の実行権限を付与して実行
→ user_b は tbl_a の削除権限がないのでエラー
-----------------------------------*/
call db_a.sh.proc_b();
--Unknown user-defined function DB_A.SH.PROC_B
--user_aで、実行権限付与
--GRANT USAGE ON PROCEDURE db_a.sh.proc_b() TO ROLE role_b;
call db_a.sh.proc_b();
--Object 'TBL_A' does not exist or not authorized.
--databaseとschemaの使用権限は前につけているがエラー
--呼び出し元(role_b)は tbl_a の削除権限がないからエラー
