Redshift Observatory

System Table Tracker

System view information_schema.role_column_grants version 1.0.43931 / 2022-11-18

View Text

SELECT CAST(u_grantor.usename AS information_schema.sql_identifier) AS grantor,
       CAST(g_grantee.groname AS information_schema.sql_identifier) AS grantee,
       CAST(current_database() AS information_schema.sql_identifier) AS table_catalog,
       CAST(nc.nspname AS information_schema.sql_identifier) AS table_schema,
       CAST(c.relname AS information_schema.sql_identifier) AS table_name,
       CAST(a.attname AS information_schema.sql_identifier) AS column_name,
       CAST(pr.type AS information_schema.character_data) AS privilege_type,
       CAST(CASE
              WHEN aclcontains(c.relacl,
                               makeaclitem(0,
                                           g_grantee.grosysid,
                                           u_grantor.usesysid,
                                           CAST(pr.type AS text),
                                           TRUE))
                THEN CAST('YES' AS text)
              ELSE CAST('NO' AS text)
            END AS information_schema.character_data) AS is_grantable
FROM pg_attribute AS a,
     pg_class AS c,
     pg_namespace AS nc,
     pg_user AS u_grantor,
     pg_group AS g_grantee,
     (((SELECT CAST('SELECT' AS varchar)

        UNION ALL

        SELECT CAST('INSERT' AS varchar))

       UNION ALL

       SELECT CAST('UPDATE' AS varchar))

      UNION ALL

      SELECT CAST('REFERENCES' AS varchar)) AS pr (type)
WHERE a.attrelid = c.oid
  AND c.relnamespace = nc.oid
  AND a.attnum > 0
  AND NOT a.attisdropped
  AND (c.relkind = CAST('r' AS "char")
    OR c.relkind = CAST('v' AS "char"))
  AND aclcontains(c.relacl,
                  makeaclitem(0,
                              g_grantee.grosysid,
                              u_grantor.usesysid,
                              CAST(pr.type AS text),
                              FALSE))
  AND CAST(g_grantee.groname AS information_schema.sql_identifier) IN (SELECT enabled_roles.role_name
                                                                       FROM information_schema.enabled_roles)

Home 3D Друк Blog Bring-Up Times Consultancy Cross-Region Benchmarks Email Forums IRC Mailing Lists Reddit Redshift Price Tracker Redshift Version Tracker Redshift Workbench System Table Tracker The Known Universe Twitter White Papers