oracle trigger execute DBMS_AQ gets insufficient privilege error

gpa picture gpa · Dec 11, 2012 · Viewed 10k times · Source

I am using oracle 11.2.

Database users:

  1. AQADM : user owns queues, and has AQ_ADMINISTRATOR_ROLE
  2. SCOTT : jms user, has following privileges.

I have following stored procedure, which compiled OK. and it is invoked from trigger on table.

CREATE OR REPLACE PROCEDURE scott.PROC_JMS_ENQUEUE (NAME      VARCHAR,
                                          MESSAGE      IN VARCHAR)
AS
  msg                  SYS.AQ$_JMS_TEXT_MESSAGE;
  queue_options        DBMS_AQ.ENQUEUE_OPTIONS_T;
  msg_props            DBMS_AQ.MESSAGE_PROPERTIES_T;
  msg_id               RAW (16);
  no_consumers_error   EXCEPTION;
  PRAGMA EXCEPTION_INIT (no_consumers_error, -24033);
BEGIN
-- Ensure what is sent will be a JMS message
 msg := SYS.AQ$_JMS_TEXT_MESSAGE.CONSTRUCT ();
 msg.set_text (MESSAGE);

 --ENQUEUE
 DBMS_AQ.ENQUEUE (queue_name           => 'aqadm.my_queue',
                enqueue_options      => queue_options,
                message_properties   => msg_props,
                payload              => msg,
                msgid                => msg_id);
  -- Without the following, the procedure will die if none
  -- Is listening for cache control
 EXCEPTION
 WHEN no_consumers_error
 THEN
  -- Output it in case, but otherwise swallow
  DBMS_OUTPUT.PUT_LINE (
     'No interested parties are listening to messages');
 END;
 /

I have following GRANT to user

GRANT EXECUTE ON AQ_USER_ROLE TO scott;
GRANT EXECUTE ON SYS.DBMS_AQ TO scott;
GRANT EXECUTE ON SYS.DBMS_AQIN TO scott;
--GRANT EXECUTE ON SYS.DBMS_AQJMS_INTERNAL TO scott;
--GRANT EXECUTE ON SYS.DBMS_TRANSFORM TO scott;
GRANT EXECUTE ANY PROCEDURE TO scott;

When i execute above stored procedure, or update table that fires trigger code, i get following error:

exec PROC_JMS_ENQUEUE('Test Message','Dam');

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_AQ", line 169
ORA-06512: at "SCOTT.PROC_JMS_ENQUEUE", line 19
ORA-06512: at line 1

EDIT: Here is privileges view, all are granted as SYS as SYSDBA enter image description hereenter image description here

Answer

gpa picture gpa · Dec 11, 2012

It worked when i grant following

GRANT ENQUEUE ANY QUEUE TO SCOTT;

Oracle sucks on one consolidated documentation!

Also, some reason GRANT has to be "direct" not via "role" in order to execute via PL/SQL Trigger or Procedure!!!!!