Revoking Privileges on all objects

Answers

• Options

  marcothesane - Select Field - Administrator

  March 2021

  Yet another SQL-generating-SQL candidate:
  I have no means to test it, as my databases don't have a lot of roles, users and grantees - but I think I can get the syntax right...

  SELECT
    'REVOKE '
  ||CASE object_type WHEN 'ROLE' THEN '' ELSE 'ALL ON ' END
  ||CASE WHEN object_schema IS NULL THEN '' ELSE object_schema||'.' END
  ||object_name
  ||' FROM '||grantee||';'
  FROM v_catalog.grants WHERE  grantee IN ('scott','tiger','fred','allofthem')
  ;
  

  The report of this will be the script you'll have to run. Should work - at least after a few tweaks...

  0
• Options

  mosheg Vertica Employee Administrator

  March 2021 edited March 2021

  I've tried something similar to Marco idea for user Mike and it works:

  \a
  \t
  \o | vsql
  SELECT
    distinct 'REVOKE ALL ON ALL TABLES IN SCHEMA ' || object_schema || ' FROM ' || grantee || ';'
  FROM v_catalog.grants WHERE grantee='mike' AND object_schema != 'NULL';
  

  You may want to add also the following because tables owners can still access their own tables:

  SELECT DISTINCT 'alter table ' || table_schema || '.' || table_name || ' owner to dbadmin;'
  FROM tables WHERE owner_name = 'mike';
  

  1