Set and view audit rules

After enabling security auditing, you need to set specific security audit rules to audit user operations.

Set audit rules

Log in to the Oracle tenant as the ORAAUDITOR user. You can use the built-in audit management user ORAAUDITOR to configure audit rules, including:

• Statement audit: Audits specific operations without specifying specific objects. You can specify that it takes effect for specific users or all users.
• Object audit: Audits specific operations performed on specific objects. You can specify that it takes effect for specific users or all users.

Audit rules are configured using the DDL statement audit/noaudit. An audit rule is also a schema object.

For more information about setting audit rules, see AUDIT.

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

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

View audit rules

After setting audit rules, you can view the audit rule information through a series of Oracle-compatible data dictionary views.

View the audit rules in ALL_DEF_AUDIT_OPTS. Example:

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, making it easy to combine with strategies such as compression, rotation, and cleanup. Refer to the steps below 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: Disable encryption. This is the default value.
• AES: Enable encryption; the algorithm is AES-256-CBC stream encryption (stream encryption during archiving write).

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

obclient> ALTER SYSTEM SET audit_log_encryption='AES';

For more information about the parameters, see the relevant documentation at the end of this topic.

Log archiving and password generation

You can call the following functions to archive logs and generate passwords. These functions must be used as output columns in a SELECT statement (they cannot be used in subqueries) and are not applicable to the AUDIT or NOAUDIT syntax:

• AUDIT_LOG_ENCRYPTION_PASSWORD_SET(): This function takes no parameters. It 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 about this function, see the relevant documentation at the end of this topic.

Here is an example:

obclient> SELECT AUDIT_LOG_ENCRYPTION_PASSWORD_SET() FROM DUAL;

Log password retrieval

You can 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 an integer that indicates the key identifier after the .enc. segment in the encrypted archive file name, such as 1001. If this parameter is omitted, the password corresponding to the latest pwd_id is returned. For more information about this function, see the relevant documentation 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 the interfaces provided by OpenSSL. To encrypt or decrypt log files with OpenSSL, you must specify a key and IV, or a password. Here is an example command:

# -d indicates decryption.
# -aes-256-cbc indicates that the AES-256-CBC algorithm is used.
# -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 indicates that the input is an encrypted log file.
# -out decrypted_file specifies the output decrypted log file.
openssl enc -d -aes-256-cbc -pass pass:password -md sha256 -in encrypted_log_file -out decrypted_file

Appendix: Log file name format

The name of an archived audit log file consists of a timestamp suffix, followed by a segment indicating whether compression or encryption is enabled (pwd_id serves as the key identifier, corresponding to view and function parameters).

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

References

For more operations related to security audit, see:

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