|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
SQL> grant connect to john;
SQL> grant create any table to john;
SQL> grant create table to john;
SQL> grant unlimited tablespace to john;
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:
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
after firing up this command give
required privileges to john to create a table....
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.
If you grant Resource role to user,
he will get unlimited quota on SYSTEM tablespace also.
Do you have an Oracle Question?
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.