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