SELECT SCHEMA/USE_SCHEMA WHICH IS MOST SUITABLE FOR ACCESS

[sandeephenkel23]

Hi,

As part of the DataGovernance or Authorization topic we are working on automation of the code for granting the access CATALOG LEVEL,SCHEMA LEVEL and TABLE LEVEL in Unity Catalog
As USE CATALOG Provides access at the Catalog level to user/group(which we are using)

SELECT TABLE - Provides access at the table level now the question is on using the access at the SCHEMA level as found there are multiple options at the SCHEMA LEVEL USE_SCHEMA,SELECT SCHEMA

USE_SCHEMA AND SELECT SCHEMA - Providing access for all the available tables in SCHEMA and if we skip this access user can't access the database/Schema

Which is the best approach to use so that User should have access to only particular table from database/schema and not to all the tables.

[sandeephenkel23]

Yes But If I want to connect Power BI from (Partner Connect which is within databricks) Granting access on SELECT SCHEMA/USE SCHEMA is mandatory without this access schema is not available in Power BI and even not able to see the table even which is granted access with SELECT privilege

[MoJaMa]

USE is foundational.
You need it at the catalog level and for any schema you want to access.

So let's say I need SELECT only on 1 table T1 in Catalog:C1, Schema:S1
(Though there may be 1K tables in that schema)

Then I would need

1. USE CATALOG on C1;
2. USE SCHEMA on S1;
3. SELECT on Table C1.S1.T1;

I will not see the other 999 tables with the USE SCHEMA privilege.

(But if you gave be SELECT on the schema level, I would see and be able to select from those as well).