I was using node.js to work on snowflake datawarehouse as a destination for users. I wanted to check if a user has the required permission level on the schema to create a table and write into it before adding the user to the database otherwise it should give an error saying that the user does not have the appropriate permission level. How can I achieve that programatically?

Thanks,

CodePudding user response：

one way you could do is check if the role has SEELCT privilege on the table by looking into the view TABLE_PRIVILEGES in information_schema schema.

select * from information_schema.TABLE_PRIVILEGES where table_name = 'SALES_RAW'

CodePudding user response：

Due to how permissions can be inherited through the role hierarchy, this isn't easy to do. Permissions aren't assigned to users in Snowflake, they are assigned to roles. You could use the table_privileges in the information schema (as Himanshu said). You'll need to ask your admin for privileges to the information_schema schema in the databsae:

You could probably use some combination of these too:

show grants to user [username] with show grants on schema [schema name]

The easiest way would be to have your app / script / service assume the same role as the user and see if you can select from a table in the schema or try to create a temporary table in the schema. If you receive an error code, the user doesn't have permissions!