Creating AQ Administrators and Users

Creating a User as an AQ Administrator

To set a user up as an AQ administrator, do the following:

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

Additionally, you can grant execute privilege 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 Users AQUSER1 and AQUSER2

If you want to create AQ users who create and access queues within their own schemas, follow the steps outlined in "Creating a User as an AQ Administrator" except do not grant the AQ_ADMINISTRATOR_ROLE.

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

Additionally, you can grant execute privilege 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 can 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);