Single query which revoke all privilages assing in a user on oralce 10g

[Former Member]

i have ecc 6.0 and oracle 10g database.

and 1 user "test" which contains the lots of privileges.

i see on dba_sys_privs and dba_role_privs.

Now my question is that i want to revoke all the privileges which is assign to test user;

in dba_sys_privs contains 158 roles.

please give me the single revoke query which revoke all sys privileges.

Regards,

Vivek

[former_member188883]

Hi Vivek,

Use the following command to revoke all the privileges

SQL> REVOKE ALL FROM <user>;

Regards,

Deepak Kori

[Former Member]

SQL> revoke ALL from test;

revoke ALL from test

*

ERROR at line 1:

ORA-01919: role 'ALL' does not exist

SQL> show user;

USER is "SYS"

Regards,

[former_member188883]

Hi Vivek,

Please use the following command

REVOKE ALL PRIVILEGES FROM test;

Regards,

Deepak Kori

[Former Member]

SQL> select * from dba_role_privs where grantee='TEST';

GRANTEE GRANTED_ROLE ADM DEF

-

---

-

---

--- ---

TEST DBA NO YES

TEST CONNECT NO YES

SQL> c/role/sys

1* select * from dba_sys_privs where grantee='TEST'

SQL> /

GRANTEE PRIVILEGE ADM

-

---

-

---

---

TEST SELECT ANY TABLE NO

TEST CREATE SESSION NO

TEST UNLIMITED TABLESPACE NO

SQL> REVOKE ALL PRIVILEGES FROM test;

REVOKE ALL PRIVILEGES FROM test

*

ERROR at line 1:

ORA-01952: system privileges not granted to 'TEST'

to to the needfull.

Regards

[former_member188883]

Hi Vivek,

The ora error implies user TEST does not have system privilges and hence it fails.

Since you have already identified the priviliges with user TEST, you can revoke them individually using the revoke command.

For eg:

SQL > REVOKE CREATE SESSION FROM TEST;

Regards,

Deepak Kori

[stefan_koehler]

Hey guys,

i am scared and horrified ... unbelievable ....

@ Vivek:

SQL> set head off
SQL> set linesize 250
SQL> select 'REVOKE ' || PRIVILEGE || ' FROM ' || GRANTEE || ';' from dba_sys_privs where grantee='TEST';
SQL> select 'REVOKE ' || GRANTED_ROLE || ' FROM ' || GRANTEE || ';' from dba_role_privs where grantee='TEST';

*** Copy and paste the output of these queries into SQL*Plus and execute them ***
*** Object grants are not removed !!! ***

Regards

Stefan

[Former Member]

done

[stefan_koehler]

Hello Vivek,

well this is pretty easy.

If your oracle database is licensed by SAP - just drop the user, because of you violate your contract (sapnote #581312).

If your oracle database is licensed by Oracle directly - please invest 5 minutes of your brain. You already know where the information is located - all you need to do now is to write one SELECT to revoke all the corresponding privileges.

Regards

Stefan

[Former Member]

please give me the sql query.

Regards

[former_member188883]

Hi Vivek,

Refer to my reply in this post. It has the sql command.

Regards,

Deepak Kori