ORA-01031: insufficient privileges creating JMS connection to Oracle topic
One of my colleagues is facing the problem of creating a (not long term) Oracle queue subscriber with Java.
The queue belongs to the same schema (say OWNER); it tries to connect as another user (say SUBSCRIBER). SUBSCRIBER got DEQUEUE privileges (and ENQUEUE, for which he stands) from OWNER.
When it creates a queue with "multiple_consumers" set to FALSE, everything works fine. When "multiple_consumers" is set to "TRUE" (the setting we want) 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 it connects as OWNER, it succeeds.
How can I decide what privileges I need to provide to the SUBSCRIBER?
a source to share
You either need to create a trusted subscriber or provide AQ_ADMINISTRATOR_ROLE to the provided connecting user, not a great security model, but for now this is an Oracle implementation.
https://forums.oracle.com/forums/thread.jspa?messageID=9793091
a source to share