Oracle Advanced QUEUE 不存在是其他模式

本文介绍了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.