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.
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...
||CASE object_type WHEN 'ROLE' THEN '' ELSE 'ALL ON ' END
||CASE WHEN object_schema IS NULL THEN '' ELSE object_schema||'.' END
||' 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...
I've tried something similar to Marco idea for user Mike and it works:
\o | vsql
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';