Re: Privilege on another sheme
Date: 29 Dec 2001 12:39:41 -0800
Message-ID: <a0l9md0k2q_at_drn.newsguy.com>
In article <a0l3sd$4nsa$1_at_as201.hinet.hr>, "Stjepan says...
>
>"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
>news:njdo2u4obmvttcfqq4hubrf4711tl2i5nu_at_4ax.com...
>> On Fri, 28 Dec 2001 09:33:37 +0100, "Stjepan Brbot"
>> <Stjepan.Brbot_at_ZG.HiNet.HR> wrote:
>>
>> >I've got user "A" having i.e. 100 objects in his scheme. What is the
>> >easiest way to grant user "B" full privilege on all object from
scheme
>> >belonging to user "A"? I'd like user "B" to have completely all
>> >privileges on objects from user "A" scheme. I know user "B" cannot be
>> >owner but all other privileges; select, delete, alter, update...
>>
>>
>> Please RTFM, sql reference manual, the GRANT command, object
>> privileges.
>
>Dear Sybrand, I know how to manually grant privileges to one particular
>object but, as I mentioned in IP, there is a lot of objects in scheme
>and I really do not want to do this manually for every object!
>
there is no "grant on schema", its all done at the object level. try automating the process:
scott_at_ORA817DEV.US.ORACLE.COM> begin
2 for x in ( select *
3 from user_objects 4 where object_type in ( 'TABLE', 'PROCEDURE', 'FUNCTION', 'VIEW', 'TYPE' ) 5 ) 6 loop 7 begin 8 execute immediate 'grant all on ' || x.object_name || ' to ops$tkyte'; 9 exception 10 when others then 11 dbms_output.put_line( 'grant on ' || x.object_type || ' ' || 12 x.object_name || ' failed' ); 13 dbms_output.put_line( sqlerrm ); 14 end;
15 end loop;
16 end;
17 /
PL/SQL procedure successfully completed.
>Please, do not break my crossposts!
>
(please don't crosspost!)
>--
>
>Stjepan Brbot <Stjepan.Brbot_at_ZG.HiNet.HR>
>
>
>
>
>
-- Thomas Kyte (tkyte_at_us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Sat Dec 29 2001 - 21:39:41 CET