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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.