Sample DBA Actions as Preparation for Working with AQ

Creating a User as an AQ Administrator

To set a user up as an AQ administrator, you must the following steps

CONNECT system/manager 
CREATE USER aqadm IDENTIFIED BY aqadm;   
GRANT AQ_ADMINISTRATOR_ROLE TO aqadm;   
GRANT CONNECT, RESOURCE TO aqadm;   
   

Additionally, you might grant execute on the AQ packages as follows:

GRANT EXECUTE ON DBMS_AQADM TO aqadm;   
GRANT EXECUTE ON DBMS_AQ TO aqadm;   
   

This allows the user to execute the procedures in the AQ packages from within a user procedure.

Creating User AQUSER1 and AQUSER2 as Two AQ Users

If you want to create an AQ user who creates and accesses queues within his/her own schema, follow the steps outlined in the previous section except do not grant the AQ_ADMINISTRATOR_ROLE.

CONNECT system/manager 
CREATE USER aquser1 IDENTIFIED BY aquser1;   
GRANT CONNECT, RESOURCE TO aquser1;   
   

Additionally, you might grant execute on the AQ packages as follows:

GRANT EXECUTE ON DBMS_AQADM to aquser1;   
GRANT EXECUTE ON DBMS_AQ TO aquser1;   
 

If you wish to create an AQ user who does not create queues but uses a queue in another schema, first follow the steps outlined in the previous section. In addition, you must grant object level privileges. However, note that this applies only to queues defined using 8.1 compatible queue tables.

CONNECT system/manager 
CREATE USER aquser2 IDENTIFIED BY aquser2;   
GRANT CONNECT, RESOURCE TO aquser2;   
   

Additionally, you might grant execute on the AQ packages as follows:

GRANT EXECUTE ON DBMS_AQADM to aquser2;   
GRANT EXECUTE ON DBMS_AQ TO aquser2;   

For aquser2 to access the queue, aquser1_q1 in aquser1 schema, aquser1 must execute the following statements:

CONNECT aquser1/aquser1 
EXECUTE DBMS_AQADM.GRANT_QUEUE_PRIVILEGE( 
  'ENQUEUE','aquser1_q1','aquser2',FALSE);