View privileged objects in Redshift

  Snippets, Technology, Redshift snippets

SELECT
   namespace AS schemaname,
   item AS object,
   pu.groname AS groupname,
   DECODE(
      charindex (
         'r',
         split_part (
            split_part (
               array_to_string (relacl, '|'),
               pu.groname,
               2
            ),
            '/',
            1
         )
      ),
      0,
      0,
      1
   ) AS
   SELECT
      ,
      DECODE(
         charindex (
            'w',
            split_part (
               split_part (
                  array_to_string (relacl, '|'),
                  pu.groname,
                  2
               ),
               '/',
               1
            )
         ),
         0,
         0,
         1
      ) AS
      UPDATE
         ,
         DECODE(
            charindex (
               'a',
               split_part (
                  split_part (
                     array_to_string (relacl, '|'),
                     pu.groname,
                     2
                  ),
                  '/',
                  1
               )
            ),
            0,
            0,
            1
         ) AS
         INSERT,
         DECODE(
            charindex (
               'd',
               split_part (
                  split_part (
                     array_to_string (relacl, '|'),
                     pu.groname,
                     2
                  ),
                  '/',
                  1
               )
            ),
            0,
            0,
            1
         ) AS
         DELETE
         FROM
            (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 item = 'fa_analystbyregion'
               AND nsp.nspname NOT IN (
                  'pg_catalog',
                  'pg_toast',
                  'information_schema'
               ))
            JOIN pg_group pu
               ON array_to_string (relacl, '|') LIKE '%' || pu.groname || '%';