Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Is there a way to assign rights to schema X to user Y without iterating through every object?
On Apr 17, 1:45 pm, Jeff Kish <kishjj..._at_charter.net> wrote:
> Hi.
>
> I'm trying to figure out the easiest or most appropriate way to give
> user Y read/write/delete/update on everything in a given schema X
> (just like the creator of schema X has)?
>
> I'm using alter session set current_schema="X" when I log in as Y.
>
> Is it possible? All I see is grant delete on xxx to user y
>
> I'm hoping to avoid having to create scripts that iterate through
> everything and also I'd have to maintain them.
>
> Any gotcha's are appreciated.
> thanks
> Jeff
That 'script' would be a 4-liner and not difficult to maintain.
begin
for tab in (select table_name from user_tables) loop
execute immediate 'grant select,insert,update, delete on '||
tab.table_name||'to ....';
end loop;
end;
/
And that's all.
Granting automatically privilege would be a security breach and a
violation of the 3-Schema (Conceptual/Logical/Physical) architecture
which is implemented in most database product.
-- Sybrand Bakker Senior Oracle DBAReceived on Tue Apr 17 2007 - 06:50:48 CDT