- 17.9K All Categories
- 3.3K Industry Applications
- 3.3K Intelligent Advisor
- 60 Insurance
- 534.4K On-Premises Infrastructure
- 137.7K Analytics Software
- 38.5K Application Development Software
- 5.3K Cloud Platform
- 109.1K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 70.8K Infrastructure Software
- 105.1K Integration
- 41.5K Security Software
Failure to establish a subscription against secure queue in 18c
I am having a problem in creating a subscription to an Oracle AQ. Whenever I try to execute the statement below:
subscriber := sys.aq$_agent(
queue_name => 'AQADMIN.AQEMS_RQEVENT_OUTBOX_Q',
subscriber => subscriber,
rule => 'CORRID=231',
transformation => NULL,
queue_to_queue => null
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.