Re: Creating tables - Newbie question

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 2000/04/04
Message-ID: <954824011.3843.0.pluto.d4ee154e@news.demon.nl>#1/1

connect system/manager
create tablespace webtspc -- etc...
/
create user webdemo identified by webdemo default tablespace webtbspc temporary tablespace temp -- assuming it exists /
rem old style
grant connect, resource to webdemo
/
rem new style
grant create session, create table to webdemo /
alter user webdemo quota unlimited on webtbspc /
create user webuser identified by webuser temporary tablespace temp /
grant create session to webuser
/
connect webdemo/webdemo
create table foo
(col1 number(1) primary key foo_prim -- might be : constraint foo_prim primary key
, col2
)
tablespace webtspc -- in your case unnecessary it is already the default /
create table bar
(col3 number(1) not null primary key bar_prim  col4 number(1) foreign key references foo )
tablespace webtbspc
/
grant select, insert, update, delete on foo to webuser /
grant select, insert, update, delete on bar to webuser /
connect webuser/webuser
create synonym foo for webdemo.foo
/
create synonym bar for webdemo.bar
/
select * from foo
/
select * from bar
rem or
connect system/manager
create public synonym foo for webdemo.foo /
rem etc.
rem normally you would issue grants to a role (made by create role etc) and grant the role to an end-user.

Hth,

Sybrand Bakker, Oracle DBA

<gnawed_at_my-deja.com> wrote in message news:8cb2e1$ugj$1_at_nnrp1.deja.com...
> 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 Tue Apr 04 2000 - 00:00:00 CDT