ORA-01952
Let's see some error patterns of ORA-01952.
1. CREATE TABLE
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.
CREATE ANY TABLE
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.
2. ALL PRIVILEGES
Taking a shortcut by ALL PRIVILEGES to revoke all privileges from an user, but it failed with ORA-01952.
SQL> revoke all privileges from hr;
revoke all privileges from hr
*
ERROR at line 1:
ORA-01952: system privileges not granted to 'HR'
This is because ALL PRIVILEGES includes almost all system privileges, except the following system privileges:
- SELECT ANY DICTIONARY
- ALTER DATABASE LINK
- ALTER PUBLIC DATABASE LINK
- ADMINISTER KEY MANAGEMENT
Which means ALL PRIVILEGES includes specific number of privileges, if any one privilege of which is missing from the user, the statement returns error.
If you insist to clear all system privileges in this way, you can re-grant ALL PRIVILEGES to user, then do it again.
SQL> grant all privileges to hr;
Grant succeeded.
SQL> revoke all privileges from hr;
Revoke succeeded.
3. UNLIMITED TABLESAPCE
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.