Skip to content

How to Resolve ORA-01952: system privileges not granted to

  • by

ORA-01952

Tried to take a privilege away from a user, but it failed with ORA-01952.

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

ORA-01952 means that the system privilege you want to remove from a user has never been granted to it. That's why the statement failed. You can ignore it if you have known the whole truth.

Most likely, you may misunderstand the situation. For example, a user who already has some tables, and we naturally assume that it has CREATE TABLE privilege. When we tried to revoke the privilege from the user, we got ORA-01952.

The truth is, those tables were created by other user who has CREATE ANY TABLE privilege. In other words, the user does own some tables, but they were created by someone else.

Another misunderstanding is that, you thought that you have granted the privileges properly, but actually no.

Unlimited Tablespace

Suppose you found an user who is able to use a tablespace without limitation, so you want to revoke UNLIMITED TABLESAPCE from it.

SQL> revoke unlimited tablespace from hr;
revoke unlimited tablespace from hr
*
ERROR at line 1:
ORA-01952: system privileges not granted to 'HR'

As you can see, the user has never been granted UNLIMITED TABLESAPCE, how can it use a tablespace without any limitation?

This is because it has some quota, maybe unlimited quota on that tablespace. Let's check its quota.

SQL> column tablespace_name format a20;
SQL> column "QUOTA(GB)" format a10;
SQL> select tablespace_name, case max_bytes when -1 then 'UNLIMITED' else to_char(max_bytes/1024/1024/1024) end "QUOTA(GB)" from dba_ts_quotas where username = 'HR';

TABLESPACE_NAME      QUOTA(GB)
-------------------- ----------
EXAMPLE              UNLIMITED
USERS                100

OK, it does have unlimited quota on a tablespace. To prevent this, you can revoke unlimited tablespace from the user.

Leave a Reply

Your email address will not be published.