Oracle Advanced QUEUE does not exist is other schema

Question

I have the same problem that is described in this issue: Grant permission to queues to another schema in oracle.

But given permissions to the other user doesn't work at all.

My queue:

   DBMS_AQADM.create_queue_table (
      queue_table          => 'event_queue_tab',
      queue_payload_type   => 't_event_queue_payload',
      multiple_consumers   => TRUE,
      comment              => 'Queue Table For Event Messages',
      secure => false);

   -- Create the event queue.
   DBMS_AQADM.create_queue (queue_name    => 'event_queue',
                            queue_table   => 'event_queue_tab');

   -- Start the event queue.
   DBMS_AQADM.start_queue (queue_name => 'event_queue');

This queue as created using schema USER1. In this schema, I have a package pkg1 with a procedure when I call it, its enqueue:

PROCEDURE proc1
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      l_enqueue_options      DBMS_AQ.ENQUEUE_OPTIONS_T;
      l_message_properties   DBMS_AQ.MESSAGE_PROPERTIES_T;
      l_message_handle       RAW (16);
      l_queue_msg            t_event_queue_payload;
   BEGIN
      l_queue_msg := t_event_queue_payload ('give_me_a_prod');

      DBMS_AQ.enqueue (queue_name           => 'event_queue',
                       enqueue_options      => l_enqueue_options,
                       message_properties   => l_message_properties,
                       payload              => l_queue_msg,
                       msgid                => l_message_handle);
      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (
            SQLERRM || ' - ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
   END proc1;

I have a second schema USER2 who have privileges to execute pkg1 by a specific ROLE (ROLE1). But when he calls proc1, receive the next error:

ORA-24010: QUEUE USER2.EVENT_QUEUE does not exist - ORA-06512: at "SYS.DBMS_AQ", line 180
ORA-06512: at "USER1.PKG1", line 1808

I've executed this privilege command in USER1 but without success:

BEGIN
   DBMS_AQADM.grant_queue_privilege (privilege      => 'ALL',
                                     queue_name     => 'USER1.event_queue',
                                     grantee        => 'USER2',
                                     grant_option   => TRUE);
END;

I'm really starting to understand how Ad.Queues works. Am I missing something here? Thanks.

EDIT1: After the grant given the privileges for this queue:

SELECT grantee,
       owner,
       name,
       grantor,
       enqueue_privilege,
       dequeue_privilege
  FROM queue_privileges
 WHERE name = upper('event_queue');

ROLE1   USER1   EVENT_QUEUE USER1   1   1
USER2   USER1   EVENT_QUEUE USER1   1   1

Answer 1

Just a guess, does it have something to do with synonyms? Because the error message says USER2.QUEUE doesn't exist. Maybe its not able to touch User1 queue, because internally it is trying to find it in it's own schema? Try giving queue name in procedure as user1.event_queue.

What I mean is:

PROCEDURE proc1
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      l_enqueue_options      DBMS_AQ.ENQUEUE_OPTIONS_T;
      l_message_properties   DBMS_AQ.MESSAGE_PROPERTIES_T;
      l_message_handle       RAW (16);
      l_queue_msg            t_event_queue_payload;
   BEGIN
      l_queue_msg := t_event_queue_payload ('give_me_a_prod');

      DBMS_AQ.enqueue (queue_name           => 'user1.event_queue',
                       enqueue_options      => l_enqueue_options,
                       message_properties   => l_message_properties,
                       payload              => l_queue_msg,
                       msgid                => l_message_handle);
      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (
            SQLERRM || ' - ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END proc1;

Why I say so? Because when you are giving permission you are explicitly mentioning the schema USER1 before event_queue, and that procedure works. But not doing the same when using the enqueue procedure.