Redshift Observatory

System Table Tracker

System view information_schema.routine_privileges version 1.0.52931 / 2023-07-07

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