Re: user privileges; select any table

Create a function accepting a select statement and returning the ref cursor for the statement, grant execute on this function to the user you want to be able to get data and there you have it. The function will be executed with definer rights and will be able to select from the definer's tables. Invoker will get the data, but will not be able to select it directly. Note that function will have to be packaged along with ref cursor definition. Something like this will do:

connect schema01/password

create or replace package access_point as

type cursor_type is ref cursor;
function get_cursor(stmt in varchar2) return cursor_type; end;
/
create or replace package body access_point as

function get_cursor(stmt in varchar2) return cursor_type is
 c cursor_type;
begin
 open c for stmt; -- we will be able to open the cursor and select from correct table(s) here

• since this code will run with definer (that is, package owner) rights. return c; -- return opened cursor for further processing end;

end;
/

grant execute on access_point to user03
/

then you can call the access_point.get_cursor as user03 with a statement you need and fetch into the corresponding type/record from it:

declare
 c access_point.cursor_type;
 n number;
begin
 c := access_point.get_cursor('select id from emp');  loop
  fetch c into n;
  exit when c%notfound;

• process row here end loop; end; /

Note that although the access_point.cursor_type is weak, you will need to provide a suitable record variable when fetching from it (though at this point you will probably know the row layout since you know the query executed). I am not aware of any way to 'describe' this cursor and find out which columns of which types are there at runtime unless you use DBMS_SQL and return the DBMS_SQL cursor reference to subsequently fetch from it using DBMS_SQL functions and procedures (instead of explicit PL/SQL fetch from a ref cursor). Your code will be a bit more complex if you will decide to use DBMS_SQL, but will ultimately be much more flexible.

hth.

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"John Lasta" <lasta_at_chello.at> wrote in message news:Hy9h8.318064$V52.3212540_at_news.chello.at...


> how can an oracle database user make all his tables visible to another user,

> without granting object privileges, that means without granting every table

> seperately, e.g.:

> "grant select on <table01> to <user>"

> "grant select on <table02> to <user>"...

> ------------------------------------

> e.g.

> there are three oracle database users:

> - schema01

> - schema02

> - user03

> 

> user03 shall see all tables from schema01 but not those of schema02;

> in schema01 many tables are created (and deleted) permanently and user03

> wants to see them immediately without disturbing schema01 to give him the

> select privilege.

> 

> Any advice would be great !!

> lasta

> 

> 

> 

> 

>