Redshift Research Project

System Table Tracker

System view information_schema.role_routine_grants version 1.0.38698 / 2022-05-27

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 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(0,
                                           g_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, pg_group AS g_grantee
WHERE p.pronamespace = n.oid
  AND aclcontains(p.proacl,
                  makeaclitem(0,
                              g_grantee.grosysid,
                              u_grantor.usesysid,
                              CAST('EXECUTE' 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