Revoke a ton of privileges all at once!

So.... I just found out that some user accounts were created some time ago and that they were given privileges they should not have.... No big deal just revoke these privs and everyone can go home happy.

However, the privileges granted to these users rank close to 2200 times! So in order to revoke I will have to run the following command 2200 times... I will be here all night.

revoke select on SCHEMA1.TABLE32 from USER1;

Well I have to be home in time for dinner and it is already 4:45PM. Here is a tip to get you in the car and on your way home.

1.) Find out how many privileges we are talking about. (as sysdba)

SQL>select count(*) from dba_tab_privs where grantee='USER1';

COUNT(*)
--------
2200

1 rows selected

2.) Now here comes the trick... Create your DML statement on the fly with this little gem.

SQL>select 'revoke select on '||''||owner||'.'||table_name||''||' from USER1;' from DBA_TAB_PRIVS where grantee='USER1';

This will generate 2200 hundred lines of revoke statements for you to script up and run while your packing your briefcase to go home!