Redshift Observatory

System Table Tracker

System view information_schema.column_privileges version 1.0.60353 / 2023-11-24

schema name column data type
information_schema column_privileges column_name sql_identifier
information_schema column_privileges grantee sql_identifier
information_schema column_privileges grantor sql_identifier
information_schema column_privileges is_grantable character_data
information_schema column_privileges privilege_type character_data
information_schema column_privileges table_catalog sql_identifier
information_schema column_privileges table_name sql_identifier
information_schema column_privileges table_schema sql_identifier

View Text

SELECT CAST(u_grantor.usename AS information_schema.sql_identifier) AS grantor,
       CAST(grantee.name 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(grantee.usesysid,
                                           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,
     ((SELECT pg_user.usesysid,
              0,
              pg_user.usename
       FROM pg_user

       UNION ALL

       SELECT 0,
              pg_group.grosysid,
              pg_group.groname
       FROM pg_group)

      UNION ALL

      SELECT 0,
             0,
             CAST('PUBLIC' AS name)) AS grantee (usesysid, grosysid, name),
     (((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(grantee.usesysid,
                              grantee.grosysid,
                              u_grantor.usesysid,
                              CAST(pr.type AS text),
                              FALSE))
  AND (u_grantor.usename = CAST("current_user"() AS name)
    OR grantee.name = CAST("current_user"() AS name)
    OR grantee.name = CAST('PUBLIC' AS name))


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