❄️

SnowflakeのRBAC設定ファイル(SQL/Terraform)を生成するOSSツール『Roleout』

に公開

SnowflakeにおいてRBAC(Role-Based Access Control:ロールベースのアクセス権限設定)はセキュリティ管理とアクセス権付与の中心的な仕組みであり、データガバナンスや運用管理の観点から強く推奨されています。

https://docs.snowflake.com/ja/user-guide/security-access-control-overview

このRBAC設定に関して、Snowflake Labsからオープンソースとして提供されている便利ツールが存在します。それがこのエントリで取り上げる『Roleout』というデスクトップツールです。

https://github.com/Snowflake-Labs/roleout

RoleoutはSnowflake環境の設計・デプロイを加速するためのInfrastructure as Code(IaC)ツールです。GUIで設定した内容(データベース、スキーマ、仮想ウェアハウス、機能ロール、RBAC階層など)ををYAMLファイルとして作成し、その上で作成されたYAMLファイルを元に実行可能なSQLまたはTerraformコードを出力します。

本記事ではこの『Roleout』について、実際にインストールして使ってみた内容を紹介します。

Roleoutのインストール

RoleoutはWindows/macOS/Linux環境に導入可能です。私の環境はMac(Apple M4)なので本記事ではMacに導入する前提で進めていきます。

まずはMac搭載のCPUを確認。

$ uname -m
arm64

該当するインストーラとしてRoleout-2.0.1-arm64.dmgを最新リリースページからダウンロード。
https://github.com/Snowflake-Labs/roleout/releases/tag/v2.0.1

ダウンロードしたdmgファイルを実行してインストール。

実行の際、Roleoutはコード署名されていないため、macOSでは実行前に隔離を解除する必要があるとのことなので、下記コマンドを実行して対応しておきます。

$ sudo xattr -r -d com.apple.quarantine /Applications/Roleout.app
Password:

これでアプリ起動の準備は完了です。

検討事項

このツールは『設定した内容に基づき、SQLまたはTerraformの設定ファイルの形式で実行する内容を出力してくれる(RBAC設定含めて)』というものです。なので実際に作成する内容、Snowflake環境上で実行したい内容を踏まえて以下の内容について検討しておく必要があります。

  1. プロジェクト運営に先立ってどういうデータをどういうデータベース/スキーマ配下で作っていくかを検討
  2. 作成するデータベース(名)を決める
  3. 作成するデータベースで利用するウェアハウスを決める(名前及び設定)
  4. 作成するデータベースにおけるスキーマ(名)を決める
  5. 作成するデータベース配下で利用する機能ロール名を決める
  6. 作成するデータベース及びスキーマ&機能ロールのマトリクスで、どういうアクセスを行わせるか決める(アクセスロールの作成)

なお、検討に際しては下記RBACのベストプラクティスに関する情報も参考にしました。

検討した内容

検討した内容は以下の通り。まぁブログ用にシンプルな形にしたところはあります。

  • データベース: SHINYAA31_XYZ_DB_DEV
  • スキーマ:
    • SCHEMA01
    • SCHEMA02
    • SCHEMA03
  • 仮想ウェアハウス
    • WH_XYZ_BI_REPORTING(BIレポート用)
    • WH_XYZ_DEVELOP(開発者用)
    • WH_XYZ_ETL_BATCH(ETL/バッチ処理用)
  • 機能ロール(Functional Role)
    • XYZ_DEV_DEVELOPER_FR(開発者用)
    • XYZ_DEV_SERVICE_XXX_FR(サービス利用用途)
    • XYZ_DEV_VIEWER_FR(閲覧者用)
  • アクセス権限(Access Role)
    • 開発者用ロールは配下のスキーマに関してフルアクセスが可能
    • サービス利用用途のロールは配下のスキーマに関して読み書きのみ可能
    • 閲覧者ロールは配下のスキーマに関して読み取り(参照)のみ可能

ツール実践

ここまでの内容を踏まえて、ツールを使って設定を作っていきます。

アプリケーション起動。初期設定の表示と案内が為されています。プロジェクト名に任意の文字列を入力(今回はshinyaa31_rbac_testとしました)。

画面には初期設定に関する項目が幾つか存在します。意味合いは以下表を参照。ひとまずここは初期設定のまま進めました。

英文 日本語訳 初期設定
When enabled, your specified databases, virtual warehouses, functional roles, and access controls will be replicated for every environment you specify. 有効にすると、指定したデータベース、仮想ウェアハウス、機能ロール、およびアクセス制御が、指定したすべての環境に複製されます。 無効
(disabled)
Although not generally recommended, sometimes you might have a hard requirement to control access to individual tables and views, rather than at the schema level. Enable Schema Object Groups to create groups of tables and views across schemas and databases that should share an access level, and manage that access from the Access page. 一般的には推奨されませんが、スキーマレベルではなく、個々のテーブルやビューへのアクセスを制御するという厳格な要件が求められる場合があります。スキーマオブジェクトグループを有効にすると、アクセスレベルを共有するスキーマやデータベース間でテーブルとビューのグループを作成し、「アクセス」ページからそのアクセスを管理できます。 有効
(disabled)
When enabled, Roleout will force identifiers to be all upper case with no special characters. When disabled, Roleout will allow case-sensitive text with certain special characters (' ', '$', '-') for identifiers which will then be double-quoted by the deployment backend. 有効にすると、Roleout は識別子を特殊文字を含まない大文字で表記するよう強制します。無効にすると、Roleout は大文字と小文字を区別するテキストを識別子として使用し、特定の特殊文字(' '、'$'、'-')を使用できます。これらの特殊文字はデプロイメントバックエンドによって二重引用符で囲まれます。 有効
(enabled)

Databases

データベース及びスキーマに関する設定。作成対象としたいデータベース名を登録し、配下に必要なスキーマがあればそれらを追加していきます。

なお、初期設定では[Managed Access]が有効となっています。ここはそのまま有効としておきます。

Virtual Warehouses

仮想ウェアハウスに関する設定。名称含め必要な数値、文字列設定を必要なウェアハウス分入力します。

Functional Roles(機能ロール)

ユーザーに割り当てる形となるロール(利用用途別に用意するもの)に関する設定。ここでは名称のみを記載する形となります。必要数分リスト化しておきます。

Access

アクセスロールを作成する際に利用する画面。

  • データベース/スキーマ
  • Functional Roles(機能ロール)
    を掛け合わせる形で『アクセスロール』を作成します。

『データベース』と『ウェアハウス』に対してそれぞれ作成していきます。ここでは前述取り決めたルールーに基づく設定しました。

データベースにおけるアクセスロール設定


ウェアハウスにおけるアクセスロール設定

Naming Convention(命名規約設定)

生成される要素に関する命名規約を設定する事が出来ます。必要であればアレンジしてみてください。

実行(設定ファイルの出力)

ここまでの内容をファイルとして保存しておき(設定ファイル自体はyamlファイルとして保存可能)、

[{} Deploy]ボタンを押下。すると即座にDeployment Preview画面が表示され、Terraform及びSQLでの実行ファイルが画面に出力されます。今回はSQLファイルを使いたいのでプルダウン選択肢の[SQL]を選択。

ステップ毎の内容をSQLコマンドベースで確認する事が出来ます。また一連の設定ファイルについてはzipアーカイブの形でダウンロードすることも出来ます。ここでは[DOWNLOAD DEPLOYMENT FILES]を押下し、zipファイルをダウンロードします。

以下のようなファイル構成、内容で成果物が出来ました。以降の項でこの内容を見ていきます。

出力結果の確認

01 - Databases and Schemas.

01 - Databases and Schemas.sql
/** SYSADMINにロール切替. */
USE ROLE "SYSADMIN";

/** 存在確認を行いつつ任意の名前でデータベースを作成. */
CREATE DATABASE IF NOT EXISTS "SHINYAA31_XYZ_DB_DEV";

/**
存在確認を行いつつ任意の名前でスキーマも作成.
WITH MANAGED ACCESS(スキーマ所有者権限のみが権限管理可能)が追記されている.
*/
CREATE SCHEMA IF NOT EXISTS "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" WITH MANAGED ACCESS;
CREATE SCHEMA IF NOT EXISTS "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" WITH MANAGED ACCESS;
CREATE SCHEMA IF NOT EXISTS "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" WITH MANAGED ACCESS;

02 - Virtual Warehouses

02 - Virtual Warehouses.sql
/** SYSADMINにロール切替. */
USE ROLE "SYSADMIN";

/** 存在確認を行いつつ、任意の名前と設定でウェアハウスを作成(設定は最小限). */
CREATE WAREHOUSE IF NOT EXISTS "WH_XYZ_BI_REPORTING" WITH INITIALLY_SUSPENDED = TRUE WAREHOUSE_SIZE = MEDIUM;

/**
作成したウェアハウス設定に対して各種設定地を上書き指定.
リソースモニターはこの時点では作成してないのでエラーになる。
なのでここは後程の対応で良いのかもしれない.
*/
ALTER WAREHOUSE "WH_XYZ_BI_REPORTING" SET
WAIT_FOR_COMPLETION = TRUE
WAREHOUSE_SIZE = MEDIUM
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 1
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE
ENABLE_QUERY_ACCELERATION = FALSE
STATEMENT_TIMEOUT_IN_SECONDS = 7200
RESOURCE_MONITOR = shinyaa31_resource_monitor
WAREHOUSE_TYPE = STANDARD;

/** 以下、作成したウェアハウス分だけ同じ処理で作成. */
CREATE WAREHOUSE IF NOT EXISTS "WH_XYZ_DEVELOP" WITH INITIALLY_SUSPENDED = TRUE WAREHOUSE_SIZE = XSMALL;
ALTER WAREHOUSE "WH_XYZ_DEVELOP" SET
WAIT_FOR_COMPLETION = TRUE
WAREHOUSE_SIZE = XSMALL
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 1
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE
ENABLE_QUERY_ACCELERATION = FALSE
STATEMENT_TIMEOUT_IN_SECONDS = 7200
RESOURCE_MONITOR = shinyaa31_resource_monitor
WAREHOUSE_TYPE = STANDARD;

CREATE WAREHOUSE IF NOT EXISTS "WH_XYZ_ETL_BATCH" WITH INITIALLY_SUSPENDED = TRUE WAREHOUSE_SIZE = LARGE;
ALTER WAREHOUSE "WH_XYZ_ETL_BATCH" SET
WAIT_FOR_COMPLETION = TRUE
WAREHOUSE_SIZE = LARGE
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 1
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE
ENABLE_QUERY_ACCELERATION = FALSE
STATEMENT_TIMEOUT_IN_SECONDS = 7200
RESOURCE_MONITOR = shinyaa31_resource_monitor
WAREHOUSE_TYPE = STANDARD;

03 - Functional Roles

03 - Functional Roles.sql
/** USERADMINにロール切替. */
USE ROLE USERADMIN;

/**
存在確認をした上で指定の機能ロール(FR)を作成し、各ロールをSYSADMINロールに付与。
これにより、SYSADMINロールを持つユーザーは、各カスタムロールの権限も利用できるようになる(ロール階層化)
※SYSADMINが業務ロールに最低限のアクセス保証を持つパターン.
*/
CREATE ROLE IF NOT EXISTS "XYZ_DEV_DEVELOPER_FR";
GRANT ROLE "XYZ_DEV_DEVELOPER_FR" TO ROLE "SYSADMIN";

CREATE ROLE IF NOT EXISTS "XYZ_DEV_SERVICE_XXX_FR";
GRANT ROLE "XYZ_DEV_SERVICE_XXX_FR" TO ROLE "SYSADMIN";

CREATE ROLE IF NOT EXISTS "XYZ_DEV_VIEWER_FR";
GRANT ROLE "XYZ_DEV_VIEWER_FR" TO ROLE "SYSADMIN";

04 - RBAC

04 - RBAC.sql
/** ================================================================
スキーマに対応するアクセスロールを作成。
今回は1つのデータベースに対して3つのスキーマを作成しているので
都合この設定を3つ(のスキーマに対して)行っている
================================================================= */
-- Create SCHEMA01 owner access role
/** USERADMINに切替. */
USE ROLE USERADMIN;
/** アクセスロールを作成(末尾に"AR"を付与しアクセスロールであることを明示). */
CREATE ROLE IF NOT EXISTS "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
/** SECURITYADMINに切替. */
USE ROLE SECURITYADMIN;
/**
作成したスキーマの所有権(OWNERSHIP)を、
新しく作ったロール(SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR)に譲渡。
REVOKE CURRENT GRANTSオプションにより、
既存のアウトバウンド権限(他ロールへの付与権限)は全て解除される。
これにより、所有者しか権限管理できない「Managed Access Schema」的な管理が可能となる.
*/
GRANT OWNERSHIP ON SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR" REVOKE CURRENT GRANTS;
GRANT ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR" TO ROLE "SYSADMIN";

/** というのを残りのスキーマに対しても同様に実施. */
-- Create SCHEMA02 owner access role
USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
USE ROLE SECURITYADMIN;
GRANT OWNERSHIP ON SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR" REVOKE CURRENT GRANTS;
GRANT ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR" TO ROLE "SYSADMIN";

-- Create SCHEMA03 owner access role
USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
USE ROLE SECURITYADMIN;
GRANT OWNERSHIP ON SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR" REVOKE CURRENT GRANTS;
GRANT ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR" TO ROLE "SYSADMIN";


/** ================================================================
ウェアハウスに対応するアクセスロールを作成。
================================================================= */
-- Create WH_XYZ_BI_REPORTING owner access role
/** USERADMINに切替. */
USE ROLE USERADMIN;
/** 作成したウェアハウスに対するロールを作成. */
CREATE ROLE IF NOT EXISTS "WH_XYZ_BI_REPORTING_FULL_AR";
/** SECURITYADMINに切替. */
USE ROLE SECURITYADMIN;
/** ウェアハウスに関する所有権を作成したロールに付与.*/
GRANT OWNERSHIP ON WAREHOUSE "WH_XYZ_BI_REPORTING" TO ROLE "WH_XYZ_BI_REPORTING_FULL_AR";
/**
SYSADMINロールに、上記の「ウェアハウス所有者アクセス」ロールを継承付与。
これにより、SYSADMINは間接的にウェアハウスの全権管理が可能となる.
*/
GRANT ROLE "WH_XYZ_BI_REPORTING_FULL_AR" TO ROLE "SYSADMIN";

/** というのを残りのウェアハウスに対しても同様に実施. */
-- Create WH_XYZ_DEVELOP owner access role
USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS "WH_XYZ_DEVELOP_FULL_AR";
USE ROLE SECURITYADMIN;
GRANT OWNERSHIP ON WAREHOUSE "WH_XYZ_DEVELOP" TO ROLE "WH_XYZ_DEVELOP_FULL_AR";
GRANT ROLE "WH_XYZ_DEVELOP_FULL_AR" TO ROLE "SYSADMIN";

-- Create WH_XYZ_ETL_BATCH owner access role
USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS "WH_XYZ_ETL_BATCH_FULL_AR";
USE ROLE SECURITYADMIN;
GRANT OWNERSHIP ON WAREHOUSE "WH_XYZ_ETL_BATCH" TO ROLE "WH_XYZ_ETL_BATCH_FULL_AR";
GRANT ROLE "WH_XYZ_ETL_BATCH_FULL_AR" TO ROLE "SYSADMIN";


/** =======================
ここからはRBACの肝であるアクセス設定コーナー。
ツールの[ACCESS]→[DATABASES]に相当する部分.
ツールで設定した『DB/スキーマ』『機能ロール』『DBスキーマ配下で機能ロールが何を出来るのか』の設定に応じて、アクセスロール単位での詳細な設定が作成した分(今回はアクセスロール3つx機能ロール3つの計9パターン)展開されている。
- SHINYAA31_XYZ_DB_DEV_SCHEMA01に対するアクセスロール
  - SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR(読み取り専用)
  - SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR(読み取り&書き込み可能)
  - SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR(フルアクセス権限)
- SHINYAA31_XYZ_DB_DEV_SCHEMA02に対するアクセスロール
  - SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR(読み取り専用)
  - SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR(読み取り&書き込み可能)
  - SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR(フルアクセス権限)
- SHINYAA31_XYZ_DB_DEV_SCHEMA03に対するアクセスロール
  - SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR(読み取り専用)
  - SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR
  - SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR(フルアクセス権限)
======================== */

/** ======================== */
/** 読み取り専用ロールのパターン. */
/** ======================== */
-- Create and Grant Privileges to Access Role SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR
/** USERADMINに切替. */
USE ROLE USERADMIN;
/** 作成したスキーマ(SCHEMA01読み取り専用)に対するロールを作成し、SYSADMINに権限付与. */
CREATE ROLE IF NOT EXISTS "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
GRANT ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR" TO ROLE "SYSADMIN";
/** SECURITYADMINに切替. */
USE ROLE SECURITYADMIN;

/** 作成したロールに対し、スキーマ配下の各種オブジェクトの利用権限(USAGE)及び参照(SELECT)の権限を付与。
都度増える可能性のものに対しては"ON FUUTRE"オプションを付与して実行することにより柔軟に対応している. */
/** データベース(DATABASE). */
GRANT USAGE ON DATABASE "SHINYAA31_XYZ_DB_DEV" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
/** スキーマ(SCHEMA). */
GRANT USAGE ON SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
/** テーブル(TABLES). */
GRANT SELECT ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
GRANT SELECT ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
/** ビュー(VIEWS). */
GRANT SELECT ON ALL VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
GRANT SELECT ON FUTURE VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
/** シーケンス(SEQUENCES). */
GRANT USAGE ON ALL SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
GRANT USAGE ON FUTURE SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
/** ステージ(STAGES). */
GRANT USAGE ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
GRANT READ ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
GRANT USAGE ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
GRANT READ ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
/** フォーマット(FORMATS). */
GRANT USAGE ON ALL FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
GRANT USAGE ON FUTURE FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
/** ストリーム(STREAMS). */
GRANT SELECT ON ALL STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
GRANT SELECT ON FUTURE STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
/** プロシージャ(PROCEDURES). */
GRANT USAGE ON ALL PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
GRANT USAGE ON FUTURE PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
/** ファンクション(FUNCTIONS). */
GRANT USAGE ON ALL FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
/** マテリアライズドビュー(MATERIALIZED VIEWS). */
GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";
GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR";

/** ================================= */
/** 読み取り&書き込み可能なロールのパターン. */
/** ================================= */
-- Create and Grant Privileges to Access Role SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR
USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR" TO ROLE "SYSADMIN";
USE ROLE SECURITYADMIN;
/** データベース(DATABASE). */
GRANT USAGE ON DATABASE "SHINYAA31_XYZ_DB_DEV" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
/** スキーマ(SCHEMA). */
GRANT USAGE ON SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
/** テーブル(TABLES)/参照系. */
GRANT SELECT ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT SELECT ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
/** ビュー(VIEWS). */
GRANT SELECT ON ALL VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT SELECT ON FUTURE VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
/** シーケンス(SEQUENCES). */
GRANT USAGE ON ALL SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT USAGE ON FUTURE SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
/** ステージ(STAGES). */
GRANT USAGE ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT READ ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT USAGE ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT READ ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
/** フォーマット(FORMATS). */
GRANT USAGE ON ALL FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT USAGE ON FUTURE FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
/** ストリーム(STREAMS). */
GRANT SELECT ON ALL STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT SELECT ON FUTURE STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
/** プロシージャ(PROCEDURES). */
GRANT USAGE ON ALL PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT USAGE ON FUTURE PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
/** ファンクション(FUNCTIONS). */
GRANT USAGE ON ALL FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
/** マテリアライズドビュー(MATERIALIZED VIEWS). */
GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
/** テーブル(TABLES)/更新系. */
GRANT INSERT ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT UPDATE ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT DELETE ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT REFERENCES ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT INSERT ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT UPDATE ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT DELETE ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT REFERENCES ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
/** ステージ(STAGES)/更新系. */
GRANT WRITE ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT WRITE ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
/** タスク(TASK). */
GRANT MONITOR ON ALL TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT OPERATE ON ALL TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT MONITOR ON FUTURE TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";
GRANT OPERATE ON FUTURE TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR";

/** ====================== */
/** フル権限なロールのパターン. */
/** ====================== */
-- Create and Grant Privileges to Access Role SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR
USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR" TO ROLE "SYSADMIN";
USE ROLE SECURITYADMIN;
GRANT USAGE ON DATABASE "SHINYAA31_XYZ_DB_DEV" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT USAGE ON SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
/** 所有者権限(OWNERSHIP)を付与している処理が追加されている:START. */
GRANT OWNERSHIP ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT OWNERSHIP ON ALL VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT OWNERSHIP ON ALL SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT OWNERSHIP ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT OWNERSHIP ON ALL FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT OWNERSHIP ON ALL STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT OWNERSHIP ON ALL PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT OWNERSHIP ON ALL FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT OWNERSHIP ON ALL MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT OWNERSHIP ON ALL TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
/** 所有者権限(OWNERSHIP)を付与している処理が追加されている:END. */
GRANT USAGE ON DATABASE "SHINYAA31_XYZ_DB_DEV" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT USAGE ON SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT SELECT ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT SELECT ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT SELECT ON ALL VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT SELECT ON FUTURE VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT USAGE ON ALL SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT USAGE ON FUTURE SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT USAGE ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT READ ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT USAGE ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT READ ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT USAGE ON ALL FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT USAGE ON FUTURE FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT SELECT ON ALL STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT SELECT ON FUTURE STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT USAGE ON ALL PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT USAGE ON FUTURE PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT USAGE ON ALL FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT INSERT ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT UPDATE ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT DELETE ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT REFERENCES ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT INSERT ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT UPDATE ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT DELETE ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT REFERENCES ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT WRITE ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT WRITE ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT MONITOR ON ALL TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT OPERATE ON ALL TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT MONITOR ON FUTURE TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";
GRANT OPERATE ON FUTURE TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA01" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR";

-- Create and Grant Privileges to Access Role SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR
USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR" TO ROLE "SYSADMIN";
USE ROLE SECURITYADMIN;
GRANT USAGE ON DATABASE "SHINYAA31_XYZ_DB_DEV" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT USAGE ON SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT SELECT ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT SELECT ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT SELECT ON ALL VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT SELECT ON FUTURE VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT USAGE ON ALL SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT USAGE ON FUTURE SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT USAGE ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT READ ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT USAGE ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT READ ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT USAGE ON ALL FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT USAGE ON FUTURE FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT SELECT ON ALL STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT SELECT ON FUTURE STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT USAGE ON ALL PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT USAGE ON FUTURE PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT USAGE ON ALL FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";
GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR";

-- Create and Grant Privileges to Access Role SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR
USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR" TO ROLE "SYSADMIN";
USE ROLE SECURITYADMIN;
GRANT USAGE ON DATABASE "SHINYAA31_XYZ_DB_DEV" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT USAGE ON SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT SELECT ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT SELECT ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT SELECT ON ALL VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT SELECT ON FUTURE VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT USAGE ON ALL SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT USAGE ON FUTURE SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT USAGE ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT READ ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT USAGE ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT READ ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT USAGE ON ALL FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT USAGE ON FUTURE FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT SELECT ON ALL STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT SELECT ON FUTURE STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT USAGE ON ALL PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT USAGE ON FUTURE PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT USAGE ON ALL FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT INSERT ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT UPDATE ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT DELETE ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT REFERENCES ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT INSERT ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT UPDATE ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT DELETE ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT REFERENCES ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT WRITE ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT WRITE ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT MONITOR ON ALL TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT OPERATE ON ALL TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT MONITOR ON FUTURE TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";
GRANT OPERATE ON FUTURE TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR";

-- Create and Grant Privileges to Access Role SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR
USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR" TO ROLE "SYSADMIN";
USE ROLE SECURITYADMIN;
GRANT USAGE ON DATABASE "SHINYAA31_XYZ_DB_DEV" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT USAGE ON SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT OWNERSHIP ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT OWNERSHIP ON ALL VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT OWNERSHIP ON ALL SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT OWNERSHIP ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT OWNERSHIP ON ALL FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT OWNERSHIP ON ALL STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT OWNERSHIP ON ALL PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT OWNERSHIP ON ALL FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT OWNERSHIP ON ALL MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT OWNERSHIP ON ALL TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT USAGE ON DATABASE "SHINYAA31_XYZ_DB_DEV" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT USAGE ON SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT SELECT ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT SELECT ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT SELECT ON ALL VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT SELECT ON FUTURE VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT USAGE ON ALL SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT USAGE ON FUTURE SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT USAGE ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT READ ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT USAGE ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT READ ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT USAGE ON ALL FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT USAGE ON FUTURE FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT SELECT ON ALL STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT SELECT ON FUTURE STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT USAGE ON ALL PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT USAGE ON FUTURE PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT USAGE ON ALL FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT INSERT ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT UPDATE ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT DELETE ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT REFERENCES ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT INSERT ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT UPDATE ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT DELETE ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT REFERENCES ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT WRITE ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT WRITE ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT MONITOR ON ALL TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT OPERATE ON ALL TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT MONITOR ON FUTURE TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";
GRANT OPERATE ON FUTURE TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA02" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR";

-- Create and Grant Privileges to Access Role SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR
USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR" TO ROLE "SYSADMIN";
USE ROLE SECURITYADMIN;
GRANT USAGE ON DATABASE "SHINYAA31_XYZ_DB_DEV" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT USAGE ON SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT SELECT ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT SELECT ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT SELECT ON ALL VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT SELECT ON FUTURE VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT USAGE ON ALL SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT USAGE ON FUTURE SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT USAGE ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT READ ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT USAGE ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT READ ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT USAGE ON ALL FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT USAGE ON FUTURE FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT SELECT ON ALL STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT SELECT ON FUTURE STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT USAGE ON ALL PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT USAGE ON FUTURE PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT USAGE ON ALL FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";
GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR";

-- Create and Grant Privileges to Access Role SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR
USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR" TO ROLE "SYSADMIN";
USE ROLE SECURITYADMIN;
GRANT USAGE ON DATABASE "SHINYAA31_XYZ_DB_DEV" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT USAGE ON SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT SELECT ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT SELECT ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT SELECT ON ALL VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT SELECT ON FUTURE VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT USAGE ON ALL SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT USAGE ON FUTURE SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT USAGE ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT READ ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT USAGE ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT READ ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT USAGE ON ALL FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT USAGE ON FUTURE FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT SELECT ON ALL STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT SELECT ON FUTURE STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT USAGE ON ALL PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT USAGE ON FUTURE PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT USAGE ON ALL FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT INSERT ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT UPDATE ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT DELETE ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT REFERENCES ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT INSERT ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT UPDATE ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT DELETE ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT REFERENCES ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT WRITE ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT WRITE ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT MONITOR ON ALL TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT OPERATE ON ALL TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT MONITOR ON FUTURE TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";
GRANT OPERATE ON FUTURE TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR";

-- Create and Grant Privileges to Access Role SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR
USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR" TO ROLE "SYSADMIN";
USE ROLE SECURITYADMIN;
GRANT USAGE ON DATABASE "SHINYAA31_XYZ_DB_DEV" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT USAGE ON SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT OWNERSHIP ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT OWNERSHIP ON ALL VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT OWNERSHIP ON ALL SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT OWNERSHIP ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT OWNERSHIP ON ALL FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT OWNERSHIP ON ALL STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT OWNERSHIP ON ALL PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT OWNERSHIP ON ALL FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT OWNERSHIP ON ALL MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT OWNERSHIP ON ALL TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR" COPY CURRENT GRANTS;
GRANT OWNERSHIP ON FUTURE TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT USAGE ON DATABASE "SHINYAA31_XYZ_DB_DEV" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT USAGE ON SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT SELECT ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT SELECT ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT SELECT ON ALL VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT SELECT ON FUTURE VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT USAGE ON ALL SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT USAGE ON FUTURE SEQUENCES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT USAGE ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT READ ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT USAGE ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT READ ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT USAGE ON ALL FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT USAGE ON FUTURE FILE FORMATS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT SELECT ON ALL STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT SELECT ON FUTURE STREAMS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT USAGE ON ALL PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT USAGE ON FUTURE PROCEDURES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT USAGE ON ALL FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT INSERT ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT UPDATE ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT DELETE ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT REFERENCES ON ALL TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT INSERT ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT UPDATE ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT DELETE ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT REFERENCES ON FUTURE TABLES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT WRITE ON FUTURE STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT WRITE ON ALL STAGES IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT MONITOR ON ALL TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT OPERATE ON ALL TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT MONITOR ON FUTURE TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";
GRANT OPERATE ON FUTURE TASKS IN SCHEMA "SHINYAA31_XYZ_DB_DEV"."SCHEMA03" TO ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR";

/** ================================================ */
/** 作成したウェアハウスに対する設定.                      */
/** ツールの[ACCESS]→[VIRTUAL WAREHOUSES]に相当する部分. */
/** ================================================ */
/** == 作成したウェアハウス1個目(WH_XYZ_BI_REPORTING). == */
-- Create and Grant Privileges to Access Role WH_XYZ_BI_REPORTING_U_AR
/** USERADMINに切替. */
USE ROLE USERADMIN;
/** ウェアハウスに対応するロールを作成(利用専用). */
CREATE ROLE IF NOT EXISTS "WH_XYZ_BI_REPORTING_U_AR";
/** このロールをSYSADMINロールの下位ロールとして追加。
SYSADMINを持つユーザーはこのロール権限を継承. */
GRANT ROLE "WH_XYZ_BI_REPORTING_U_AR" TO ROLE "SYSADMIN";
/** SECURITYADMINに切替. */
USE ROLE SECURITYADMIN;
/** 指定のウェアハウスの利用(USAGE)権限をこのロールに付与。
これによりロールを割り当てられたユーザーのみが
このウェアハウスでSQLクエリ等の実行が可能となる. */
GRANT USAGE ON WAREHOUSE "WH_XYZ_BI_REPORTING" TO ROLE "WH_XYZ_BI_REPORTING_U_AR";

-- Create and Grant Privileges to Access Role WH_XYZ_BI_REPORTING_UM_AR
/** USERADMINに切替. */
USE ROLE USERADMIN;
/** ウェアハウスに対応するロールを作成(利用&監視専用). */
CREATE ROLE IF NOT EXISTS "WH_XYZ_BI_REPORTING_UM_AR";
/** このロールをSYSADMINロールの下位ロールとして追加。
SYSADMINを持つユーザーはこのロール権限を継承. */
GRANT ROLE "WH_XYZ_BI_REPORTING_UM_AR" TO ROLE "SYSADMIN";
/** SECURITYADMINに切替. */
USE ROLE SECURITYADMIN;
/** 指定のウェアハウスの利用(USAGE)及び監視(MONITOR)権限をこのロールに付与。
これによりロールを割り当てられたユーザーのみが
このウェアハウスでSQLクエリ等の実行が可能となる. */
GRANT USAGE ON WAREHOUSE "WH_XYZ_BI_REPORTING" TO ROLE "WH_XYZ_BI_REPORTING_UM_AR";
GRANT MONITOR ON WAREHOUSE "WH_XYZ_BI_REPORTING" TO ROLE "WH_XYZ_BI_REPORTING_UM_AR";

/** == 作成したウェアハウス2個目(WH_XYZ_DEVELOP). == */
/** 以下同上. */
-- Create and Grant Privileges to Access Role WH_XYZ_DEVELOP_U_AR
USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS "WH_XYZ_DEVELOP_U_AR";
GRANT ROLE "WH_XYZ_DEVELOP_U_AR" TO ROLE "SYSADMIN";
USE ROLE SECURITYADMIN;
GRANT USAGE ON WAREHOUSE "WH_XYZ_DEVELOP" TO ROLE "WH_XYZ_DEVELOP_U_AR";

-- Create and Grant Privileges to Access Role WH_XYZ_DEVELOP_UM_AR
USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS "WH_XYZ_DEVELOP_UM_AR";
GRANT ROLE "WH_XYZ_DEVELOP_UM_AR" TO ROLE "SYSADMIN";
USE ROLE SECURITYADMIN;
GRANT USAGE ON WAREHOUSE "WH_XYZ_DEVELOP" TO ROLE "WH_XYZ_DEVELOP_UM_AR";
GRANT MONITOR ON WAREHOUSE "WH_XYZ_DEVELOP" TO ROLE "WH_XYZ_DEVELOP_UM_AR";

/** == 作成したウェアハウス3個目(WH_XYZ_ETL_BATCH). == */
/** 以下同上. */
-- Create and Grant Privileges to Access Role WH_XYZ_ETL_BATCH_U_AR
USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS "WH_XYZ_ETL_BATCH_U_AR";
GRANT ROLE "WH_XYZ_ETL_BATCH_U_AR" TO ROLE "SYSADMIN";
USE ROLE SECURITYADMIN;
GRANT USAGE ON WAREHOUSE "WH_XYZ_ETL_BATCH" TO ROLE "WH_XYZ_ETL_BATCH_U_AR";

-- Create and Grant Privileges to Access Role WH_XYZ_ETL_BATCH_UM_AR
USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS "WH_XYZ_ETL_BATCH_UM_AR";
GRANT ROLE "WH_XYZ_ETL_BATCH_UM_AR" TO ROLE "SYSADMIN";
USE ROLE SECURITYADMIN;
GRANT USAGE ON WAREHOUSE "WH_XYZ_ETL_BATCH" TO ROLE "WH_XYZ_ETL_BATCH_UM_AR";
GRANT MONITOR ON WAREHOUSE "WH_XYZ_ETL_BATCH" TO ROLE "WH_XYZ_ETL_BATCH_UM_AR";

/** ==========================================
上記処理で作成した各種アクセスロールを機能ロールに付与.
=========================================== */
-- Grant Access Roles to Functional Roles
/** SECURITYADMINに切替. */
USE ROLE SECURITYADMIN;

/** 開発者機能ロール(XYZ_DEV_DEVELOPER_FR)へのアクセスロール付与. */
GRANT ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_FULL_AR" TO ROLE "XYZ_DEV_DEVELOPER_FR";
GRANT ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_FULL_AR" TO ROLE "XYZ_DEV_DEVELOPER_FR";
GRANT ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_FULL_AR" TO ROLE "XYZ_DEV_DEVELOPER_FR";
GRANT ROLE "WH_XYZ_BI_REPORTING_FULL_AR" TO ROLE "XYZ_DEV_DEVELOPER_FR";
GRANT ROLE "WH_XYZ_DEVELOP_FULL_AR" TO ROLE "XYZ_DEV_DEVELOPER_FR";
GRANT ROLE "WH_XYZ_ETL_BATCH_FULL_AR" TO ROLE "XYZ_DEV_DEVELOPER_FR";

/** サービス利用機能ロール(XYZ_DEV_SERVICE_XXX_FR)へのアクセスロール付与. */
GRANT ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_RW_AR" TO ROLE "XYZ_DEV_SERVICE_XXX_FR";
GRANT ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_RW_AR" TO ROLE "XYZ_DEV_SERVICE_XXX_FR";
GRANT ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_RW_AR" TO ROLE "XYZ_DEV_SERVICE_XXX_FR";
GRANT ROLE "WH_XYZ_BI_REPORTING_UM_AR" TO ROLE "XYZ_DEV_SERVICE_XXX_FR";
GRANT ROLE "WH_XYZ_DEVELOP_UM_AR" TO ROLE "XYZ_DEV_SERVICE_XXX_FR";
GRANT ROLE "WH_XYZ_ETL_BATCH_UM_AR" TO ROLE "XYZ_DEV_SERVICE_XXX_FR";

/** 閲覧者機能ロール(XYZ_DEV_VIEWER_FR)へのアクセスロール付与. */
GRANT ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA01_R_AR" TO ROLE "XYZ_DEV_VIEWER_FR";
GRANT ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA02_R_AR" TO ROLE "XYZ_DEV_VIEWER_FR";
GRANT ROLE "SHINYAA31_XYZ_DB_DEV_SCHEMA03_R_AR" TO ROLE "XYZ_DEV_VIEWER_FR";
GRANT ROLE "WH_XYZ_BI_REPORTING_U_AR" TO ROLE "XYZ_DEV_VIEWER_FR";
GRANT ROLE "WH_XYZ_DEVELOP_U_AR" TO ROLE "XYZ_DEV_VIEWER_FR";
GRANT ROLE "WH_XYZ_ETL_BATCH_U_AR" TO ROLE "XYZ_DEV_VIEWER_FR";

SQL実行

生成されたSQLファイルをそれぞれ実行してみましょう。今回はSnowflakeコンソール画面にて内容を貼り付ける形で実行しました。いずれも実行は問題なく終了し、生成後にオブジェクトの内容確認も出来ています。

  • 01 - Databases and Schemas.sql

    SHOW DATABASEコマンドで対象データベースを含む一覧を表示


*DESC DATABASEコマンドで対象データベースの内容を表示

  • 02 - Virtual Warehouses.sql(やっぱりリソースモニターの部分はエラーになりました。なのでコメントアウトして実行しています)

    リソースモニター部分をコメントアウトして実行した後のウェアハウス一覧

  • 03 - Functional Roles.sql

    SQL実行完了後のウェアハウス参照画面

  • 04 - RBAC.sql(今回の設定でSQLコマンドが466個作成されていました。1つ1つは処理時間が短いものでしたが積もり積もって10分くらい実行に掛かりました)

    実行に時間が掛かっている様子

    SQL実行完了後のウェアハウス一覧画面

まとめ

という訳で、Snowflake環境の設計・デプロイを加速するためのInfrastructure as Code(IaC)ツール『Roleout』の紹介でした。この手の初期設定は構築する環境の複雑さに応じて手間やコストも増えてしまいますが、GUI操作でイメージしやすく作業出来、実行ファイルも一式出力してくれるのは管理者・運用者としてもとてもありがたいところです。うまく活用して基盤構築のスピードを加速して行きたいところですね!

truestarテックブログ
設定によりコメント欄が無効化されています