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

Home -> Community -> Usenet -> c.d.o.misc -> Re: How give full permission on a schema to a user

Re: How give full permission on a schema to a user

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 17 Feb 2007 13:14:11 -0800
Message-ID: <1171746851.357184.105890@s48g2000cws.googlegroups.com>


On Feb 16, 8:39 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Sam wrote:
> > Hi There,
> > I created 3 different table spaces as default schema for 3 Oracle user,
> > What's the easiest way to give them full permission
> > on their schema to create, update,delete ... all kind of objects and
> > add,delete,... records to their objects
> > but minimum possible permissions on the system and other schemas,
>
> > Thank you in advance - Sam
>
> Can't imagine why you would create different tablespaces for different
> users as Oracle != SQL Server.
>
> But when you create users you assign the quota:
>
> CREATE USER ...
> IDENTIFIED BY ...
> DEFAULT TABLESPACE ...
> TEMPORARY TABLESPACE ...
> QUOTA ... ON ...;
>
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

You will also want to grant the users object creation permissions like

create session <== so they can connect

create table        <== to create tables
create view         <== if you want them to be able to define views
create procedure <== if the user is to be allowed to create stored procedures including functions and packages ...
See the SQL manul for a complete list of available object privileges

Avoid the use of the obsolete role connect and resource. These exist for backward compatiability.

HTH -- Mark D Powell -- Received on Sat Feb 17 2007 - 15:14:11 CST

Original text of this message

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