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.

[Kaniz · Accepted solution]

Hi @sandeephenkel23, Based on the information provided, if you want to grant a user access to only a particular table from a database/schema and not to all the tables, you should use the SELECT privilege on that particular table. Using USE SCHEMA or SELECT SCHEMA will grant the user access to all the tables in the schema, which is not what you want.

[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

[Kaniz]

Hi @sandeephenkel23, Based on the provided information, whether granting access to SELECT SCHEMA/USE SCHEMA is mandatory for connecting Power BI from Partner Connect in Databricks is not explicitly mentioned. Could you please file a support ticket for the same?