3

I have a schema that owns some queues and an application that connects to that schema and uses the queues. For security reasons I want to create a user schema that can access the queue and I want the application to use the user schema from now on.

I gave queue privileges to the user like this:

BEGIN

    FOR Q IN (SELECT * FROM ALL_QUEUES WHERE owner = 'OWNER_SCHEMA')
        LOOP
            DBMS_AQADM.GRANT_QUEUE_PRIVILEGE('ALL', 'OWNER_SCHEMA.' || Q.NAME, 'USER_SCHEMA', FALSE);
        END LOOP;

END;

The problem is that the application fails cause it tries to access a queue owned by the user schema, which does not exist.

I tried to manually enqueue a message using the USER schema:

DECLARE
    msg SYS.AQ$_JMS_TEXT_MESSAGE;
    queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
    msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;
    msg_id RAW(16);
    BEGIN
    msg := SYS.AQ$_JMS_TEXT_MESSAGE.CONSTRUCT();
    msg.set_text('
{
  "someKey": "someValue"
}
');
    DBMS_AQ.ENQUEUE( queue_name => 'SOME_QUEUE'
      , enqueue_options => queue_options
      , message_properties => msg_props
      , payload => msg
      , msgid => msg_id);
      COMMIT;
END;

and it failed with the following error:

ORA-24010: QUEUE USER_SCHEMA.SOME_QUEUE does not exist
ORA-06512: at "SYS.DBMS_AQ", line 185
ORA-06512: at line 18

but when I try to enqueue the message using also the USER schema but reference the queue from the OWNER schema it worked (queue_name => 'SCHEMA_OWNER.SOME_QUEUE'). This worked:

DECLARE
        msg SYS.AQ$_JMS_TEXT_MESSAGE;
        queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
        msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;
        msg_id RAW(16);
        BEGIN
        msg := SYS.AQ$_JMS_TEXT_MESSAGE.CONSTRUCT();
        msg.set_text('
    {
      "someKey": "someValue"
    }
    ');
        DBMS_AQ.ENQUEUE( queue_name => 'SCHEMA_OWNER.SOME_QUEUE'
          , enqueue_options => queue_options
          , message_properties => msg_props
          , payload => msg
          , msgid => msg_id);
          COMMIT;
    END;

I tried to create a synonym for the queue but get the same result.

The only thing that makes the application work is adding one more config property that is the schema owner so that the app can use the schema user to connect, but then use the schema owner to reference the queue, but this is not the desired solution, cause there are a lot of envs and the config file for all envs would need to be changed.

Is there a way to reference the queue directly with the USER schema?

Dinu Nicolae
  • 1,019
  • 2
  • 17
  • 42
  • Yeah, unfortunately [AQ does not support synonyms for queues](https://docs.oracle.com/database/121/ADQUE/manage.htm#ADQUE2584). – kfinity Feb 22 '21 at 17:32
  • @kfinity Any idea how this could be done? How do I make it so that the application does not even know about the OWNER schema? – Dinu Nicolae Feb 23 '21 at 08:39
  • Not really. It looks like [some people have tried creating a package or procedure](https://stackoverflow.com/questions/41425808/oracle-advanced-queue-does-not-exist-is-other-schema) with the owner schema hardcoded there, and then having the application call that procedure instead of DBMS_AQ. So you might be able to encapsulate it somehow. – kfinity Feb 23 '21 at 15:17

0 Answers0