Amazon Redshift Useful SQL: テーブル別実行権限設定の一覧をもう少し分かり易く表示する

Amazon Redshiftで良く使いそうな便利系SQL

#Amazon Redshift

#AWS

しんや

2014.10.22

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Amazon RedshiftではPostgreSQL準拠に倣い、\zというコマンドでテーブルに設定されているユーザーやグループの権限を表示させる事が出来ます。

ただこのコマンド、一発で表示出来る一方で若干見辛い部分もあり(個人的な感想としてはそう思いました)、一覧性としては視認性に掛けるなぁと少々思っておりました。特に一番右の権限部分)

cmtestdb-# \z                         
                        Access privileges
  schema    |     name      | type  |            access privileges             
------------+---------------+-------+------------------------------------------
public      | view_xxx      | view  | 
public      | table_aaa     | table | root=arwdRxt/root          +
            |               |       | group cm_group_aaa=r/root  +
            |               |       | group cm_group_bbb=r/root
cmtest      | table_bbb     | table | root=arwdRxt/root          +
            |               |       | group cm_group_aaa=r/root  +
            |               |       | group cm_group_bbb=r/root  +
            |               |       | group cm_group_zzz=arwdRxt/root

そこで以下のSQLを実行してみます。権限情報表示に必要な要素を再構成し、不要な情報(カタログ情報)の除去、必要な情報(任意のスキーマを指定)の抽出等を行っています。

SELECT
  use.usename as subject, 
  nsp.nspname as namespace, 
  c.relname as item, 
  c.relkind as type, 
  use2.usename as owner, 
  c.relacl 
FROM
  pg_user use 
  cross join pg_class c 
  left join pg_namespace nsp on (c.relnamespace = nsp.oid) 
  left join pg_user use2 on (c.relowner = use2.usesysid)
WHERE
  c.relowner = use.usesysid  
  and nsp.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
  and nsp.nspname in('public','staging','xxxxxxxx') /** 任意のスキーマを指定 */
  and type in('r','v')
ORDER BY
  subject,namespace,item;

以下が実行結果。権限部分が一列で表示され、一覧で見た際にも比較確認が気持ち分かり易くなっているかと思います。(テーブル件数が増えてくるとこれが地味に効いてくるのです...)

  subject  | namespace |   item    | type | owner |            relacl           
-----------+-----------+-----------+------+-------+---------------------------------------
root       | public    | view_xxx  | v    | root  |
root       | public    | table_aaa | r    | root  | {root=arwdRxt/root,"group cm_group_aaa=r/root","group cm_group_bbb=r/root"}
root       | cmtest    | table_bbb | r    | root  | {root=arwdRxt/root,"group cm_group_aaa=r/root","group cm_group_bbb=r/root","group cm_group_zzz=arwdRxt/root"}

まとめ

以上、Amazon Redshiftに於けるテーブルアクセス権限情報(Access privileges)の詳細確認用SQLのご紹介でした。より分かり易く見せるのであればここからマトリックス表のような形で図示出来る方向に持って行けると良いかなぁと思っているのですが...欲を言えばこの辺は管理コンソールで良い感じに表示管理出来ると最高かな〜と思っております。ユーザーやグループ、スキーマが増えてくるとどうしてもこの辺は煩雑になってしまい、管理も大変になって来ますので...。

以前エントリとしてまとめていた『Amazon Redshiftで良く使いそうな便利系SQL』ですが、"まとめる"となるとボリュームも大きくなってしまい、また"ある程度まとまる"まで時間を置く必要が出て来てスピード感に乏しくなってしまうので、シリーズとして小出しにして行こうと思います。こちらからは以上です。