I have set up 2 queues & one topic for the Oracle Advanced Queuing on Oracle 10.2. I've set up the messaging thru WebLogic 11g. This is in schema A. It all works fine when connected as A. However I have some new features to try so I created schema B and "copied" over the tables I need from A - not any of the queue tables. Both A and B have access to the AQ packages:
Grant aq_user_role TO B; (and A)
Grant execute ON sys.dbms_aqadm TO B;
Grant execute ON sys.dbms_aq TO B;
Grant execute ON sys.dbms_aqin TO B;
Grant execute ON sys.dbms_aqjms TO B;
When I try the application logged in as B, I get an "insufficient privileges" error in a procedure at the line:
I also had A grant all on arf_jms_topic but that didn't help.
I have further tried:
dbms_aqadm.grant_queue_privilege('ALL', 'EXECUTESCHEDULEQUEUE', 'B', FALSE);
dbms_aqadm.grant_queue_privilege('ALL', 'JOBREADYTORUNQUEUE', 'B', FALSE);
dbms_aqadm.grant_queue_privilege('ALL', 'JOBSTATUSCHANGETOPIC', 'B1', FALSE);
with no difference.
Any suggestions would be helpful. Please let me know if I need to explain further.