How can I access objects under INFORMATION_SCHEMA in a DB in Snowflake?
If I try to grant USAGE access to a PUBLIC or any role on INFORMATION_SCHEMA I get this error: SQL access control error: Insufficient privileges to operate on schema 'INFORMATION_SCHEMA'
I am trying to access objects under INFORMATION_SCHEMA from Power BI. Would be possible to do this..?
Best Answer
There are a lot of views and table functions in a databases's INFORMATION_SCHEMA. Some require elevated permissions, but if you just want basic access for a role all it needs is usage on the database:
use role ACCOUNTADMIN;create or replace role NEWROLE;grant usage on database TEST to NEWROLE;grant role NEWROLE to user MYUSER;use role NEWROLE;select * from "TEST"."INFORMATION_SCHEMA"."COLUMNS";INFORMATION_SCHEMA is at the DB level, I believe so long as you have usage access at the DB level, you should be able to access INFORMATION_SCHEMA underneath it, as Greg's example has suggested.
But the result from INFORMATION_SCHEMA can be dependent on the user's access. For example, if the user does not have access to certain tables, then they won't appear in the results.