ORA-01031: insufficient privileges creating JMS connection to Oracle topic

Question

One of my colleagues is having an issue creating a (non-durable) subscriber to an Oracle queue from Java.

The queue is owned by one schema (say OWNER); he's trying to connect as another user (say SUBSCRIBER). SUBSCRIBER has been granted DEQUEUE privileges (and ENQUEUE, for what it's worth) by OWNER.

When he creates the queue with the "multiple_consumers" option set to FALSE, everything works fine. When "multiple_consumers" is set to TRUE (the setting we need), it fails with the following:

oracle.jms.AQjmsException: ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 5757
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_AQJMS", line 129
ORA-06512: at line 1
    at oracle.jms.AQjmsSession.addDurableSubscriber(AQjmsSession.java:3708)
    at oracle.jms.AQjmsSession.createSubInOldMode(AQjmsSession.java:2742)
    at oracle.jms.AQjmsSession.createDurableSubscriber(AQjmsSession.java:2400)
    at oracle.jms.AQjmsSession.forceCreateOldSubscriber(AQjmsSession.java:2884)
    at oracle.jms.AQjmsSession.createConsumer(AQjmsSession.java:6820)

If he connects as OWNER, it succeeds.

How do I work out what privileges I need to grant to SUBSCRIBER?

Answer 1

You either have to create a durable subscriber, or have the AQ_ADMINSTRATOR_ROLE granted to the connecting user - not a great security model, but that is Oracle's implementation for the time being.