Re: Creating tables - Newbie question

In article <954786937.17697.0.pluto.d4ee154e_at_news.demon.nl>, "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> Answers embedded
> <gnawed_at_my-deja.com> wrote in message

 news:8calve$g55$1_at_nnrp1.deja.com...
> > 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>

Thanks. This is very informative, especially the example. However, when I create a new table, how do I create it in/assign it to the new tablespace? Also, if I create a table (as user SYSTEM), the table becomes part of the system schema. How do I create it as part of a new independent schema (or is that not usually done)?

> >
> > 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.

OK, this works. Do I have to grant insert, select ... on each table independently, or is there a way to grant the same permissions to all tables in a schema?

> 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).

I'm not sure I understand the details of your answer. Do you have any examples? I want to create a tablespace belonging to a new user (say 'owner') and create a slew of table in that tablespace, all in a new schema. And I want to grant limited access to these tables (select, insert, update) by a webuser (say 'webuser').

>
> Hth,
>
> Sybrand Bakker, Oracle DBA

Thanks so much. I know these questions are pretty basic. I'm still trying to grapple with how databases, tablespaces and users affect access & setup of each other.

• Ed Wang

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Apr 03 2000 - 00:00:00 CDT