Q
Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

New user can't create table in SYSTEM tablespace

I have created a user to which I gave create session and create table privileges. When I logged in with the newly created user and tried to create a table, the error comes as 'Insufficient privilege for SYSTEM tablespace'.

So how do I grant a SYSTEM tablespace to this newly created user. Are there any other privileges required to create a table with the new user?

It sounds like when you created the user you didn't specify the user's default tablespace nor did you specify a quota. Here's how you need to create a user properly:

SQL> CREATE USER new_user IDENTIFIED BY password
  2  DEFAULT TABLESPACE users
  3  TEMPORARY TABLESPACE temp
  4  QUOTA 10M ON users ;

User created.
This will create a new user, assign them a password, set their default and temporary tablespaces and give them a quota for space they may use in their default tablespace for objects they create. Without these pieces of information, the default and temporary tablespaces are set to SYSTEM (very bad idea!!!).

The error you were getting indicates that the user is assigned with a default tablespace of SYSTEM and you didn't give them quota to be able to create objects. So, even though you granted create table privilege, you didn't give them the ability to use any space. This is actually a good thing in this case because you NEVER want users having the SYSTEM tablespace as their default.

Recreate the user using these options and you should be all set.

For More Information


Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close