Oracle Advanced QUEUE 不存在是其他模式

问题描述

我遇到了这个问题中描述的相同问题:在 oracle 中授予队列到另一个架构的权限.

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.

我的队列:

   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');

此队列是使用架构 USER1 创建的.在这个模式中，我有一个包 pkg1 和一个过程，当我调用它时，它的入队:

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;

我有第二个模式 USER2，它有权按特定角色 (ROLE1) 执行 pkg1.但是当他调用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

我已经在 USER1 中执行了这个权限命令，但没有成功:

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;

我真的开始了解 Ad.Queues 的工作原理了.我在这里错过了什么吗?谢谢.

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

授予此队列的权限后:

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

推荐答案

猜一猜，跟同义词有关系吗?因为错误消息说 USER2.QUEUE 不存在.也许它无法触及 User1 队列，因为它在内部试图在自己的架构中找到它?尝试在过程中将队列名称指定为 user1.event_queue.

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.

我的意思是:

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;

我为什么这么说?因为当您授予权限时，您会在 event_queue 之前明确提及架构 USER1，并且该过程有效.但在使用入队过程时不一样.

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.

这篇关于Oracle Advanced QUEUE 不存在是其他模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！