Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?

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

データベース

-- 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
...
CREATE TABLE MARKETING_SCHEMA.USER_PARTIAL AS
SELECT
user_id
/* -- 除外したいカラムは除いてテーブルを作る
,email_address
,user_name
*/
,prefecture_code
,likesports
,likemovies
...
FROM APP_SCHEMA.USER_MA
GRANT ALL ON APP_DB TO poweruser;
GRANT SELECT ON ALL TABLES IN SCHEMA MARKETING_SCHEMA TO operuser;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment