Re: Different user can't see the otherones tables

Hi Aravind!

I tried it with synonyms and with "grant all on tablename to public" statement.
But all didn't work like I like to.

In my systems I have multiple administrators. They create tables and multiple users
should use them without care which of the administrators created them (so i don't like
to adress the tables wie <adminname>.<tablename>).

Moreover, administrators should be able to drop the tables of other admins (and that
doesn't seem to work with synonyms).

This seems to be a standard situation - so it should be possible with a DB like oracle
(that's what i hope at least ;-).

Thanks,
Dirk

Aravind Yalamanchi <ayalaman_at_yahoo.com> schrieb in im Newsbeitrag: 38A04967.7EF52DEC_at_yahoo.com...
>
> Tablespace does not control what a user can see and cannot see.
> In your case, the owner of the table has to grant select (and other
> privileges) to the other users. Also if you want the other users to
> access these tables with out schema extensions, you can use synonyms
>
> SVRMGR> connect a/a
> Connected.
> SVRMGR> create table tab1 (a int, b int);
> Statement processed.
> SVRMGR> insert into tab1 values (1,2);
> 1 row processed.
> SVRMGR> insert into tab1 values (1,2);
> 1 row processed.
> SVRMGR> insert into tab1 values (1,2);
> 1 row processed.
> SVRMGR> commit
> Statement processed.
> SVRMGR> connect b/b
> Connected.
> SVRMGR> select * from tab1;
> select * from tab1
> *
> ORA-00942: table or view does not exist
> SVRMGR> connect a/a
> Connected.
> SVRMGR> grant select on tab1 to b;
> Statement processed.
> SVRMGR> connect b/b
> Connected.
> SVRMGR> select * from tab1;
> select * from tab1
> *
> ORA-00942: table or view does not exist
> SVRMGR> select * from a.tab1;
> A B
> ---------- ----------
> 1 2
> 1 2
> 1 2
> 3 rows selected.
> SVRMGR> create synonym tab1 for a.tab1;
> Statement processed.
> SVRMGR> select * from tab1;
> A B
> ---------- ----------
> 1 2
> 1 2
> 1 2
> 3 rows selected.
> SVRMGR>
>
> Dirk Poppke wrote:
>
> > Hi there!
> >
> > I want different users to work on the same tables.
> > Anybody should see the tables of anybody. Therefore, I created a
> > Tablespace and set it as the default Tablespace for every user.
> >
> > Nevertheless, every user can only see its own tables.
> > What's wrong?
> >
> > Thanks,
> > Dirk
>
Received on Tue Feb 08 2000 - 11:38:38 CST