Revoking Privileges on all objects

SK21

Is it possible to revoke all privileges of users on every object whether table, object or view in one single command. Something like revoke all for a particular user.

marcothesane

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...

mosheg

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';