Correcting an ORA-01031 Error While Enqueuing or Dequeuing Messages

If a user who does not have the required privileges attempts to enqueue or dequeue messages, then Oracle Database returns the following error:

ORA-01031: insufficient privileges

The enqueue or dequeue operation fails when Oracle Database returns this message. To correct the problem, grant ENQUEUE or DEQUEUE privileges on the queue to the user.

To grant ENQUEUE or DEQUEUE privileges to a user: 

1. On a command line, open SQL*Plus and connect to the database as an administrative user, such as the Oracle Streams administrator or SYSTEM.

   See Starting SQL*Plus and Connecting to the Database for more information about starting SQL*Plus.

2. Run the GRANT_QUEUE_PRIVILEGE procedure in the DBMS_AQADM package to grant the required queue privileges to the user.

   For example, to grant the ENQUEUE privilege to the hr user on the strmadmin.streams_queue queue, run the following procedure:

   BEGIN
      DBMS_AQADM.GRANT_QUEUE_PRIVILEGE (
         privilege  => 'ENQUEUE', 
         queue_name => 'strmadmin.streams_queue',
         grantee    => 'hr');
   END;
   /
   

   To grant the DEQUEUE privilege to the hr user on the strmadmin.streams_queue queue, run the following procedure:

   BEGIN
      DBMS_AQADM.GRANT_QUEUE_PRIVILEGE (
         privilege  => 'DEQUEUE', 
         queue_name => 'strmadmin.streams_queue',
         grantee    => 'hr');
   END;
   /