Set and view auditing rules

After you enable security auditing, you must set specific auditing rules to audit user operations.

Set auditing rules

Log in to the Oracle-compatible tenant as the ORAAUDITOR user. You can configure auditing rules by using the built-in auditing management user ORAAUDITOR. Auditing rules can be of the following types:

• Statement auditing: Audits operations performed by specific users or by all users, without specifying particular objects.
• Object auditing: Audits operations performed on specific objects by specific users or by all users.

Auditing rules are configured using the DDL statements AUDIT and NOAUDIT. Each auditing rule is a schema object.

For more information about setting auditing rules, see AUDIT.

For example, to audit all INSERT, UPDATE, and DELETE operations on the tbl1 table owned by user user1:

obclient> AUDIT INSERT,UPDATE,DELETE ON user1.tbl1;

View auditing rules

After you set auditing rules, you can view information about them using a series of Oracle-compatible data dictionary views.

The following example shows how to view auditing rules in the ALL_DEF_AUDIT_OPTS view:

obclient> SELECT * FROM SYS.ALL_DEF_AUDIT_OPTS;

Set audit log encryption (Optional)

Oracle tenants support encrypted storage of audit logs. After enabling encryption, archived log files can be stored on disk with a key identifier, facilitating combined use with strategies such as compression, rotation, and cleanup. You can refer to the steps in the following sections to perform the operations in sequence as needed.

Enable log encryption

The audit_log_encryption parameter controls whether to enable audit log encryption. Valid values include:

• NONE: No encryption is enabled. This is the default value.
• AES: Encryption is enabled. The algorithm is AES-256-CBC stream encryption (stream encryption during archive write).

For example, to enable audit log encryption for the current tenant:

obclient> ALTER SYSTEM SET audit_log_encryption='AES';

For a complete description of the parameters, see the References section at the end of this topic.

Log archiving and password generation

Call the following functions to implement log archiving and password generation. These functions must be called directly as output columns in a SELECT statement (they cannot be used in subqueries) and are not part of the AUDIT/NOAUDIT syntax:

• AUDIT_LOG_ENCRYPTION_PASSWORD_SET(): No parameters. This function triggers the archiving of the currently writing audit log and generates a new encryption key. Subsequent newly generated archive files will be encrypted using this new key. For more information, see the References section at the end of this topic.

Example:

obclient> SELECT AUDIT_LOG_ENCRYPTION_PASSWORD_SET() FROM DUAL;

Log password retrieval

Call the following functions to retrieve the encryption password of a log file:

• AUDIT_LOG_ENCRYPTION_PASSWORD_GET(pwd_id): The pwd_id parameter is of type Int and represents the timestamp suffix of the log file. It is used to return the encryption password of the corresponding archived log file based on its timestamp suffix. If no parameter is provided, the password corresponding to the latest pwd_id is retrieved by default. For more information, see the References section at the end of this topic.

Retrieve the encryption password of the most recent archived log:

obclient> SELECT AUDIT_LOG_ENCRYPTION_PASSWORD_GET() FROM DUAL;

Retrieve the encryption password of a specified log:

obclient> SELECT AUDIT_LOG_ENCRYPTION_PASSWORD_GET('1682089200') FROM DUAL;

Log decryption

Audit log encryption uses interfaces provided by OpenSSL. To encrypt or decrypt log files with OpenSSL, you need to specify a key and IV, or a password. Example command:

# -d indicates decryption
# -aes-256-cbc specifies to use the AES-256-CBC algorithm.
# -pass pass:password specifies to use the password as the password.
# -md sha256 indicates the use of the SHA-256 algorithm.
# -in encrypted_log_file specifies the input encrypted log file.
# -out decrypted_file specifies the output decrypted log file.
obclient > openssl enc -d -aes-256-cbc  -pass pass:password -md sha256 -in encrypted_log_file -out decrypted_file

Appendix: Log file naming convention

The name of an archived audit log file consists of a timestamp suffix, followed by a segment indicating whether compression or encryption is enabled (or both), identified by a pwd_id key (which corresponds to view and function parameters):

Here, timestamp is the timestamp suffix in the archived file name. If compression is enabled, the compression extension depends on the configuration and implementation (currently, when the compression algorithm is ZSTD, it corresponds to .zst).

References

For more information about security auditing, see:

• Enable security auditing
• View auditing records
• Set log encryption: audit_log_encryption
• Log archiving and decryption setting functions: AUDIT_LOG_ENCRYPTION_PASSWORD_SET
• Log archiving and decryption query functions: AUDIT_LOG_ENCRYPTION_PASSWORD_GET
• Overview of audit functions