Why not login to Qiita and try out its useful features?

We'll deliver articles that match you.

You can read useful information later.

2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

SnowflakeのManaged Access Schemaを使う

Posted at

SnowflakeのManaged Access Schemaとは

snowflakeはDWH。https://www.snowflake.com/

Managed Access SchemaはSchemaのOWNERにSchema内のオブジェクトの権限を管理させるもの。
通常TableやViewなどSchema内のオブジェクト作成すると、作成者がOWNERとなりそのオブジェクトに対してのGrant権限を持つが、このSchemaではオブジェクトのOWNERでもGrant権限を持つことはできない。
良くある運用ケースではSchemaの管理者とは別に、利用者がそのSchema以下のTableやViewの作成を好きにやって良いということがある。ただし、他の利用者には好き勝手に権限を付与しないで欲しいという時に使える。

細かいアクセスコントロールについてはsnowflakeのドキュメントを参照
https://docs.snowflake.net/manuals/user-guide/security-access-control-configure.html#creating-managed-access-schemas

Managed Access Schemaの作成方法

https://docs.snowflake.net/manuals/sql-reference/sql/create-schema.html#create-schema
WITH MANAGED ACCESS を付けるだけ


CREATE [ OR REPLACE ] [ TRANSIENT ] SCHEMA [ IF NOT EXISTS ] <name>
  [ CLONE <source_schema>
        [ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ] ]
  [ WITH MANAGED ACCESS ]

作成例

スキーマ管理者側の操作

use database testdb;
-- managed_testスキーマを作成
create schema managed_test with managed access;
-- managed_testスキーマのALL権限をtest_roleに対して付与
grant all on schema managed_test to test_role;

スキーマ利用者(テーブル作成)側の操作

use database testdb;
use schema managed_test;
-- table作成
create table aa_table (kkk string , rrr string);
-- table確認
select TABLE_SCHEMA, TABLE_NAME, TABLE_OWNER from information_schema.tables where TABLE_SCHEMA= 'MANAGED_TEST';
-- information_schemaのselect結果
-- Row TABLE_SCHEMA TABLE_NAME TABLE_OWNER
-- 1 MANAGED_TEST AA_TABLE TEST_ROLE

-- aa_tableの権限付与
grant select on table aa_table to sysadmin;
SQL execution error: Not authorized in a managed access schema. Use the schema owner role or a role with the MANAGE GRANTS privilege.

information_schemaの値は大文字でしかヒットしないので注意です。

2
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up

Qiita Advent Calendar is held!

Qiita Advent Calendar is an article posting event where you post articles by filling a calendar 🎅

Some calendars come with gifts and some gifts are drawn from all calendars 👀

Please tie the article to your calendar and let's enjoy Christmas together!

2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?