acl_check.md

Redshiftユーザの権限チェック用クエリ

• http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_System_information_functions.html

データベース

-- database
select
 datname
,HAS_DATABASE_PRIVILEGE(datname, 'create') as create
,HAS_DATABASE_PRIVILEGE(datname, 'temporary') as temporary
from pg_database;

• 結果例

   datname    | create | temporary
--------------+--------+-----------
 dev          | f      | t
 padb_harvest | f      | t
 template1    | f      | f
 template0    | f      | f
...

スキーマ

-- schema(namespace)
select nspname,
   has_schema_privilege(nspname, 'create') as create,
   has_schema_privilege(nspname, 'usage') as usage
from pg_namespace;

• 結果例

      nspname       | create | usage
--------------------+--------+-------
 pg_toast           | f      | f
 pg_internal        | f      | f
 pg_temp_1          | f      | f
 pg_catalog         | f      | t
 information_schema | f      | t
 public             | t      | t
...

テーブル

-- tables in current database
select tablename,
   HAS_TABLE_PRIVILEGE(tablename, 'select') as select,
   HAS_TABLE_PRIVILEGE(tablename, 'insert') as insert,
   HAS_TABLE_PRIVILEGE(tablename, 'update') as update,
   HAS_TABLE_PRIVILEGE(tablename, 'delete') as delete,
   HAS_TABLE_PRIVILEGE(tablename, 'references') as references
   HAS_TABLE_PRIVILEGE(tablename, 'references') as references
from pg_tables where schemaname='public' order by tablename;

• 結果例

 tablename  | select | insert | update | delete | references
------------+--------+--------+--------+--------+------------
 category   | t      | f      | f      | f      | f
 date       | t      | f      | f      | f      | f
 event      | t      | f      | f      | f      | f
 listing    | t      | f      | f      | f      | f
 sales      | t      | f      | f      | f      | f
 users      | t      | f      | f      | f      | f
 venue      | t      | f      | f      | f      | f
...

ctas.sql

CREATE TABLE MARKETING_SCHEMA.USER_PARTIAL AS 
SELECT
  user_id
 /* -- 除外したいカラムは除いてテーブルを作る
 ,email_address
 ,user_name
  */
 ,prefecture_code
 ,likesports
 ,likemovies
 ...
FROM APP_SCHEMA.USER_MA

db-obj.png

grant_all_database.sql

GRANT ALL ON APP_DB TO poweruser;

grant_select_schema.sql

GRANT SELECT ON ALL TABLES IN SCHEMA MARKETING_SCHEMA TO operuser;

rs_ctas.png