ORA-01952: system privileges not granted to 'ROJIB

Question

why do i get ORA-01952: system privileges not granted to 'ROJIB' ?

create role :

SQL> create role security;

Role created.

and then create grant for role 'security' :

SQL> grant create table,create view to security;

Grant succeeded.

grant for user :

SQL> grant security to rojib;

Grant succeeded.

after in revoke, i get the error :

SQL> revoke create table from rojib;
revoke create table from rojib
*
ERROR at line 1:
ORA-01952: system privileges not granted to 'ROJIB'

You have granted the privilege to the user through a role, while you are trying to revoke the privilege from the user rather than revoking it from the role. — Lalit Kumar B

Lalit Kumar B Lalit Kumar B · Accepted Answer · 2015-09-01T10:46:41

why do i get ORA-01952: system privileges not granted to 'ROJIB' ?

That's because you granted a privilege to the ROLE SECURITY, so you need to REVOKE the privilege from the ROLE SECURITY, not the USER.

For example,

Create a USER:

SQL> CREATE USER TEST IDENTIFIED BY TEST;

User created.

Create a ROLE:

SQL> CREATE ROLE SECURITY;

Role created.

Grant privileges to the ROLE:

SQL> GRANT CREATE TABLE,CREATE VIEW TO SECURITY;

Grant succeeded.

Grant ROLE to the USER:

SQL> GRANT SECURITY TO TEST;

Grant succeeded.

Revoke privilege from the ROLE:

SQL> REVOKE CREATE TABLE FROM SECURITY;

Revoke succeeded.

Revoke ROLE from the USER:

SQL> REVOKE SECURITY FROM TEST;

Revoke succeeded.

ORA-01952: system privileges not granted to 'ROJIB

1 Answers