My Oracle Support Banner

How To Audit Advanced Queueing (AQ) Enqueue/Dequeue In Oracle Database? (Doc ID 2776931.1)

Last updated on MAY 20, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 18.0.0.0 and later
Information in this document applies to any platform.

Goal

How to audit Oracle Advanced Queueing (AQ) enqueue/dequeue in Oracle Database?

Oracle Database can audit when certain statements are performed, when objects are accessed, or when certain system privileges are invoked.  For example, if someone has SELECT ANY TABLE and uses this to select from some other schema, then if you are specifically auditing SELECT ANY TABLE, then an audit record would be created for this.

In the case of Advanced Queueing (AQ), this becomes a function of the system privileges invoked by users who interact with queue tables.  One must identify which system privileges can be invoked in order to enqueue/dequeue, and then specifically audit those privileges.

The AQ documentation mentions the following:

- To enqueue or dequeue, users need EXECUTE rights on DBMS_AQ and either enqueue or dequeue privileges on target queues, or ENQUEUE ANY QUEUE or DEQUEUE ANY QUEUE system privileges.

- For an Oracle Call Interface (OCI) application to access a queue, the session user must be granted either the object privilege of the queue that the user intends to access, or the ENQUEUE ANY QUEUE or DEQUEUE ANY QUEUE system privileges.

- Users who have been granted EXECUTE rights to DBMS_AQADM and DBMS_AQ are able to create, manage, and use queues in their own schemas.  (This is separate from the "ANY" privileges mentioned above, because users do not invoke "ANY" privileges to touch objects belonging to their own schemas.) 

References:

https://docs.oracle.com/en/database/oracle/oracle-database/19/adque/managing-aq.html#GUID-3E0B5CD0-817A-49A3-8895-4651B224D765
https://docs.oracle.com/en/database/oracle/oracle-database/19/adque/managing-aq.html#GUID-8D620EFB-9B2B-4A8D-AEA7-41F0BC5C6859

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.