CODEWITHSUNDEEP
sqloracle-databaseplsql
Phillip
Phillip

Reputation: 73

Oracle: How can you clean up every grant a user has?

I'm looking for a quick easy way to revoke every privilege a user has to tables, views, etc. Is there any simple magic that can do this?

The purpose of doing this is to start fresh on what access should have.

Upvotes: 1

Views: 3024

Answers (2)

APC
APC

Reputation: 146239

I kept thinking there was some sort of REVOKE ALL command

Alas, no. Privileges are revoked (and granted) atomically, which is the way it should be. Wanting to revoke all privileges from a user is a product of the same mindset which lead to granting too many and/or too powerful privileges in the first place.

There are three classes of granted privilege:

  • role
  • system (CREATE TABLE, CREATE SESSION, etc)
  • object access (tables, views, procedures etc in other schemas)

Each has a different set of views over the data dictionary.

  • USER_ROLE_PRIVS ( also ALL_, DBA_ )
  • USER_SYS_PRIVS ( also ALL_, DBA_ )
  • USER_TABLE_PRIVS ( also ALL_, DBA_ )

We can use these views to generate REVOKE statements. It seems peculiar to do this as the user in question. So, a a power user (i.e. a DBA) execute something like this:

begin

    dbms_output.put_line('Revoking granted roles ...');

    for r in ( select * from dba_role_privs
               where grantee = 'JOESOAP' )
    loop
        dbms_output.put_line('revoke ' || r.granted_role ||' from ' || r.grantee ||';');
    end loop;

    dbms_output.put_line('Revoking granted system privileges ...');

    for r in ( select * from dba_sys_privs
               where grantee = 'JOESOAP' )
    loop
        dbms_output.put_line('revoke ' || r.privilege ||' from ' || r.grantee ||';');
    end loop;

    dbms_output.put_line('granted access privileges ...');

    for r in ( select * from dba_tab_privs
               where grantee = 'JOESOAP' )
    loop
        dbms_output.put_line('revoke ' || r.privilege || ' on ' || r.owner||'.'||r.table_name ||' from ' || r.grantee ||';');
    end loop;

end;
/

This will output commands to the screen - use an IDE like SQL Developer to make this less tricky - which you can review and save as an executable script. I suggest you do this rather than have the loops EXECUTE IMMEDIATE simply because you need to have a record of what privileges you've zapped, and also to stop you accidentally de-authorising something or somebody which might come back to bite you later.

In fact, rather than revoking all privileges and re-granting some of them it would be better to see all the privileges the user has and just revoke the ones which shouldn't have been granted.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142713

When you find out which privileges user has, e.g.

SQL> select * From user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
SCOTT                          CREATE DATABASE LINK                     NO
SCOTT                          CREATE ROLE                              NO
SCOTT                          CREATE VIEW                              NO
SCOTT                          CREATE TYPE                              NO
SCOTT                          CREATE PROCEDURE                         NO
SCOTT                          UNLIMITED TABLESPACE                     NO
SCOTT                          CREATE PUBLIC SYNONYM                    NO
SCOTT                          CREATE TABLE                             NO
SCOTT                          CREATE TRIGGER                           NO
SCOTT                          CREATE SEQUENCE                          NO
SCOTT                          CREATE SESSION                           NO

11 rows selected.

SQL>

then write query which will write some code for you:

SQL> select 'revoke ' || privilege || ' from scott;'
  2  from user_sys_privs;

'REVOKE'||PRIVILEGE||'FROMSCOTT;'
--------------------------------------------------------
revoke CREATE DATABASE LINK from scott;
revoke CREATE VIEW from scott;
revoke CREATE ROLE from scott;
revoke UNLIMITED TABLESPACE from scott;
revoke CREATE PROCEDURE from scott;
revoke CREATE TYPE from scott;
revoke CREATE PUBLIC SYNONYM from scott;
revoke CREATE TABLE from scott;
revoke CREATE TRIGGER from scott;
revoke CREATE SESSION from scott;
revoke CREATE SEQUENCE from scott;

11 rows selected.

SQL>

Now copy/paste those revoke statements and run them.

However, that's not all. User can have additional privileges, so - as a privileged user - query DBA_SYS_PRIVS, DBA_ROLE_PRIVS, DBA_TAB_PRIVS.

In order not to think too much :), have a look at how Pete Finnigan did that. Script dates from 2003, but - it'll give you idea how to do it.

Also, probably the simplest way to do it would be to drop that user (but that's, I suppose, not an option).

Upvotes: 1

Related Questions