Redshift Research Project

System Table Tracker

System Table Version 1.0.41465 / 2022-09-10

schema name column data type
information_schema routine_privileges grantee sql_identifier
information_schema routine_privileges grantor sql_identifier
information_schema routine_privileges is_grantable character_data
information_schema routine_privileges privilege_type character_data
information_schema routine_privileges routine_catalog sql_identifier
information_schema routine_privileges routine_name sql_identifier
information_schema routine_privileges routine_schema sql_identifier
information_schema routine_privileges specific_catalog sql_identifier
information_schema routine_privileges specific_name sql_identifier
information_schema routine_privileges specific_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 specific_catalog,
       CAST(n.nspname AS information_schema.sql_identifier) AS specific_schema,
       CAST((CAST(p.proname AS text) || CAST('_' AS text)) || CAST(CAST(p.oid AS varchar) AS text) AS information_schema.sql_identifier) AS specific_name,
       CAST(current_database() AS information_schema.sql_identifier) AS routine_catalog,
       CAST(n.nspname AS information_schema.sql_identifier) AS routine_schema,
       CAST(p.proname AS information_schema.sql_identifier) AS routine_name,
       CAST(CAST('EXECUTE' AS information_schema.character_data) AS information_schema.character_data) AS privilege_type,
       CAST(CASE
              WHEN aclcontains(p.proacl,
                               makeaclitem(grantee.usesysid,
                                           grantee.grosysid,
                                           u_grantor.usesysid,
                                           CAST('EXECUTE' AS text),
                                           TRUE))
                THEN CAST('YES' AS text)
              ELSE CAST('NO' AS text)
            END AS information_schema.character_data) AS is_grantable
FROM pg_proc AS p,
     pg_namespace AS n,
     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)
WHERE p.pronamespace = n.oid
  AND aclcontains(p.proacl,
                  makeaclitem(grantee.usesysid,
                              grantee.grosysid,
                              u_grantor.usesysid,
                              CAST('EXECUTE' 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