Oracle 8.1-Style Queues

Compatibility

For 8.1-style queues, the compatible parameter of init.ora and the compatible parameter of the queue table should be set to 8.1 to use the following features:

• Queue-level access control

• Nonpersistent queues (automatically created when queue table compatible = 8.1)

• Support for Oracle Parallel Server environments

• Rule-based subscribers for publish-subscribe

• Asynchronous notification

• Sender identification

• Separate storage of history management information

Security

AQ administrators of an Oracle9i database can create 8.1-style queues. All 8.1 security features are enabled for 8.1-style queues. Note that AQ 8.1 security features work only with 8.1-style queues. When you create queues, the default value of the compatible parameter in DBMS_AQADM.CREATE_QUEUE_TABLE is 8.1.

Table 4-1 lists the AQ security features and privilege equivalences supported with 8.1-style queues.

Table 4-1 Security with 8.1-Style Queues

Privilege	8.1.x-Style Queues in a 8.1.x Database or Higher
AQ_USER_ROLE	Not supported. Equivalent privileges: execute right on dbms_aq enqueue any queue system privilege dequeue any queue system privilege execute right on dbms_transform
AQ_ADMINISTRATOR_ROLE	Supported.
Execute right on DBMS_AQ	Execute right on DBMS_AQ should be granted to all AQ users. To enqueue/dequeue on 8.1-compatible queues, the user needs the following privileges: execute right on DBMS_AQ enqueue/dequeue privileges on target queues, or ENQUEUE ANY QUEUE/DEQUEUE ANY QUEUE system privileges

Privileges and Access Control

You can grant or revoke privileges at the object level on 8.1- style queues. You can also grant or revoke various system-level privileges. The following table lists all common AQ operations and the privileges need to perform these operations for an Oracle9i or 8.1-compatible queue:

Table 4-2 Operations and Required Privileges

Operation(s)	Privileges Required
CREATE/DROP/MONITOR own queues	Must be granted execute rights on DBMS_AQADM. No other privileges needed.
CREATE/DROP/MONITOR any queues	Must be granted execute rights on DBMS_AQADM and be granted AQ_ADMINISTRATOR_ROLE by another user who has been granted this role (SYS and SYSTEM are the first granters of AQ_ADMINISTRATOR_ROLE)
ENQUEUE/ DEQUEUE to own queues	Must be granted execute rights on DBMS_AQ. No other privileges needed.
ENQUEUE/ DEQUEUE to another's queues	Must be granted execute rights on DBMS_AQ and be granted privileges by the owner using DBMS_AQADM.GRANT_QUEUE_PRIVILEGE.
ENQUEUE/ DEQUEUE to any queues	Must be granted execute rights on DBMS_AQ and be granted ENQUEUE ANY QUEUE or DEQUEUE ANY QUEUE system privileges by an AQ administrator using DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE.

OCI Applications

For an OCI application to access an 8.1-style queue, the session user has to be granted either the object privilege of the queue he intends to access or the ENQUEUE ANY QUEUE and/or DEQUEUE ANY QUEUE system privileges. The EXECUTE right of DBMS_AQ will not be checked against the session user's rights if the queue he intends to access is anOracle9i or 8.1-compatible queue.

Security Required for Propagation

AQ propagates messages through database links. The propagation driver dequeues from the source queue as owner of the source queue; hence, no explicit access rights have to be granted on the source queue. At the destination, the login user in the database link should either be granted ENQUEUE ANY QUEUE privilege or be granted the rights to enqueue to the destination queue. However, if the login user in the database link also owns the queue tables at the destination, no explicit AQ privileges need to be granted.