Re: Is there a way to assign rights to schema X to user Y without iterating through every object?

On 17 Apr 2007 04:50:48 -0700, sybrandb <sybrandb_at_gmail.com> wrote:

>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.
Oh Sybrandb, thanks so much.

I guess I can figure some extra/similar things for procedures and functions and views.

I appreciate it. yea.. that is much simplier than the monstrosity I was attempting to unwittingly/unwitlessly create. Received on Tue Apr 17 2007 - 17:02:59 CDT