6

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, ...

  • Updated question to answer these questions. – kainaw Sep 9 '13 at 19:18
  • Does the schema owner i have privs on the tablespace USERS, because it needs them rather than the user kainaw needing them - kainaw doesnt own the table, i` does. – Philᵀᴹ Sep 9 '13 at 19:31
  • Yes i has all privileges on i.test. However, i is not a real user. i is being used as a schema for tables that all users will use. So, I'm trying to insert into i.test from a user other than i. – kainaw Sep 9 '13 at 19:34
  • 1
    As I said, user i owns the table, therefore user i needs to be granted a quota on the USERS tablespace. i IS still a user that exists in the database. In Oracle Schema=User (at least until 12c came along). That`s the way it works - the owner of the table needs the grants, not the user that is actually inserting the data (because that makes no sense!). – Philᵀᴹ Sep 9 '13 at 19:36
  • I thought i had all privileges. I rant grant unlimited tablespace to i and suddenly everything works. Thanks. – kainaw Sep 9 '13 at 19:43
8

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.

| improve this answer | |
6

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

| improve this answer | |
1

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

| improve this answer | |
0

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 Ppoint-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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.