Security

Configuration information can be managed through procedures in the DBMS_AQADM package. Initially, only SYS and SYSTEM have the execution privilege for the procedures in DBMS_AQADM and DBMS_AQ. Any users who have been granted the EXECUTE rights to these two packages will be able to create, manage, and use queues in their own schema. The user would also need the MANAGE ANY QUEUE privilege in order to create and manage queues in other schemas.

Security with 8.0 and 8.1 Compatible Queues

AQ administrators of an 8.1 database are allowed to create queues with 8.0 or 8.1 compatibility. All 8.1 security features are enabled for 8.1 compatible queues. However, please note that AQ 8.1 security features work only with 8.1 compatible queues; 8.0 compatible queues are protected by the 8.0 compatible security features.

To create queues in 8.1 that can make use of the new security features, the compatible parameter in DBMS_AQADM.CREATE_QUEUE_TABLE must be set to '8.1' or above. If you want to use the new security features on a queue originally created in an 8.0 database, the queue table must be converted to 8.1 compatibility by running DBMS_AQADM.MIGRATE_QUEUE_TABLE on the queue table.

If a database downgrade is necessary, all 8.1 compatible queue tables have to be either converted back to 8.0 compatibility or dropped before the database downgrade can be carried out. During the conversion, all 8.1 security features on the queues, like the object privileges, will be dropped. When a queue is converted to 8.0 compatibility, the 8.0 security model apply to the queue, and only 8.0 security features are supported.

The following table lists the AQ security features supported in each version of Oracle8 database and their equivalence privileges across different database version.

Table 4-1 Security with 8.0- and 8.1-Compatible Queues

Privilege	8.0.x Database	8.0.x Compatible Queues in a 8.1.x Database	8.1.x Compatible Queues in a 8.1.x Database
AQ_USER_ROLE	Supported. The grantee is given the execute right of DBMS_AQ through the role.	Supported. The grantee is given the execute right of dbms_aq through the role.	Not supported. Equivalent privileges: execute right on dbms_aq enqueue any queue system privilege dequeue any queue system privilege
AQ_ADMINISTRATOR_ROLE	Supported.	Supported.	Supported.
Execute right on DBMS_AQ	Execute right on DBMS_AQ should be granted to developers who write AQ applications in PL/SQL.	Execute right on DBMS_AQ should be granted to developers who write AQ applications in PL/SQL.	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 either enqueue/dequeue privileges on target queues, or ENQUEUE ANY QUEUE/DEQUEUE ANY QUEUE system privileges

Privileges and Access Control

With Oracle 8.1, you can grant or revoke privileges at the object level on 8.1 compatible 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 8.1-compatible queue:

Table 4-2 Operations and Required Privileges in the 8.1 Security Model

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.

Roles

Access to AQ operations in Oracle 8.0 is granted to users through roles which provide execution privileges on the AQ procedures. The fact that there is no control at the database object level when using Oracle 8.0 means that in Oracle 8.0 a user with the AQ_USER_ROLE can enqueue and dequeue to any queue in the system. Since Oracle 8.1 offers a finer-grained access control, the function of roles changes when you develop applications in the 8.1 context.

Administrator Role

Oracle 8.1 continues to support the AQ_AQMISTRATOR_ROLE. As in 8.0, the AQ_ADMINISTRATOR_ROLE has been granted all the required privileges to administer queues. The privileges granted to the role let the grantee:

• perform any queue administrative operation, including create queues and queue tables on any schema in the database

• perform enqueue and dequeue operations on any queues in the database

• access statistics views used for monitoring the queues' workload

User Role

AQ_USER_ROLE continues to work for queues that are created with 8.0 compatibility. However, you should avoid granting AQ_USER_ROLE in Oracle 8.1 since this role will not provide sufficient privileges for enqueuing or dequeuing on 8.1 compatible queues.

Your database administrator has the option of granting the system privileges ENQUEUE ANY QUEUE and DEQUEUE ANY QUEUE, exercising DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE and DBMS_AQADM.REVOKE_SYSTEM_PRIVILEGE directly to a database user, provided that you wish the user to have this level of control. You as the application developer give rights to a queue by granting and revoking privileges at the object level by exercising DBMS_AQADM.GRANT_QUEUE_PRIVILEGE and DBMS_AQADM.REVOKE_QUEUE_PRIVILEGE.

As a database user you do not need any explicit object level or system level privileges to enqueue or dequeue to queues in your own schema other than the execute right on DBMS_AQ.

Access to AQ Object Types

The procedure grant_type_access is made obsolete in release 8.1.5 for both 8.0-compatible and 8.1 compatible queues. All internal AQ objects are now accessible to PUBLIC.

OCI Applications

For an OCI application to access an 8.0-compatible queue, the session user has to be granted the EXECUTE rights of DBMS_AQ. For an OCI application to access an 8.1-compatible 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 an 8.1-compatible queue.

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 either.

Purpose:

To upgrade a 8.0-compatible queue table to an 8.1-compatible queue table or to downgrade a 8.1-compatible queue table to an 8.0-compatible queue table.

Syntax:

DBMS_AQADM.MIGRATE_QUEUE_TABLE( 
    queue_table        IN       VARCHAR2, 
    compatible         IN       VARCHAR2) 

Usage:

Table 4-3 DBMS_AQADM_MIGRATE_QUEUE_TABLE

Parameter	Description
queue_table (IN VARCHAR2)	Specifies name of the queue table that is to be migrated.
compatible	Set to '8.1' to upgrade an 8.0 queue table to 8.1 compatible. Set to '8.0' to downgrade an 8.1 queue table to 8.0 compatible.

Usage Notes

For the most current information regarding the interrelationship of different releases, please refer to "Compatibility" in Chapter 4, "Managing AQ".

Example: To Upgrade An 8.0 Queue Table To A 8.1-Compatible Queue Table

Note: You may need to set up the following data structures for certain examples to work: EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE ( queue_table => 'qtable1', multiple_consumers => TRUE, queue_payload_type => 'aq.message_typ', compatible =>'8.0');

EXECUTE DBMS_AQADM.MIGRATE_QUEUE_TABLE(
    queue_table  => 'qtable1',                                                                           
   compatible   => '8.1');