Problem:
A user called JIM has left your organisation, you have revoked their roles to the database, 4 days later during a restart of your application, some people start reporting ‘ORA-01950: no privileges on tablespace ‘ORA-01950: no privileges on tablespace X’, whats happened.
You have checked the owners being used in the application, and tried to insert into the table a row using the user.
|
1 2 3 4 |
INSERT INTO SIEBEL.S_EVT_ACT ( * ERROR at line 1: ORA-01950: no privileges on tablespace 'SIEBEL_INDEX25AS' |
They all have the roles that have permissions to the table space. They have unlimited quotas
Lets look deeper to the schema
|
1 2 3 4 |
SQL> select owner,table_name,tablespace_name,cluster_name,iot_name from dba_tables where table_name='S_EVT_ACT'; OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAME IOT_NAME ------------------------------ ------------------------------ SIEBEL S_EVT_ACT SIEBEL_500 |
|
1 2 3 4 5 6 7 |
SQL> select owner,table_owner,table_name,index_name,index_type,tablespace_name from dba_indexes where table_name='S_EVT_ACT'; OWNER TABLE_OWNER TABLE_NAME INDEX_NAME INDEX_TYPE TABLESPACE_NAME --------------------------------------------------------------- SIEBEL SIEBEL S_EVT_ACT S_EVT_ACT_F11 NORMAL SIEBEL_INDEX25AS JIM SIEBEL S_EVT_ACT S_EVT_ACT_M6 FUNCTION-BASED NORMAL SIEBEL_INDEX25AS JIM SIEBEL S_EVT_ACT S_EVT_ACT_M8 FUNCTION-BASED NORMAL SIEBEL_INDEX25AS SIEBEL SIEBEL S_EVT_ACT S_EVT_ACT_F14 NORMAL SIEBEL_INDEX25AS |
We revoked JIM’s access when he left but he still has objects in the application schema.
For sure if new extents are going to be allocated for this object, it will fail with the no privilege error. It may still work for some users because the already allocated extent space for JIM can be used without any issues.