AQ permissions for OJMS

Hi,

Versions: 9205 database on AIX, 10.1.2 app server on Win2k3, J2EE application making use of Oracle JMS.

Developers are creating a web facing application which needs to implement advanced queuing. Dev standards are that the app server should connect to a USER schema which contains synonyms, views etc and a DATA schema which contains the segments and PLSQL.

All works fine, except we are experiencing an issue with AQ and Oracle JMS.

Started out the obvious way

• grant AQ_ADMINISTRATOR_ROLE to the DATA schema and create the queues
• grant AQ_USER_ROLE, DBMS_AQADM queue privileges to to the USER schema.

but we cannot deploy the application (with Message Driven Beans) unless it connects to the DATA schema (cant find the queue).

Discovered the docs
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96587/qmanage.htm#73010 (User role section) state you should avoid granting AQ_USER_ROLE in Oracle9i and 8.1 since this role will not provide sufficient privileges for enqueuing or dequeuing, and that you have to effectively grant AQ_ADMIN_ROLE no matter what.

This seems to fly in the face of all the security advice I know. Has anyone else implemented this scenario, and if so, is there a better way?

Cheers
Adrian

--
http://www.freelists.org/webpage/oracle-l