Databricks SQL: Audit Effective Grants with SHOW GRANTS

This tutorial demonstrates how to use the `SHOW GRANTS` command in Databricks SQL to audit effective grants on a table. We’ll progress through three scripts, each focusing on a slightly more complex scenario.

Script 1: Basic Grant Audit

This script demonstrates the fundamental usage of `SHOW GRANTS` on a single table.

USE CATALOG EXAMPLE;
SHOW GRANTS ON TABLE my_table;
-- No further data or validation is required for this introductory example.
-- The SHOW GRANTS command will return the grants associated with the current user.
-- For demonstration purposes, let's create the table and a simple row.
CREATE TABLE my_table (
    id INT
);
INSERT INTO my_table (id) VALUES (1);

SELECT COUNT() FROM my_table;


1


Script 2: Grant Audit with Multiple Grants

This script shows how to verify that a user has multiple grants on a single table. We’ll create a user with differing access levels.

USE CATALOG EXAMPLE;
-- Create a new user
CREATE USER user1 WITH PASSWORD 'password123';
-- Grant SELECT privileges to user1
GRANT SELECT ON TABLE my_table TO user1;
-- Grant ALL privileges to user2
GRANT ALL ON TABLE my_table TO user2;
SHOW GRANTS ON TABLE my_table;
-- Validate user1 has only SELECT privileges
SELECT COUNT() FROM TABLE(FLATTEN(SELECT  FROM TABLE(MAGIC_QUERIES('SELECT COUNT() FROM my_table WHERE USER = ''user1'') AS t)));
-- Validate user2 has ALL privileges
SELECT COUNT() FROM TABLE(FLATTEN(SELECT  FROM TABLE(MAGIC_QUERIES('SELECT COUNT() FROM my_table WHERE USER = ''user2'') AS t)));
-- Cleanup the user (important practice)
REVOKE ALL ON TABLE my_table FROM user2;
DROP USER user2;

SELECT COUNT() FROM my_table;


1


Script 3: Grant Audit on a Table with Permissions Managed via Databricks SQL

This script demonstrates auditing grants in a scenario where permissions might be managed through Databricks SQL features (though this is simplified for this example). It shows how `SHOW GRANTS` displays the currently effective permissions.

USE CATALOG EXAMPLE;
-- Create a new user
CREATE USER user3 WITH PASSWORD 'password456';
-- Grant SELECT privileges to user3
GRANT SELECT ON TABLE my_table TO user3;
SHOW GRANTS ON TABLE my_table;
-- Validate user3 has SELECT privileges
SELECT COUNT() FROM TABLE(FLATTEN(SELECT  FROM TABLE(MAGIC_QUERIES('SELECT COUNT() FROM my_table WHERE USER = ''user3'') AS t)));
-- Cleanup the user (important practice)
REVOKE SELECT ON TABLE my_table FROM user3;
DROP USER user3;

SELECT COUNT() FROM my_table;


1


This tutorial covered the basics of auditing effective grants on a table in Databricks SQL using the `SHOW GRANTS` command. Remember to always clean up resources (like users) after you’re finished testing.