REPOST: Re: Privilege on another sheme

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;

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?

autocancel