Snowflake Users and Roles via SQL

To Nha Notes | July 26, 2022, 3:16 p.m.

Below is a example to query list all privileges of each object granted to role PUBLIC.

SHOW GRANTS TO ROLE PUBLIC;
SELECT "granted_on", ARRAY_AGG(DISTINCT "privilege") AS privileges
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE 
"name" LIKE '%<SEARCH_KEY_WORD>%'
GROUP BY
    "granted_on"
ORDER BY
    "granted_on";

To generate SQLs to grant privileges of database, schema and warehouse to role PUBLIC, execute below SQL:

SHOW GRANTS TO ROLE PUBLIC;
SELECT CONCAT('GRANT ', ARRAY_TO_STRING(ARRAY_AGG(DISTINCT "privilege"), ', '), ' ON ', "granted_on", ' ', "name", ' TO ROLE PUBLIC;') AS GRANT_SQL
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE 
"name" LIKE '%<SEARCH_KEY_WORD>%' AND "granted_on" IN ('DATABASE', 'SCHEMA', 'WAREHOUSE')

 -- AND "granted_on" = 'DATABASE'
 -- AND "granted_on" = 'SCHEMA'
 -- AND "granted_on" = 'WAREHOUSE'
 -- AND "granted_on" = 'TABLE'
GROUP BY
    "granted_on", "name"
ORDER BY
    "granted_on", "name";

References

https://community.snowflake.com/s/article/How-to-Capture-Snowflake-Users-Roles-and-Grants-Into-a-Table