1 Reply Latest reply: Aug 10, 2011 1:37 PM by 758358 RSS

    Problem accessing Oracle Advanced Queuing from different schema

    417309
      Hi all,

      I have set up 2 queues & one topic for the Oracle Advanced Queuing on Oracle 10.2. I've set up the messaging thru WebLogic 11g. This is in schema A. It all works fine when connected as A. However I have some new features to try so I created schema B and "copied" over the tables I need from A - not any of the queue tables. Both A and B have access to the AQ packages:

      Grant aq_user_role TO B; (and A)
      Grant execute ON sys.dbms_aqadm TO B;
      Grant execute ON sys.dbms_aq TO B;
      Grant execute ON sys.dbms_aqin TO B;
      Grant execute ON sys.dbms_aqjms TO B;

      When I try the application logged in as B, I get an "insufficient privileges" error in a procedure at the line:

      DBMS_AQADM.purge_queue_table
      (queue_table => 'A.arf_jms_topic',
      purge_condition => 'qtview.queue =''JOBSTATUSCHANGETOPIC'' '
      || 'and qtview.msg_state = ''READY'' '
      || 'and qtview.consumer_name='''
      || v_machine
      || '''',
      purge_options => po);

      I also had A grant all on arf_jms_topic but that didn't help.

      I have further tried:
      BEGIN
           dbms_aqadm.grant_queue_privilege('ALL', 'EXECUTESCHEDULEQUEUE', 'B', FALSE);
      dbms_aqadm.grant_queue_privilege('ALL', 'JOBREADYTORUNQUEUE', 'B', FALSE);
      dbms_aqadm.grant_queue_privilege('ALL', 'JOBSTATUSCHANGETOPIC', 'B1', FALSE);
      END;
      /
      with no difference.

      Any suggestions would be helpful. Please let me know if I need to explain further.

      Jim Greetham
        • 1. Re: Problem accessing Oracle Advanced Queuing from different schema
          758358
          Hi,

          You may find B needs privileges to SELECT from the aq$ views for purge_queue_table to work, e.g.
          grant select on A.aq$arf_jms_topic to B
          Also try:
          GRANT aq_administrator_role TO B;
          BEGIN
             DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
                privilege          =>    'MANAGE_ANY', 
                grantee            =>    'B', 
                admin_option       =>     FALSE);
          END;
          /
          Thanks,
          Paul