Is there anyway to execute grant and revoke statements to a user for an object based on a condition?

data_explorer — Tue, 31 Jan 2023 09:48:01 GMT

SELECT if((select count(*) from information_schema.table_privileges where grantee = 'samo@test.com' and table_schema='demo_schema' and table_catalog='demo_catalog')==1, (select count(*) from demo_catalog.demo_schema.demo_table), (select count(*) from demo_catalog.demo_schema.demo_table2));

The above query is working and I'm getting the result.

But what I'm trying to achieve is that,

select count(*) from information_schema.table_privileges where grantee = 'samo@test.com' and table_schema='demo_schema' and table_catalog='demo_catalog')==1

If the above condition is true , then I want to run the below subqueries altogether:

revoke select on table demo_catalog.demo_schema.demo_table from `samo@test.com`;revoke usage on catalog demo_catalog from `samo@test.com`;revoke usage on schema demo_catalog.demo_schema from `samo@test.com`;

Basically, for a user, if there is access to only one table, then I want to revoke the access from table, schema and catalog.

Is it possible to achieve this, if yes, How to achieve it?

Re: Is there anyway to execute grant and revoke statements to a user for an object based on a condition?

Debayan — Sun, 05 Feb 2023 13:12:53 GMT

Hi, GRANT and REVOKE are privileges on an securable object to a principal. And a principal is a user, service principal, or group known to the metastore. Principals can be granted privileges and may own securable objects.

Also, you can use REVOKE ON SHARE to revoke access on shares from recipients and you can use GRANT ON SHARE to grant recipients access to shares.

Please refer: https://docs.databricks.com/sql/language-manual/security-grant.html

https://docs.databricks.com/sql/language-manual/security-revoke.html

topic Re: Is there anyway to execute grant and revoke statements to a user for an object based on a condition? in Data Engineering

Is there anyway to execute grant and revoke statements to a user for an object based on a condition?

Re: Is there anyway to execute grant and revoke statements to a user for an object based on a condition?