Revoking Oracle Permissions to multiple users at once

Question

We're rationalising our database user permissions and to that end we'd like to revoke all select permissions across all tables in a schema granted to all users (but not a specific role).

With some regular expressions I've tried creating a universal revoke for each table giving something like:

revoke select on TABLE1 from USER1,USER2,USER3...;

revoke select on TABLE2 from USER1,USER2,USER3...;

However as not all users were granted permissions to all tables, this results in the oracle error:

01927. 00000 - "cannot REVOKE privileges you did not grant"

Which makes sense but isn't helpful.

---

I also tried using All_Tab_Privs to get a list of all tables with the requisite grants to be revoked, and this works, but I can't see how to easily get from this to actually revoking the permissions.

SELECT * From All_Tab_Privs where grantor = 'GRANTING_USER' and grantee != 'READROLE' and grantee != 'PUBLIC' and grantee != 'SYS';

Any suggestions for how to do this without spending hours in a spreadsheet? I'm guessing some PL/SQL? Thanks.

Answer 1

A general pattern for this kind of job is to use an implicit cursor to return the data you want and then to build the DDL and execute them.

begin
  for x in (select attribute_1, attribute_2 ... from ... where ...)
  loop
    execute immediate 'REVOKE ...'||x.attribute1||' etc';
  end loop;
end;
/

A nice alternative is to build the SQL you want to execute in the SELECT and then just execute that. It's easier to test but the SQL then looks a bit clunky.