How to access an Oracle queue with another user?

490 Views Asked by At

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?

0

There are 0 best solutions below