Re: Creating tables - Newbie question

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>
>
> 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.
Received on Mon Apr 03 2000 - 00:00:00 CDT