OraFAQ Forum: Security » Grant TableSpace.?

Home » RDBMS Server » Security » Grant TableSpace.?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Grant TableSpace.? [message #10583]

Sun, 01 February 2004 21:35

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

Report message to a moderator

Re: Grant TableSpace.? [message #10584 is a reply to message #10583]

Sun, 01 February 2004 22:00

Barbara Boehmer
Messages: 9106
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.

Report message to a moderator

Re: Grant TableSpace.? [message #10588 is a reply to message #10584]

Mon, 02 February 2004 04:48

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'

Report message to a moderator

Re: Grant TableSpace.? [message #10589 is a reply to message #10588]

Mon, 02 February 2004 05:36

Rishi
Messages: 63
Registered: January 2001

Member

Thanks a Lot ... Both Barbra and Thiru...

Report message to a moderator

Re: Grant TableSpace.? [message #11466 is a reply to message #10589]

Sun, 28 March 2004 18:12

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)

Report message to a moderator

Re: Grant TableSpace.? [message #280341 is a reply to message #10583]

Tue, 13 November 2007 04:57

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

Report message to a moderator

Previous Topic:	I loos the pasword of sys
Next Topic:	encryption key management

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 29 00:14:37 CDT 2026