How to execute oracle DMBS_AQ.REGISTER behalf of different user?

Question

One privileged scheduler user - user1 receive email notifications while two another (user2, user3) no.

I want to execute code below on user3 schema, we tried this successfully on user2 (code executed directly on schema with temporary sys.dbms_aq access) so he receive now his notifications. Database access is enabled for bot user agents on user1.

So as you see I know 'what' resolve the problem but don't know how to do it :-)

Problem is - we do not have direct access to second account (user3) how to execute this code behalf user3 ?

declare  
  reginfo1    sys.aq$_reg_info;  
  reginfolist sys.aq$_reg_info_list;  
begin  

  reginfo1    := sys.aq$_reg_info('SYS.SCHEDULER$_EVENT_QUEUE:SCHED$_AGT2$_X',  
                                  1,  
                                  'plsql://SYS.SCHEDULER$_JOB_EVENT_HANDLER',  
                                  null);  
  reginfolist := sys.aq$_reg_info_list(reginfo1);  
  dbms_aq.register(reginfolist, 1);  
end; 

Already tried create procedure on user3 and execute immediate annonymous block but it still register with USER# 1 not 3 in DBA_QUEUE_SUBSCRIBERS.

Database version is 11.2.0.3.0 on unix.

Answer 1

This is my working solution

>sqlplus user1  

Connected to:  
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production  

SQL> grant execute on sys.dbms_aq to user1;  

Grant succeeded.  

SQL> alter user user3 grant connect through user1;  

User altered.  

SQL> connect user1[user3]  
Enter password:  
Connected.  

SQL> create or replace procedure sched_not_add_q as  
 2    reginfo1    sys.aq$_reg_info;  
 3    reginfolist sys.aq$_reg_info_list;  
 4  begin  
 5    reginfo1    := sys.aq$_reg_info('SYS.SCHEDULER$_EVENT_QUEUE:SCHED$_AGT2$_3',  
 6                                    1,  
 7                                    'plsql://SYS.SCHEDULER$_JOB_EVENT_HANDLER',  
 8                                    null);  
 9    reginfolist := sys.aq$_reg_info_list(reginfo1);  
10    dbms_aq.register(reginfolist, 1);  
11  end;  
12  /  

Procedure created.  

SQL> exec sched_not_add_q;  

PL/SQL procedure successfully completed.  

SQL> drop procedure sched_not_add_q;  

Procedure dropped.  

SQL> connect user1  

Connected.  

SQL> revoke execute on sys.dbms_aq from user3;  

Revoke succeeded.  

SQL> alter user user3 revoke connect through user1;  

User altered.  

SQL> exit;  

> 

Conception base on: http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23asktom-1906478.html