I’m new to Oracle 9i using R2. I’m currently logged in as sysdba and currently tried creating a new user:

SQL> create user john identified by oracle
2 default tablespace users;

User created.

SQL> grant connect to john;

Grant succeeded.

SQL> grant create any table to john;

Grant succeeded.

SQL> grant create table to john;

Grant succeeded.

SQL> grant unlimited tablespace to john;

Grant succeeded.
 

Why is it that if I don’t grant unlimited tablespace, john will not be able to create table? It gives an error :

ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

But I have stated him to use tablespace users when created the user name at first?

---------->

You have to assign a quota.

Assign Tablespace Quotas to Users

Grant to users who will be creating tables, clusters, materialized views, indexes, and other objects the privilege to create the object and a quota (space allowance or limit) in the tablespace intended to hold the object's segment. The security administrator is responsible for granting the required privileges to create objects to database users and for assigning tablespace quotas, as necessary, to database users.

---------->

just drop the user......john

and try this command....

create user john identified by oracle
default tablespace users
quota 5m on users

after firing up this command give required privileges to john to create a table....
then try whether it is working perfectly without granting that unlimited tablespace....

---------->

You don't have to delete the user. Just alter it.

alter user john quota unlimited on users;

---------->

When you creates the User and Grants him the Connect option by default the User gets allocated with the Unlimited Tablespace only you need to do is eigther Revoke that Option. or as bobanjayan said u just alter the user and add the Quota to that user.

---------->

Connect role does't have the privilege of unlimited tablespace.
Unlimited tablespace is with Resource role.

If you grant Resource role to user, he will get unlimited quota on SYSTEM tablespace also.
Be aware.

Quick Links:
Do you have an Oracle Question?

Best regards,
Oracle Database, SQL, Application, Programming Tips