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..?
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.
TO allow a role to read from the information_schema and get results about a table 3 things have to happen:
grant usage on the database to the role
grant usage on the schema the table is in to the role.
grant some access to the table to the role. Normally, this could be "SELECT" but that would give read access to all production tables. However, granting REFERENCES to the table will also work, I think. the main purpose of REFERENCES is to allow a role to set up a reference - a FK relationship to another table ( think referencing INVOICE_NUMBER in INVOICE_HEADER from INVOICE_DETAILS to be sure the parent table has the data) The REFERENCE does not allow the role to CRUD any of the data other than "background" reference. REFERENCE seems better for PRODUCTION data than SELECT.
Any thoughts or caveats welcome
