Re: Newbie: problem with user access to tablespace

Allan Tingey wrote on 08-Apr-99 18:19:36:
>Hi,

>I have a lot of experience with computers but I am new to oracle...

>I have recently set up an 8.0.3 server on a Dec(Compaq) Alpha
>server. Created a database with tablespace, etc...

>I am confused about schemas. I would like to create tables
>that can be updated by certain users and readable by others.

>Currently, I can create tables in the proper tablespace when
>logged in as system but not when logged in as another user.
>Another user can access a table created by system if I qualify
>the query with system.tablename but I suspect that this not
>the way things or usually done.

>If you can tell me how to do following or direct me to the correct
>documentation I would really appreciate it...

>1. Under what schema should your main tables be created and what
> needs to be done so that database users can access those tables?

We have one schema that owns most of our tables. Access to those tables is granted to a special role called 'db_user'. Also we have a public synonym for these tables. This way every user who has granted the role 'db_user' can access the tables. He does not even know the schema the tables are in.

>2. Currently, only my system account can create tables in the
> tablespace I want to use. Other users get a "no privileges on
> tablespace" message when trying to create a table, even though
> this is their default tablespace. What do I need to do to these
> accounts so they can work with tables in their default tablespace?

Here, you will have to grant your users a quota on that tablespace. Something like 'alter user <username> quota unlimited on <tablespace>' should work. You might have to adjust the syntax slightly because I'm writing this at home with neither Oracle nor docs available.

>Thanks for your help.

Hope that helps,
Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |