We grant the following priviliges on schema A to user B for inserting into a queue in Oracle 12c:
GRANT CREATE SESSION TO &UNAME_ENQUEUE;
GRANT EXECUTE ON SYS.DBMS_AQ TO &UNAME_ENQUEUE;
GRANT EXECUTE ON SYS.DBMS_AQADM TO &UNAME_ENQUEUE;
GRANT EXECUTE ON SYS.DBMS_AQIN TO &UNAME_ENQUEUE;
GRANT EXECUTE ON SYS.DBMS_AQJMS TO &UNAME_ENQUEUE;
...
DBMS_AQADM.GRANT_QUEUE_PRIVILEGE (
privilege => 'ENQUEUE',
queue_name => v_queue_name,
grantee => v_grantee_name,
grant_option => FALSE);
The queue table is defined as:
dbms_aqadm.create_queue_table(queue_table => 'queue_name_t', queue_payload_type => 'sys.aq$_jms_message');
...
GRANT INSERT ON &USERNAME..queue_name_t TO &UNAME_ENQUEUE;
GRANT SELECT ON &USERNAME..queue_name_t TO &UNAME_ENQUEUE;
The queue is accessed through the JMS interface. We noticed that for messages with a size greater than x, Oracle wants to store the message payload as a CLOB. First of all, we do not know x. Guessing from the Specification of the JMS message type, it should be 4000 for text messages and 2000 for byte messages, as smaller messages would fit into text_vc or bytes_raw. However, the payload is only about 500 characters long. (We do set a couple of headers, but those go into a separate header 'field', if I understand the type correctly).
The main problem however is that we get the error ORA-01031: insufficient privileges when user B wants to insert a CLOB sized message into schema A. Authorized as user A the problem does not arise.
1. How can we grant the necessary privileges to user B?
Please provide more extensive exception stack for ORA-01031: insufficient privileges this message doesn't say much about the root cause
Here are my thoughts.
In Oracle DB there's a precondition for the enqueue of JMS types like sys.aq$_jms_message Try enabling it for your queue.
begin
dbms_aqadm.enable_jms_types(queue_table => v_queue_name);
end;
/
btw. Payloads with LOBs require explicit Select, Insert and Update privileges on the queue table
2.When does Oracle choose to use a (C)LOB?
No need to guess from docs. Payload type that you use for your queue stores text message either in text_vc or in text_lob variable. You wrote the payload is only about 500 characters long, and as you can see on the type definition field is declared as varchar2(4000) which doesn't necessarily mean it's about characters, it can be about bytes as well - when there's no explicit declaration it's determined by the nls param (see also link)
select * from V$NLS_PARAMETERS t where t.PARAMETER = 'NLS_LENGTH_SEMANTICS';
For aq$_jms_text_message there are 2 options, either you provide large object datatype as an input param, or your message >= 4000 and that's why Oracle stores it as a clob. Similar situation is for aq$_jms_bytes_message (payload IN RAW if the length of bytes_lob is =< 32767 vs payload IN BLOB )
SYS.AQ$_JMS_MESSAGE (
text_vc varchar2(4000),
text_lob clob,
...
-- set_text sets payload in varchar2 into text_vc if the length of
-- payload is <= 4000, into text_lob if otherwise.
MEMBER PROCEDURE set_text ( payload IN VARCHAR2 ),
..
--
-- set_text sets payload in clob in text_lob.
MEMBER PROCEDURE set_text ( payload IN CLOB ),
...
)
btw. there are also methods like DBMS_AQADM.ENABLE_DB_ACCESS for Agents using HTTP protocols and secure queues, maybe it's worth checking this