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;
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:
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
Unlimited tablespace is with Resource role.
If you grant Resource role to user, he will get
unlimited quota on SYSTEM tablespace also.
Do you have an Oracle Question?
Oracle Database, SQL, Application, Programming Tips
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity. Information used on this site is at your own risk.
The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.