Re: "grant select" for all tables: Is it possible?

"Wolfram Roesler" <wr_at_spam.la> wrote in message news:Xns96AF91733910Awrgrpde_at_62.153.159.134...
> Hi,
>
> I have an Oracle user with lots of tables, and want to create another
> user that can see all of the first user's tables, but not modify any
> data. It's easy to do this by creating a grant for each table, like
> this (as user1):
>
> grant select on table1 to user2;
>
> Now, user2 can "select ... from user1.table1", but not update or
> insert into that table.
>
> Now, since user1 has lots of tables, I don't want to issue a grant
> for each and every table but rather configure user2 to automatically
> be granted "select", but neither "insert" nor "update", for each
> of user1's tables. Also, new tables created by user1 should be
> visible to user2 immediately without further ado.
>
> I'm using Oracle versions from 8i to 10g.
>
> Is there any way to do it?
>
> Thanks for any help
> W. Rösler

no single statement but you can do this as user 1

begin
for i in (select table_name from tabs) LOOP execute immediate 'grant select on '||i.table_name||' to user2'; end loop;
end;
/

and that will do it for you Received on Thu Aug 11 2005 - 07:36:59 CDT