[Redshift] OJBECT, 스키마, 데이터베이스, Defult ACL 등 권한 조회 sql

redshift 전반적인 권한 확인할때 유용한 쿼리 

 

WITH grantor AS
(
       SELECT usesysid,
              usename
         FROM pg_user), schemas AS
(
       SELECT oid,
              nspname,
              nspacl,
              nspowner
         FROM pg_namespace), grantee AS (
(
       SELECT pg_user.usesysid AS usesysid,
              0                AS grosysid,
              pg_user.usename  AS usename
         FROM pg_user
        UNION ALL
       SELECT 0                 AS usesysid,
              pg_group.grosysid AS grosysid,
              pg_group.groname  AS usename
         FROM pg_group)
UNION ALL
SELECT 0              AS usesysid,
       0              AS grosysid,
       'PUBLIC'::name AS usename)
SELECT privs.schemaname,
       privs.objname,
       privs.objtype,
       privs.objowner,
       privs.grantor,
       privs.grantee
FROM (
              -- Functions grants
              SELECT sc.nspname,
                     textin(regprocedureout(c.oid::regprocedure)),
                     'Function',
                     pg_get_userbyid(c.proowner),
                     g.usename,
                     u.usename
                FROM pg_proc c
                join schemas sc
                  ON c.pronamespace=sc.oid,
                     grantor g,
                     grantee u
               WHERE EXISTS
                     (
                            SELECT 1
                             WHERE aclcontains(c.proacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'EXECUTE',FALSE)))
              UNION ALL
              -- Language grants
              SELECT NULL,
                     c.lanname,
                     'Language',
                     NULL,
                     g.usename,
                     u.usename
                FROM pg_language c,
                     grantor g,
                     grantee u
               WHERE EXISTS
                     (
                            SELECT 1
                             WHERE aclcontains(c.lanacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'USAGE',FALSE)))
              UNION ALL
              --Tables grants
              SELECT sc.nspname,
                     c.relname,
                     CASE c.relkind
                            WHEN 'r' THEN 'Table'
                            WHEN 'v' THEN 'View'
                     END,
                     pg_get_userbyid(c.relowner),
                     g.usename,
                     u.usename
                FROM pg_class c
                join schemas sc
                  ON c.relnamespace=sc.oid,
                     grantor g,
                     grantee u
               WHERE EXISTS
                     (
                            SELECT 1
                             WHERE aclcontains(c.relacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'SELECT',FALSE))
                            UNION ALL
                            SELECT 1
                             WHERE aclcontains(c.relacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'DELETE',FALSE))
                            UNION ALL
                            SELECT 1
                             WHERE aclcontains(c.relacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'INSERT',FALSE))
                            UNION ALL
                            SELECT 1
                             WHERE aclcontains(c.relacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'UPDATE',FALSE))
                            UNION ALL
                            SELECT 1
                             WHERE aclcontains(c.relacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'REFERENCES',FALSE)))
              UNION ALL
              -- Schema grants
              SELECT NULL,
                     c.nspname,
                     'Schema',
                     pg_get_userbyid(c.nspowner),
                     g.usename,
                     u.usename
               FROM pg_namespace c,
                     grantor g,
                     grantee u
               WHERE EXISTS
                     (
                            SELECT 1
                             WHERE aclcontains(c.nspacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'USAGE',FALSE))
                            UNION ALL
                            SELECT 1
                             WHERE aclcontains(c.nspacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'CREATE',FALSE)))
              UNION ALL
              -- Database grants
              SELECT NULL,
                     c.datname,
                     'Database',
                     pg_get_userbyid(c.datdba),
                     g.usename,
                     u.usename
               FROM pg_database c,
                     grantor g,
                     grantee u
               WHERE EXISTS
                     (
                            SELECT 1
                             WHERE aclcontains(c.datacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'CREATE',FALSE))
                            UNION ALL
                            SELECT 1
                             WHERE aclcontains(c.datacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'TEMP',FALSE)))
              UNION ALL
              --Default ACL grants
              SELECT    sc.nspname,
                        decode(c.defaclobjtype,
                               'r','Tables',
                               'f','Functions'),
                        'Default ACL '
                                  ||decode(c.defaclnamespace,
                                           0,'User',
                                           'Schema'),
                        pg_get_userbyid(c.defacluser),
                        g.usename,
                        u.usename
                FROM pg_default_acl c
               left  join schemas sc
              ON        c.defaclnamespace=sc.oid,
                        grantor g,
                        grantee u
               WHERE    EXISTS
                        (
                               SELECT 1
                                WHERE aclcontains(c.defaclacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'SELECT',FALSE))
                               UNION ALL
                               SELECT 1
                                WHERE aclcontains(c.defaclacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'DELETE',FALSE))
                               UNION ALL
                               SELECT 1
                                WHERE aclcontains(c.defaclacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'INSERT',FALSE))
                               UNION ALL
                               SELECT 1
                                WHERE aclcontains(c.defaclacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'UPDATE',FALSE))
                               UNION ALL
                               SELECT 1
                                WHERE aclcontains(c.defaclacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'REFERENCES',FALSE))
                               UNION ALL
                               SELECT 1
                                WHERE aclcontains(c.defaclacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'EXECUTE',FALSE)))
                 UNION ALL
                 --Default ACL grants with empty acl
                 SELECT    sc.nspname,
                           decode(c.defaclobjtype,
                                  'r','Tables',
                                  'f','Functions'),
                           'Default ACL '
                                     ||decode(c.defaclnamespace,
                                              0,'User',
                                              'Schema'),
                           pg_get_userbyid(c.defacluser),
                           NULL,
                           decode(c.defaclobjtype,
                                  'r','Regrant privileges on tables to owner',
                                  'f','Regrant privileges on Functions to owner and PUBLIC')
                   FROM pg_default_acl c
                  left  join schemas sc
                 ON        c.defaclnamespace=sc.oid
                  WHERE    EXISTS
                           (
                                  SELECT 1
                                   WHERE defaclacl='{}'::aclitem[]) ) privs("schemaname","objname","objtype","objowner","grantor","grantee")
 WHERE schemaname = 'testdb';

 

 

 

출처 : https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminScripts/user_to_be_dropped_privs.sql