REPOST: Re: Privilege on another sheme
Date: 29 Dec 2001 12:39:41 -0800
Message-ID: <3$--$$-$-%$_$%_%_$_at_news.noc.cabal.int>
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 Corp ========= WAS CANCELLED BY =======: Path: news.sol.net!spool0-nwblwi.newsops.execpc.com!newsfeeds.sol.net!news-out.visi.com!hermes.visi.com!newsfeed.direct.ca!look.ca!newsfeed1.earthlink.net!newsfeed.earthlink.net!uunet!lax.uu.net!news.navix.net!u.n.a.c.4.n.c.3.l.l.e.r From: Thomas Kyte <tkyte_at_us.oracle.com> Newsgroups: news.admin.censorship,alt.test,comp.databases.oracle.tools Subject: cmsg cancel <a0l9md0k2q_at_drn.newsguy.com> Control: cancel <a0l9md0k2q_at_drn.newsguy.com> Date: Wed, 2 Jan 2002 02:16:50 GMT Organization: Navix Internet Subscribers Lines: 2 Message-ID: <cancel.a0l9md0k2q_at_drn.newsguy.com> NNTP-Posting-Host: 166.102.15.34 X-Trace: iac5.navix.net 1009948142 29203 166.102.15.34 (2 Jan 2002 05:09:02 GMT) X-Complaints-To: abuse_at_navix.net NNTP-Posting-Date: 2 Jan 2002 05:09:02 GMT X-No-Archive: yes Comment: Dude, where's my NewsAgent? autocancelReceived on Sat Dec 29 2001 - 21:39:41 CET