Re: Different user can't see the otherones tables

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Wed, 9 Feb 2000 11:41:31 +0100
Message-ID: <87rg8v$ndt$1@news2.isdnet.net>

Why don't you use a unique schema for all your objects. All admins create the objects in this schema.

example (assuming "public" schema is pub): 1)admin1:
create table pub.tab1 (...);
create synonym tab1 for pub.tab1;
connect pub/pub
grant all on tab1 to public;
2)admin2:
drop table pub.tab1;

As all objects are in schema pub, you haven't to know who created it.

The only problem is you have to connect to pub to grant privilege on the objects.
A workaround is to create a procedure:
create or replace procedure pub.GrantPriv (p_name varchar2) is
  cid integer;
  res integer;
begin
  cid := dbms_sql.open_cursor;
  dbms_sql.parse (cid, 'grant all on '||p_name||' to public', dbms_sql.native);   res := dbms_sql.execute (cid);
  dbms_sql.close_cursor (cid);
exception
  when others then
    if dbms_sql.is_open (cid) then
      dbms_sql.close_cursor (cid);
    end if;
  raise;
end;
/
create public synonym grantpriv for pub.grantpriv;

then admin1 can do:
exec grantpriv('tab1');
to grant privileges on pub.tab1 to public.

If you don't want admin2 use the owner name, you can create a drop procedure likewise: create or replace procedure pub.DropTable (p_name varchar2) is
  cid integer;
  res integer;
begin
  cid := dbms_sql.open_cursor;
  dbms_sql.parse (cid, 'drop table '||p_name||' cascade constraints',

                  dbms_sql.native);

  res := dbms_sql.execute (cid);
  dbms_sql.close_cursor (cid);
exception
  when others then
    if dbms_sql.is_open (cid) then
      dbms_sql.close_cursor (cid);
    end if;
  raise;
end;
/
create public synonym droptable for pub.droptable;

etc...

That way, it is not necessary that user pub has the right to connect himself (that is has the create session privilege). It's just a tank for the objects.

Hth
Michel

---

> Hi!

> Aravinds example works, that's not the point. The Problem is
> that I don't like to add the owner to the tablename, because
> I don't know the name of the creator. The name of the creator
> can change dynamically over the lifetime of the System.

> An Example:

> Admin1 creates a table a. Different users should access the table a.
> Afterwards, Admin2 deletes the table a und creates a new table with name
> a. The Different users should access the table a (they don't know, that
> the table now belongs to a different admin).

> When using synonyms, users didn't have to add the owner of a table,
> but at least when admin2 wants to delete the table a, he has to know
> the owner of the table.

> What i would like to have (and didn't know if its possible), is one
> simple namespace for all tables of different users (I first thought
> that tablespaces are something like that...)

> Thanks for your help,
> Dirk

[snipped] Received on Wed Feb 09 2000 - 04:41:31 CST