Ensuring the security and integrity of databases is paramount in today’s data-driven world. Oracle 19c offers robust tools for auditing users in Container Databases (CDBs) and Pluggable Databases (PDBs). This article delves into user audit techniques and strategies for effectively auditing users in Oracle databases, ensuring that your database environment remains secure and compliant.
About Auditing in a Multitenant Environment
Auditing tracks changes that users make in the multitenant container database (CDB). You can use unified auditing in a multitenant environment. Unified auditing consolidates all audit logs into a single repository, simplifying audit management and providing a comprehensive view of all user activities across CDBs and PDBs.
In a multitenant environment, each PDB, including the root, has its own unified audit trail. Audit settings can be applied to individual PDBs or to the CDB, depending on the type of policy. Here are the main types of audit policies:
Unified Audit Policies: Created with the CREATE AUDIT POLICY and AUDIT statements. You can create policies for both the root and individual PDBs.
Fine-Grained Audit Policies: Created for individual PDBs only, not the root.
Traditional Auditing: Uses the AUDIT and NOAUDIT statements to audit statements and privileges in a multitenant environment.
Techniques for Auditing Users
Unified Auditing
Unified auditing is the default auditing mechanism in Oracle 19c. It simplifies the management of audit records by storing all audit data in a single location.
How to enable unified auditing:
ALTER SYSTEM SET AUDIT_TRAIL=DB, EXTENDED SCOPE=SPFILE;
Fine-Grained Auditing (FGA)
Fine-Grained Auditing (FGA) allows administrators to create policies that audit specific columns and conditions within a table. This targeted approach ensures that only relevant user activities are logged.
Example of setting up FGA:
BEGIN
DBMS_FGA.ADD_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'SALARY_AUDIT',
audit_column => 'SALARY',
audit_condition => 'SALARY > 10000',
audit_column_opts => DBMS_FGA.ANY_COLUMNS
);
END;
Standard Auditing
Standard auditing involves creating audit policies that capture user actions such as logins, DML operations, and schema modifications.
How to enable standard auditing for users actions:
AUDIT CREATE SESSION, ALTER, DELETE ON HR.EMPLOYEES BY HR;
📢 You might also like: Oracle 19c Manage Other Types of Policies in Application Containers (Category: Oracle Database Admin)
Configuring Audit Policies
Create an Audit Policy: Define the actions and objects to be audited.
Example: To audit all select operations on the HR schema:
CREATE AUDIT POLICY hr_select_audit
ACTIONS SELECT ON HR.EMPLOYEES;
Enable the Audit Policy: Apply the policy to the relevant auditing users or roles:
AUDIT POLICY hr_select_audit;
Review Audit Logs: Regularly review the audit logs to identify any suspicious activities.
Query to view audit logs:
SELECT * FROM DBA_AUDIT_TRAIL WHERE OBJECT_SCHEMA = 'HR';
Managing Audit Trails
Centralized Storage: Store audit trails in a centralized repository to simplify management and analysis. Oracle’s Unified Auditing feature for Oracle users supports centralized storage, making it easier to maintain and review audit logs.
Regular Monitoring: Regularly monitor audit trails to detect and respond to any suspicious activities promptly. Set up automated alerts for specific actions or anomalies in the audit logs.
Purging Old Audit Data: To manage storage effectively, regularly purge old audit data that is no longer needed. Ensure compliance with your organization’s data retention policies.
BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp => TRUE
);
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp => TRUE
);
END;
Auditing users with DBA Role in a Multitenant Environment
The CREATE AUDIT POLICY statement can audit roles in a multitenant environment.
Example of auditing the DBA role for DBA or non-DBAs users:
CREATE AUDIT POLICY role_dba_audit_pol
ROLES DBA
CONTAINER = ALL;
AUDIT POLICY role_dba_audit_pol;
Local Unified Auditing Users Policy
The CREATE AUDIT POLICY statement can create a local unified audit policy in either the root or a PDB.
Example of a local unified audit policy:
CONNECT c##sec_admin@hrpdb
Enter password: password
Connected.
CREATE AUDIT POLICY table_privs
PRIVILEGES CREATE ANY TABLE, DROP ANY TABLE
CONTAINER = CURRENT;
AUDIT POLICY table_privs BY c##hr_admin;
CDB Common Unified Audit Policy
Auditing users – The CREATE AUDIT POLICY statement can create a CDB common unified audit policy.
Example of a common unified audit policy:
CONNECT c##sec_admin
Enter password: password
Connected.
CREATE AUDIT POLICY admin_pol
ACTIONS CREATE TABLE, ALTER TABLE, DROP TABLE
ROLES c##hr_mgr, c##hr_sup
CONTAINER = ALL;
AUDIT POLICY admin_pol BY c##hr_admin;
Auditing Users – Application Common Unified Audit Policy
For application container common unified audit policies, you can audit action options and system privilege options, and refer to common objects and roles.
Example of creating an application common unified audit policy:
CONNECT c##sec_admin@app_pdb
Enter password: password
Connected.
CREATE AUDIT POLICY app_pdb_admin_pol
ACTIONS SELECT ON hr_app_cdb.utils_tab, DROP TABLE
PRIVILEGES SELECT ANY TABLE
CONTAINER = ALL;
AUDIT POLICY app_pdb_admin_pol by SYSTEM, c##hr_admin;
Conclusion
Auditing users in Oracle 19c CDBs and PDBs is crucial for maintaining a secure and compliant database environment. By leveraging unified auditing, fine-grained auditing, and standard auditing techniques, database administrators can monitor user activities effectively. Regularly reviewing audit logs and managing audit trails ensures that any suspicious activities are detected and addressed promptly, maintaining the integrity and security of your database systems.
See more on Oracle’s website!
Be Oracle Database Certified Professional, this world is full of opportunities for qualified DBAs!
