When user permissions are explicitly granted for individual tables and views, the selected user can access those tables and views even if they don’t have permission to access the underlying database.

If you want to revoke a user’s access, you can do so with the REVOKE command. However, the REVOKE command is explicit, and is strictly scoped to the object specified in the command.

For example:

%sql

REVOKE ALL PRIVILEGES ON DATABASE <database-name> FROM `<user>@<domain-name>`
REVOKE SELECT ON <table-name> FROM `<user>@<domain-name>`

If you want to revoke all privileges for a single user you can do it with a series of multiple commands, or you can use a regular expression and a series of for loops to automate the process.

Example code

This example code matches the <search-string> pattern to the database name and the table name and then revokes the user’s privileges. The search is recursive.

%python

from re import search
databaseQuery = sqlContext.sql("show databases")
databaseList = databaseQuery.collect()
# This loop revokes at the database level.
for db in databaseList:
  listTables = sqlContext.sql("show tables from "+db['databaseName'])
  tableRows = listTables.collect()
  if search(<search-string>, db['databaseName']):
    revokeDatabase=sqlContext.sql("REVOKE ALL PRIVILAGES ON DATABASE "+db['databaseName']+" to `<username>`")
    display(revokeDatabase)
    print("Ran the REVOKE query on "+db['databaseName']+" for <username>")
  # This loop revokes at the table level.
  for table in tableRows:
    if search(<search-string>,table['tableName']):
      revokeCommand=sqlContext.sql("REVOKE SELECT ON "+table['database']+"."+table['tableName']+" FROM `<username>`")
      display(revokeCommand)
      print("Revoked the SELECT permissions on "+table['database']+"."+table['tableName']+" for <username>")