Home » RDBMS Server » Security » Grant TableSpace.?
Grant TableSpace.? [message #10583] Sun, 01 February 2004 21:35 Go to next message
Rishi
Messages: 63
Registered: January 2001
Member
Hi..
I have created a new user in Oracle 9i and also granted him the permission to create session and to create table. But now when I log in through the newly created user I cannot create a table through it.

Some Table Space permission is required.

Gone through Oracle Documentation as specified but cant find anything fruitful as far as Granting of Table Space is concerned.

So I want the syntex and a bit details of the Table Space keyword.

Regards
Rishi
Re: Grant TableSpace.? [message #10584 is a reply to message #10583] Sun, 01 February 2004 22:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Either of the following will do:

GRANT UNLIMITED TABLESPACE TO new_user;

or

GRANT RESOURCE TO new_user;

The resource role includes tablespace permission.

A user needs to have permission to a tablespace in order to create anything that needs to be stored in a tablespace.
Re: Grant TableSpace.? [message #10588 is a reply to message #10584] Mon, 02 February 2004 04:48 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Granting UNLIMITED_TABLESPACE or RESOURCE to a user enables him to create tables of ANY size on ANY tablespace( including SYSTEM) and hence is undesirable. Allocating quota on specific tablespaces is the more appropriate way to control quota allocation.

SQL> create user newuser identified by newuser default tablespace users temporary tablespace temp;

User created.

SQL> grant connect to newuser;
SQL> 
Grant succeeded.

SQL> create table t(x int);
create table t(x int)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

SQL> connect /
Connected.
SQL> alter user newuser quota unlimited on users;

User altered.

SQL> connect newuser/newuser
Connected.
SQL> create table t(x int);

Table created.

SQL> create table t1(x int) tablespace tools;
create table t1(x int) tablespace tools
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'TOOLS'

Re: Grant TableSpace.? [message #10589 is a reply to message #10588] Mon, 02 February 2004 05:36 Go to previous messageGo to next message
Rishi
Messages: 63
Registered: January 2001
Member
Thanks a Lot ... Both Barbra and Thiru...
Re: Grant TableSpace.? [message #11466 is a reply to message #10589] Sun, 28 March 2004 18:12 Go to previous messageGo to next message
navdeep singh
Messages: 1
Registered: March 2004
Junior Member
hi,
i am very thankful to you for providing me so
useful information so that i could carry on with my
work.i was reading a book and in that book we have to create that can access system tablespac.

thank you
(navdeep singh)
Re: Grant TableSpace.? [message #280341 is a reply to message #10583] Tue, 13 November 2007 04:57 Go to previous message
hb_venkatesh
Messages: 9
Registered: October 2007
Location: india
Junior Member

Hi,
I'm so thank full to this thread, i face this problem to take Half woking day

regards
venki
Previous Topic: I loos the pasword of sys
Next Topic: encryption key management
Goto Forum:
  


Current Time: Fri Mar 29 07:03:46 CDT 2024