CODEWITHSUNDEEP
snowflake-cloud-data-platform
Alexey Gorshkov
Alexey Gorshkov

Reputation: 1

How get list of databases for certain user

As Admin I want to help to user select database, schema, table in web UI. I have account with accountadmin role. Need by the user login get all available databases, schemes, tables. For example like useing 'MASTER.INFORMATION_SCHEMA.DATABASES' view with parameters. The 'MASTER.INFORMATION_SCHEMA.DATABASES' returns databases only for current user. Need to have possibility to define a user.

Upvotes: 0

Views: 504

Answers (1)

Simon D
Simon D

Reputation: 6279

Snowflake uses role-based access so users aren't granted access directly to a database but their role is.

To see which roles have access to which databases you can get this info from the Snowflake database which you should have access to if you have the accountadmin role: 

select 
    roles.privilege,
    roles.name as database_name, 
    roles.grantee_name as role_name
from snowflake.account_usage.grants_to_roles roles
where roles.granted_on = 'DATABASE'
;

Note that I think this table only shows privileges that have been granted directly to a role and doesn't show privileges that are attained through their hierarchical relationships. You could write a more complex query to figure out the privs granted through the hierarchy and down to user by using a recursive query on grants_to_roles along with the grants_to_users tables. I think ¯\_(ツ)_/¯

Upvotes: 1

Related Questions