Forum Stats

  • 3,769,714 Users
  • 2,253,014 Discussions
  • 7,875,157 Comments

Discussions

Failure to establish a subscription against secure queue in 18c

RC2017
RC2017 Member Posts: 4 Red Ribbon

I am having a problem in creating a subscription to an Oracle AQ.  Whenever I try to execute the statement below:

DECLARE

    subscriber   sys.aq$_agent;

BEGIN

    subscriber   := sys.aq$_agent(

        'EMSAQUSER',

        NULL,

        NULL

    );

    dbms_aqadm.add_subscriber(

        queue_name       => 'AQADMIN.AQEMS_RQEVENT_OUTBOX_Q',   

        subscriber       => subscriber,

        rule             => 'CORRID=231',

        transformation   => NULL,

        queue_to_queue   => null

    );

END;

/

I get this result:

Error report -

ORA-01031: insufficient privileges

ORA-06512: at "SYS.DBMS_AQADM", line 69

ORA-06512: at "SYS.DBMS_AQADM_INV", line 379

ORA-06512: at "SYS.DBMS_AQADM", line 65

ORA-06512: at "SYS.DBMS_AQADM", line 875

ORA-06512: at line 9

01031. 00000 -  "insufficient privileges"

*Cause:    An attempt was made to perform a database operation without

           the necessary privileges.

*Action:   Ask your database administrator or designated security

           administrator to grant you the necessary privileges

This queue in question is a Secure Queue.

This problem is very similar to the one reported by another user in this forum.  I have already followed the resolution steps detailed by that issue, and I still have the problem.  Please note that were recently upgraded from Oracle11g to Oracle18c; in 11g, the subscription worked perfectly, once we upgraded to 18c, this problem has appeared  in ALL of our 18c databases. 

I have tried many different approaches to resolving with no resolution thus far:

  • I granted AQ_ADMINISTRATOR_ROLE to the subscriber
  • I traced the the execution of the anonymous block (shown above) and found nothing unusual.
  • I have granted ENQUEUE and DEQUEUE to the subscriber for the queue in question
  • Since this is a secure queue, I created user EMSAQUSER, I have created an AQ$AGENT by the same name, and excuted dbms_aqadm.enable_db_acess for said user.

I have a total of 4 database environments that used to work with this subscription in 11g but are now failing in 18c.