not able to create table.

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Fri, 23 Aug 2002 14:49:02 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA7028C4F10@lnewton.leeds.lfs.co.uk>

Afternoon Darkstar,

when a user is created in an Oracle database is has absolutely no powers to do anything - it can't even login, so, when your dba user (SYSTEM) has created the user it must grant it certain privs :

grant CREATE SESSION,
CREATE TABLE,
CREATE VIEW,
CREATE TRIGGER,
CREATE PROCEDURE,
CREATE SEQUENCE to <username>;

These will do for a start. If you have more than one user to create, and they all want similar privs, best to create a role :

CREATE ROLE ANY_USER; Then grant the above privs to the role, rather than the user (you only have to do this once) :

grant CREATE SESSION,
CREATE TABLE,
CREATE VIEW,
CREATE TRIGGER,
CREATE PROCEDURE,
CREATE SEQUENCE to USER_ROLE;

And then, when you create your list of users, grant each of them the role :

GRANT ANY_USER to <username>;

Next, even though the user has the privs to create things, they have no tablespace quota to allow them to actually create objects that use space. So,

ALTER USER <username> QUOTA some_number_of_bytes_or_k_or_m ON <tablespace_name>;

Then, always always always, remove any quota on SYSTEM tablespace :

ALTER USER <username> QUOTA 0 ON SYSTEM;

Trust me, one day you'll thank me for that !

To grant space on lots of tablespace do each one as above.

Alternatively, do something like this :

CREATE USER fred IDENTIFIED BY some_password_or_other DEFAULT TABLESPACE some_pre_existing_tablespace_name TEMPORARY TABLESPACE some_existing_temporary_tablespace QUOTA 0 ON SYSTEM
QUOTA 200M ON some_pre_existing_tablespace_name QUOTA 100M ON some_other_pre_existing_tablespace_name QUOTA 64K ON yet_another_pre_existing_tablespace_name;

GRANT ANY_USER TO fred;

et voila !

Cheers,
Norman.

PS. Check out the concepts guide - it makes excellent bed-time reading :o)

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com


-------------------------------------

-----Original Message-----
From: darkstar [mailto:chaihock_at_singnet.com.sg] Posted At: Friday, August 23, 2002 2:13 PM Posted To: server
Conversation: not able to create table.
Subject: not able to create table.

I encountered the following wrror when try to create a table ERROR at line 1: ORA-01950: no privileges on tablespace 'APP01'

How can i grant the tablespace authority ? Can I assisgn multiple tablespace
to signle user??

Thanks.. Received on Fri Aug 23 2002 - 08:49:02 CDT