Question:

I'm trying to run an SQL script to create tables for a new database.
This is only for development, not production.

1) Is it common to create a new tablespace for these tables?  I noticed
that the demo tables are all in the SYSTEM tablespace.  Is this
standard?  And how do I create a new tablespace with the proper
permissions (see next question).

2) I want to connect to the database from a client app.  I want to have
one user "own" the database (may be system or somesuch), and another
user that can only query, update, insert, but not delete (basically
restricted for web access).  I can't seem to create the tables with the
proper ownership, nor proper access.  Can someone outline the steps
necessary?

Answers embedded
<[email protected]> wrote in message news:[email protected]...
> I'm trying to run an SQL script to create tables for a new database.
> This is only for development, not production.
>
> 1) Is it common to create a new tablespace for these tables?
Yes

I noticed
> that the demo tables are all in the SYSTEM tablespace.

This is some 100k only

Is this
> standard?  And how do I create a new tablespace with the proper
> permissions (see next question).

create tablespace <tablespace_name>
default storage ( etc.
datafile '<filename on server>' size <whatever> M
alter user <youruser> quota unlimited on <tablespace name>
>
> 2) I want to connect to the database from a client app.  I want to have
> one user "own" the database (may be system or somesuch), and another
> user that can only query, update, insert, but not delete (basically
> restricted for web access).  I can't seem to create the tables with the
> proper ownership, nor proper access.  Can someone outline the steps
> necessary?
>
> - Ed Wang
>
>

The only thing you need to do is to grant select, insert, update, delete on
tables to the other user, select on views, execute on pl/sql and select on
sequences. Furthermore all those objects need to have a public or private
synonym, or you are forced to hardcode the owner in your app everywhere.
Proper ownership is either an issue of connecting as the proper user or do
this on behalf by a DBA account : create <owner>.<table>.
The user becoming the owner needs as a minimum create session and create
table privilege, usually they get the connect and the resource role (which
are obsolete since the early days of 7 but everyone uses them).

Hth,

Sybrand Bakker, Oracle DBA

> Sent via Deja.com http://www.deja.com/
> Before you buy.