Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Create User Problem

Re: Oracle Create User Problem

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Wed, 27 Aug 2003 09:57:36 -0700
Message-ID: <3F4CE2FF.90CF4999@exxesolutions.com>


Rajesh wrote:

> Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3F4B90E8.6939064E_at_exxesolutions.com>...
> > Rajesh wrote:
> >
> > > I have a script to create all the tablepaces from tb1 to tb4.
> > >
> > > And when i run the below create user script, I get the error in the
> > > sql option.
> > > Are the below line is synataxically wrong, anybody please suggest?
> > >
> > > CREATE USER <username> IDENTIFIED BY <password> DEFAULT TABLESPACE
> > > tb1, tb2, tb3, tb4 PROFILE DEFAULT;
> > >
> > > Whether it hold good for both Oracle 8i and 9i?
> > >
> > > Thanks in advance.
> >
> > CREATE USER <user_name>
> > IDENTIFIED BY <password>
> > DEFAULT TABLESPACE <pick_just_one>
> > TEMPORARY TABLESPACE <temp>
> > QUOTA 0 ON SYSTEM
> > QUOTA ? ON <tablespace_name>
> > QUOTA ? ON <tablespace_name>
> > QUOTA ? ON <tablespace_name>
> > QUOTA ? ON <tablespace_name>
> > PROFILE DEFAULT;
> Thanks! Morgan.
> I am able to create user with your suggestion.
> How to see, who is the owner of the tablespace? if, i don't specify
> "Quota ? on tb1", User won't be able to access tb1.
>
> Please correct me?

My pleasure.

A schema must have a quota on a tablespace to access it unless granted the UNLIMITED TABLESPACE system privilege and that is one privilege I don't believe should ever be granted for any reason.

To determine who has what quota on what tablespace look at the data dictionary views:

user_ts_quota
all_ts_quotas
dba_ts_quotas

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
http://www.outreach.washington.edu/extinfo/certprog/aoa/aoa_main.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Wed Aug 27 2003 - 11:57:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US