CODEWITHSUNDEEP
snowflake-cloud-data-platform
ShilpaK
ShilpaK

Reputation: 23

Snowflake - Privileges required to query snowflake Information schema views

I am new to Snowflake. Is it possible to query Information schema views for eg, SCHEMATA, TABLES, COLUMNS for all tables in a snowflake Db without having select access to the underlying tables. The requirement is to create a user/account that only has access to query metadata of the snowflake Db and should not have a select access to the table data. Please advise.

Upvotes: 2

Views: 6073

Answers (3)

Ankush Jadhao
Ankush Jadhao

Reputation: 1

The views in INFORMATION_SCHEMA display metadata about objects defined in the database, as well as metadata for non-database, account-level objects that are common across all databases.

There are 17 views available under INFORMATION_SCHEMA that holds information of Database level objects. There are 8 views that holds information of Account level objects.

INFORMATION_SCHEMA is a read-only schema available automatically under each database. It stores metadata of all Snowflake objects built under the database.

Running Queries on INFORMATION_SCHEMA requires warehouse to be up and running which incurs Snowflake credits.

The output of a view or table function depend on the privileges granted to the user’s current role. When querying an INFORMATION_SCHEMA view or table function, only objects for which the current role has been granted access privileges are returned.

To use a database's information schema all you need is usage privilege on that database. The role in turn will only see content from the information schema that he/she has access to.

Upvotes: 0

Sriga
Sriga

Reputation: 1321

I believe you won't see the object in information schema views. So you required privileges to access the object.

Upvotes: 0

Suzy Lockwood
Suzy Lockwood

Reputation: 1170

From the Snowflake documentation:

"The output of a view or table function depends on the privileges granted to the user’s current role. When querying an INFORMATION_SCHEMA view or table function, only objects for which the current role has been granted access privileges are returned."

In other words, you won't see the metadata for objects you do not have access to when you query the INFORMATION_SCHEMA views.

To workaround, you can use a role like ACCOUNTADMIN that has permissions to all tables and populate a new table with results from the desired INFORMATION_SCHEMA views. Then give your new role access to that table. You may be able to even setup a task in Snowflake to regularly update the table.

References:

Upvotes: 3

Related Questions