Oracle no privileges on tablespace USERS

Question

I have a brand new Oracle database that is giving the error:

ORA-01950: no privileges on tablespace 'USERS'

I have done:

alter user kainaw quota 100M on 'USERS';
grant unlimited tablespace to kainaw;

Still, a single insert returns that error. Other than disk quota, what else causes the "no privileges on tablespace 'USERS'" error?

UPDATE:

Oracle version is 11.2.0.3.0 (11g). I am logging in from the command prompt on the server. So, I alter user kainaw as sysdba. Then, I logout and login a user kainaw to test:

insert into i.test values (1);

Note: i.test is a table with only a number field. I get the error above. I logout as kainaw, login as sysdba, play with permissions, logout, login, test, error, logout, login, ...

Answer 1

You are granting the privileges to the incorrect user.

The schema owner i owns the table, and is therefore the user that needs to be granted the relevant permissions on the tablespace.

Answer 2

It is because no privilege on tablespace. Grant tablespace quota to the user to fix this:

SQL> alter user <your username> quota unlimited on tablespace_name;

SQL> GRANT UNLIMITED TABLESPACE TO <your username>;

Detailed on ora-01950

Answer 3

This is very common error which comes due to insufficient privileges on tablespace. Just try this.

SQL>select USERNAME, DEFAULT_TABLESPACE from dba_users where USERNAME='XXXXX';
SQL> alter user USER_NAME DEFAULT TABLESPACE TABLESPC_NAME quota unlimited on TABLESPC_NAME;

ORA-01950

Answer 4

As others have said quite rightly, the owner of the table needs to have "permission" to store data in a Tablespace, i.e. a quota within that Tablespace, not the individual causing the data to be stored.

My tuppence-worth:

Do not grant UNLIMITED TABLESPACE to any User.

It allows them to scribble data into any Tablespace, including the database-internal System Tablespaces, if they happen to know what they're called.

Keep the users reined in and only allow them to write into Tablespaces that you've prepared (and sized) for them. If, like me, you associate Schemas and Tablespaces to create separately restorable units through Tablespace Point-in-Time Recovery (TSPITR) - a strategy pre-dating CDB's, obviously - then allowing the Users to write data into any old corner of the database where they can find some space completely undermines that recovery strategy.