Security auditing in Amazon RDS for Oracle: Part 1

Due to compliance requirements and increasing security threats, security auditing has become more important to implement than ever before. Security auditing is an effective method of enforcing strong internal controls that enable you to monitor business operations to find any activities that may deviate from company policy and meet various regulatory compliance requirements. Security auditing allows you to record the activity on the database for future examination and is one part of an overall database security strategy.

In this post, we show you the options available to set up security auditing on Amazon Relational Database Service (Amazon RDS) for Oracle. This is of particular interest to those with a focus on tracking actions on Amazon RDS for Oracle for compliance and regulatory purposes. We highlight different auditing options available for Amazon RDS for Oracle, and explore how to enable those options and manage audit trails. In addition, we explain how to integrate audit trails with AWS native monitoring services like Amazon CloudWatch.

This a two-part series. Part one describes the security auditing options available. Part 2 takes a deep dive into Database Activity Streams (DAS) for Amazon RDS for Oracle. DAS provides a near-real-time stream of all audited statements (SELECT, DML, DDL, DCL, and TCL) run in your DB instance.

The relevance of Oracle database security auditing

Enterprise customers may be required to audit their IT operations for several reasons. Predominantly, it’s for the purpose of satisfying regulatory requirements or demonstrating compliance with the following:

• Industry standards and frameworks, such as PCI, SOX, HIPAA, or MIST 800-53
• Regulations specific to EU, Japan Privacy Law, International Convergence of Capital Measurement, and Capital Standards: A Revised Framework (Basel II)
• Country-specific or regional data privacy laws

Alternatively, auditing may be performed within an organization or department for the purpose of troubleshooting or process improvement.

Auditing options with Oracle and Amazon RDS for Oracle

The following diagram shows the auditing options that are currently available on Amazon RDS for Oracle.

We don’t go into extensive detail on each auditing method because these are covered comprehensively in the Oracle documentation, Monitoring Database Activity with Auditing. We provide a high-level overview of the purposes and best practices associated with the different auditing options.

alert.log and listener.log

These Oracle-native files are available out the box and provide a chronological listing of events on the Oracle database. The alert.log lists database errors and messages including administrative events like STARTUP and SHUTDOWN. The listener.log provides a chronological listing of network events on the database, such as connections. For example, if you want to establish where connections have come from (such as IP address, OS user, or database user), these files are very useful and make up the base of any auditing strategy. You can configure Amazon RDS for Oracle to publish alert.log and listener.log to CloudWatch Logs for longer retention and analysis. For more information about various logs in Amazon RDS for Oracle, see Oracle database log files.

Standard auditing

Standard (traditional) auditing is an Oracle-native feature that has been around since Oracle 7. It’s available in all versions with Standard and Enterprise Editions. You can use standard auditing to audit SQL statements, privileges, schemas, objects, and network and multi-tier activity. Oracle recommends using standard auditing on versions prior to Oracle Database 12c release 1 (12.1). Standard auditing can be difficult to manage because you end up having more than one audit trail and different parameters to control the auditing behavior with lack of granular auditing options.

Unified auditing

Oracle Database 12c release 1 (12.1) released new auditing features with the introduction of unified auditing. This is the strategic Oracle Database audit framework and should be used to audit activity in Oracle Database 12.1 or greater. Oracle unified auditing changes the fundamental auditing functionality of the database. Previous releases of Oracle had separate audit trails for each individual component. Unified auditing consolidates all auditing into a single repository and view. Audit data can now be found in a single location, and all audit data is in a single format. Unified auditing comes with Oracle Enterprise and Standard Edition 2. It’s installed by default and includes the following features:

• A common audit trail for all types of audit information
• Flexible and granular auditing options to control audit data and more auditing features
• Separation of duties for audit administration
• Integration with Database Activity Streams (supported from 19.0.0.0.ru-2019-07.rur-2019-07.r1)

You can configure unified auditing in mixed mode or pure mode. Mixed mode, which is the default unified auditing mode, is intended to introduce unified auditing features and provide a transition from standard auditing. With mixed mode unified auditing, you can use features of both standard auditing and unified auditing. Pure mode unified auditing requires database binaries to be relinked with the uniaud_on option, and therefore isn’t supported in Amazon RDS for Oracle.

Amazon RDS for Oracle supports unified auditing in mixed mode and it’s enabled by default. For more information, see Mixed Mode Auditing.

The following diagram illustrates the differences between the two modes:

Fine-grained auditing

Oracle fine-grained auditing is an Enterprise Edition feature that enables you to create customized audit policies that you can use to create audit records focusing on sensitive columns. Fine-grained auditing complements unified auditing by enabling audit conditions to be associated with specific columns. You can create policies that define specific conditions that must be met in order for an audit to occur. It provides a more granular audit of queries, INSERT, UPDATE, and DELETE operations.

The following are few use cases where you may want to consider using fine-grained auditing in addition to standard or unified auditing:

• Auditing specific columns – You can audit specific relevant columns that hold sensitive information, such as salaries or Social Security numbers. For instance, you can create policies to create audit records when a sensitive column is accessed outside office hours or from outside the corporate network, or when a sensitive column is modified with a value outside a predetermined range.
• Using event handlers – You can write a function that sends an email alert to a security administrator when an audited column that shouldn’t be changed at a certain time is updated.

AWS CloudTrail

AWS CloudTrail helps you audit your AWS account. All Amazon RDS API calls are logged by CloudTrail. It provides a record of actions taken by a user, role, or another AWS service in an RDS for Oracle instance. CloudTrail captures API calls for Amazon RDS for Oracle as events. You can use the CloudTrail console to view the last 90 days of recorded API activity and events in a Region. CloudTrail doesn’t log any access or actions at the database level. For example, it doesn’t track SQL commands.

Standard auditing with Amazon RDS for Oracle

Standard database auditing provides robust audit support in both the Enterprise Edition and Standard Edition 2 of Amazon RDS for Oracle. Traditional database auditing is available in all versions of Amazon RDS for Oracle, but it’s recommended to use unified auditing in Oracle versions above Oracle Database 12c release 1 (12.1). With standard auditing, audit records can be stored in the database audit trail or in operating system files of the instance hosting Amazon RDS for Oracle instance. Standard auditing includes operations on privileges, schemas, objects, and statements. Oracle recommends that the audit trail be written to the operating system files because this configuration imposes the least amount of overhead on the source database system. Amazon RDS for Oracle also supports integration of audit files with CloudWatch Logs when audit records are stored at the operating system level.

AUDIT_TRAIL is set to NONE in the default parameter group. To enable auditing in Amazon RDS for Oracle, you need to set the parameter to one of the values in the following table by creating a custom parameter group and changing the parameter value for that custom parameter group. Because AUDIT_TRAIL is a static parameter, changes made to it are reflected only after a reboot of the instance.

After the AUDIT TRAIL parameter is on, you run an AUDIT SQL command to enable the auditing of a particular type of SQL statement. For more information about the AUDIT statement to enable audits for different type of actions, see AUDIT (Traditional Auditing).

You can use the optional parameter AUDIT_SYS_OPERATIONS to enable or disable auditing of directly issued user SQL statements with SYS authorization. These include SQL statements directly issued by users when connected with the SYSASM, SYSBACKUP, SYSDBA, SYSDG, SYSKM, or SYSOPER privileges. This parameter defaults to TRUE.

Regardless of whether database auditing is enabled, Oracle Database always audits certain database-related operations and writes them to the operating system audit file regardless of AUDIT_TRAIL setting. This mandatory auditing includes operations like internal connection to the RDS for Oracle instance with SYSDBA/SYSOPER/SYSBACKUP type of privileges, database startup, and database shutdown.

Unified auditing with Amazon RDS for Oracle

Oracle Database 12c release 2 (12.2) unified auditing is recommended for both Enterprise and Standard Edition 2. It provides a centralized view of audit activities. It can audit events like Data Pump and RMAN operations via policy-based rules. You can implement policies to meet complex audit requirements. Audit policies can have conditions and exclusions for more granular control than traditional auditing.

For example, to audit the DML access to sensitive salary data by anyone other than the designated personnel, use the following code:

CREATE AUDIT POLICY UNAUTH_PAYROLL       
ACTIONS
INSERT ON ACCOUNT.SALARY, UPDATE ON ACCOUNT.SALARY, DELETE ON ACCOUNT.SALARY
WHEN 'SYS_CONTEXT(''APP_CONTEXT'', ''APP_USER'') IN (''PAYCLERK_USER'')' EVALUATE PER STATEMENT
ONLY TOPLEVEL;

AUDIT POLICY UNAUTH_PAYROLL EXCEPT BOSS;

For more examples of unified auditing, see CREATE AUDIT POLICY (Unified Auditing). These examples give you an idea of the possibilities that you can implement.

Unified auditing provides a new schema, AUDSYS, which owns the unified audit objects. In addition, from 12.2 onwards, unified auditing writes to its own memory area. By default, unified auditing has two audit policies enabled:

	POLICY_NAME          ENABLED_OP ENTITY_NAME     ENTITY_TYP SUCCESS   FAILURE     
-------------------- ---------- --------------- ---------- --------- ---------   
	ORA_SECURECONFIG     BY USER    ALL USERS       USER       YES       YES    
 	ORA_LOGON_FAILURES   BY USER    ALL USERS       USER       NO        YES    

The ORA_LOGON_FAILURES unified audit policy tracks failed logons only, but not any other kinds of logons. The ORA_SECURECONFIG unified audit policy provides all the secure configuration audit options. This includes the following audit events:

SQL> l
  1  select distinct
  2  policy_name,
  3  audit_option
  4  from
  5  AUDIT_UNIFIED_POLICIES
  6  having policy_name in ('ORA_LOGON_FAILURES','ORA_SECURECONFIG') group by
  7  policy_name,audit_option
  8* order by audit_option
SQL> /

POLICY_NAME          AUDIT_OPTION                                               
-------------------- -----------------------------------                        
ORA_SECURECONFIG     ADMINISTER KEY MANAGEMENT                                  
ORA_SECURECONFIG     ALTER ANY PROCEDURE                                        
ORA_SECURECONFIG     ALTER ANY SQL TRANSLATION PROFILE                          
ORA_SECURECONFIG     ALTER ANY TABLE                                            
ORA_SECURECONFIG     ALTER DATABASE                                             
ORA_SECURECONFIG     ALTER DATABASE DICTIONARY                                  
ORA_SECURECONFIG     ALTER DATABASE LINK                                        
ORA_SECURECONFIG     ALTER PLUGGABLE DATABASE                                   
ORA_SECURECONFIG     ALTER PROFILE                                              
ORA_SECURECONFIG     ALTER ROLE                                                 
ORA_SECURECONFIG     ALTER SYSTEM                                               
ORA_SECURECONFIG     ALTER USER                                                 
ORA_SECURECONFIG     AUDIT SYSTEM                                               
ORA_SECURECONFIG     BECOME USER                                                
ORA_SECURECONFIG     CREATE ANY JOB                                             
ORA_SECURECONFIG     CREATE ANY LIBRARY                                         
ORA_SECURECONFIG     CREATE ANY PROCEDURE                                       
ORA_SECURECONFIG     CREATE ANY SQL TRANSLATION PROFILE                         
ORA_SECURECONFIG     CREATE ANY TABLE                                           
ORA_SECURECONFIG     CREATE DATABASE LINK                                       
ORA_SECURECONFIG     CREATE DIRECTORY                                           
ORA_SECURECONFIG     CREATE EXTERNAL JOB                                        
ORA_SECURECONFIG     CREATE PLUGGABLE DATABASE                                  
ORA_SECURECONFIG     CREATE PROFILE                                             
ORA_SECURECONFIG     CREATE PUBLIC SYNONYM                                      
ORA_SECURECONFIG     CREATE ROLE                                                
ORA_SECURECONFIG     CREATE SQL TRANSLATION PROFILE                             
ORA_SECURECONFIG     CREATE USER                                                
ORA_SECURECONFIG     DROP ANY PROCEDURE                                         
ORA_SECURECONFIG     DROP ANY SQL TRANSLATION PROFILE                           
ORA_SECURECONFIG     DROP ANY TABLE                                             
ORA_SECURECONFIG     DROP DATABASE LINK                                         
ORA_SECURECONFIG     DROP DIRECTORY                                             
ORA_SECURECONFIG     DROP PLUGGABLE DATABASE                                    
ORA_SECURECONFIG     DROP PROFILE                                               
ORA_SECURECONFIG     DROP PUBLIC SYNONYM                                        
ORA_SECURECONFIG     DROP ROLE                                                  
ORA_SECURECONFIG     DROP USER                                                  
ORA_SECURECONFIG     EXECUTE                                                    
ORA_SECURECONFIG     EXEMPT ACCESS POLICY                                       
ORA_SECURECONFIG     EXEMPT REDACTION POLICY                                    
ORA_SECURECONFIG     GRANT ANY OBJECT PRIVILEGE                                 
ORA_SECURECONFIG     GRANT ANY PRIVILEGE                                        
ORA_SECURECONFIG     GRANT ANY ROLE                                             
ORA_SECURECONFIG     LOGMINING                                                  
ORA_LOGON_FAILURES   LOGON                                                      
ORA_SECURECONFIG     PURGE DBA_RECYCLEBIN                                       
ORA_SECURECONFIG     SET ROLE                                                   
ORA_SECURECONFIG     TRANSLATE ANY SQL                                          

49 rows selected.

SQL> spool off

The preceding defaults are a comprehensive starting point. You can also configure other out-of-the-box audit policies or your own custom audit policies. The best practice is to limit the number of enabled policies. With more policies, you can have an impact on User Global Area (UGA), which is the memory associated with a user session. Plan your auditing strategy carefully to limit the number of audited events as much as possible. Make sure security isn’t compromised because of this. Minimizing the performance impact on the running of statements audited also minimizes the size of the audit trail. An effective auditing approach should consider tracking creation and altering of database users, database management events (for example, issuing of DDL commands and use of database management tools) and access to sensitive data. For more information, refer to Oracle Database Unified Audit: Best Practice Guidelines.

Enable unified auditing on Amazon RDS for Oracle

To enable unified auditing in mixed mode, you need to change the AUDIT_TRAIL parameter to DB in the parameter group. The default on Amazon RDS for Oracle 19.12 is AUDIT_TRAIL = NONE. With AUDIT_TRAIL = NONE, you don’t use unified auditing. You can enable unified auditing by setting AUDIT_TRAIL=DB or (DB,EXTENDED).

As the Oracle database security guide explains, “as in previous releases, the traditional audit facility is driven by the AUDIT_TRAIL initialization parameter. Only for mixed mode auditing, you should set this parameter to the appropriate traditional audit trail. This traditional audit trail will then be populated with audit records, along with the unified audit trail.”

After you set AUDIT_TRAIL, audit events in the policies ORA_SECURECONFIG and ORA_LOGON_FAILURES are picked up. The following example shows how a CREATE and DROP user is audited:

SQL> show user 
USER is "DBMASTER" 
SQL> create user auditmaster identified by auditmaster; 
 User created. 
 
SQL> drop user auditmaster; 
 User dropped. 
 
SQL> show parameter audit_trail 
 
NAME TYPE VALUE 
------------------------------------ ----------- ------------------------------
 audit_trail string DB  
 
SQL> @audit 
 
	EVENT_TIMESTAMP DBUSERNAME ACTION_NAME OBJECT_SCH OBJECT_NAME 
------------------------------ ---------- -------------------- ---------- ------------
20-JUL-21 05.30.30.190873 PM DBMASTER CREATE USER AUDITMASTER 20-JUL-21 05.30.56.321507 PM DBMASTER DROP USER AUDITMASTER 
 
SQL> l 1 SELECT event_timestamp, 
dbusername, 
action_name, 
object_schema, 
object_name 
FROM unified_audit_trail 
WHERE object_name = 'AUDITMASTER' 
8* ORDER BY event_timestamp 

In Oracle Database 12c release 1 (12.1), we had the option of queuing the audit records in memory (queued-write mode), which are written periodically to the AUDSYS schema audit table.

However, starting with Oracle Database 12c release 2 (12.2), the queued-write mode is deprecated and the unified audit records are written immediately to disk to a table in the AUDSYS schema called AUD$UNIFIED.

You might have upgraded your database to 19c only to realize both traditional auditing (from your old auditing setup) and now unified auditing are active. You should determine which auditing method to use, with caution that running traditional and unified auditing at the same time should be avoided. So you need to remove standard auditing by issuing NOAUDIT commands. For more information on NOAUDIT, see How the AUDIT and NOAUDIT SQL Statements Work.

Fine-grained auditing with Amazon RDS for Oracle

Fine-grained auditing is an Enterprise Edition feature that enables you to create audit policies that define more granular conditions to be met in order for an audit record to be created.

With Amazon RDS for Oracle, which supports unified auditing in mixed mode and standard auditing, the audit trail for fine-grained audit records is controlled by the AUDIT_TRAIL parameter of the DBMS_FGA.ADD_POLICY procedure, which can be set independently of the AUDIT_TRAIL instance parameter.

The following table shows the location of a fine-grained audit trail for different settings.

The following example code creates a fine-grained auditing policy that enables auditing only when the sensitive column SALARY is accessed by any INSERT, UPDATE, SELECT, or DELETE statements with AUDIT_TRAIL as SYS.FGA_LOG$:

BEGIN 
  DBMS_FGA.ADD_POLICY( 
   object_schema      => 'HR', 
   object_name        => 'EMPLOYEES', 
   policy_name        => 'chk_hr_employees', 
   audit_column       => 'SALARY', 
   enable             =>  TRUE,
   audit_trail        => DBMS_FGA.DB,    
   statement_types    => 'INSERT, UPDATE, SELECT, DELETE'); 
END; 
/

For more methods to enable fine-grained auditing, see Auditing Specific Activities with Fine-Grained Auditing.

You can query DBA_AUDIT_POLICIES to list fine-grained auditing policies created in the database. The following query shows the contents of fine-grained audit trail for the query select salary from table hr.employees:

select salary from hr.employees:

	SQL> select OBJECT_NAME,POLICY_NAME,SQL_TEXT from dba_fga_audit_trail; 
 
OBJECT_NAME POLICY_NAME SQL_TEXT 
------------------------------ ------------------------------ ------------------------
EMPLOYEES CHK_HR_EMPLOYEES select salary from hr.employees

Managing audit trails

It’s important to properly manage audit trails on your databases to ensure efficient performance and optimum use of disk space. As audit trails on your databases grow in volume, querying an audit trail with a large volume of audit data may impact performance and lead to space scalability issues. It’s best to archive the old records and purge them from the online audit trail periodically.

Relocate audit trail segments to a dedicated tablespace

If your audit policies generate lot of audit data, it’s better to designate a different tablespace for the audit trail by using the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION procedure.

To move the unified audit trail to the new tablespace AUDITTS, use the following code:

create tablespace AUDITTS datafile size 10G;
exec dbms_audit_mgmt.set_audit_trail_location(audit_trail_type=>
DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,audit_trail_location_value=>'AUDITTS');

Changing the tablespace for audit_trail objects only takes effect for new partitions. The existing partitions remain in the old tablespace (SYSAUX).

To move the audit trail for both standard auditing and fine-grained auditing to the new tablespace AUDITTS, use the following code:

create tablespace AUDITTS datafile size 10G;
exec dbms_audit_mgmt.set_audit_trail_location(audit_trail_type=>
DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,audit_trail_location_value=>'AUDITTS');

For the audit_trail_type values AUDIT_TRAIL_AUD_STD, AUDIT_TRAIL_FGA_STD, and AUDIT_TRAIL_DB_STD, this can be a resource-intensive operation, especially if your database audit trail tables are already populated. We recommend invoking the procedure during non-peak hours.

Set a unified audit trail partition interval and use EVENT_TIMESTAMP_UTC for queries

Setting an appropriate partition strategy can help in both reading audit records and purging old records.

The AUDSYS.AUD$UNIFIED table is interval partitioned based on the EVENT_TIMESTAMP_UTC column, with a partition interval of 1 month until version 19c and 1 day for versions above 19c. You may consider changing the interval based on the volume of data in the ONLINE audit repository.

AUDSYS.AUD$UNIFIED is a partitioned table in Enterprise and Standard Edition 2; you can change the partition interval for this internal table used for unified auditing in both editions. See the following code:

BEGIN 
DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL(interval_number => 7,interval_frequency => 'DAY');
END; 
/

The next partition is created only after the current or active partition’s HIGH_VALUE is reached in the AUDSYS.AUD$UNIFIED table. Therefore, it might take a while for the newer partition to appear.

Oracle recommends that when you query the UNIFIED_AUDIT_TRAIL view to include the EVENT_TIMESTAMP_UTC column in the WHERE clause to achieve partitioning pruning.

Archive audit records and purge the unified audit trail

To maintain the integrity and reliability of audit data, we recommend keeping only minimal required audit data locally and moving audit data to a dedicated repository outside of the source database, such as Amazon Simple Storage Service (Amazon S3) or CloudWatch Logs, for long-term audit data retention and detailed analysis. We discuss this in more detail in the following section.

You can purge a subset of audit trail records or create a purge job that performs cleanup at a specified time interval. The DBMS_AUDIT_MGMT package provides utilities to set the archive timestamp, purge the audit trail, and schedule a purge job.

The following code purges the unified audit trail based on an archival timestamp you set:

BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
AUDIT_TRAIL_TYPE=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
LAST_ARCHIVE_TIME=>TO_TIMESTAMP('06-NOV-2021:22:00:10','DD-MON-YYYY:HH24:MI:SS'));
END;
/

BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
   USE_LAST_ARCH_TIMESTAMP    =>  TRUE,
   CONTAINER                  =>  DBMS_AUDIT_MGMT.CONTAINER_CURRENT );
END;
/

The following code creates a job that’s invoked every 100 hours to purge all types of audit trails in the database:

BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,audit_trail_purge_interval => 100 /* hours */, audit_trail_purge_name => 'CLEANUP',
use_last_arch_timestamp => TRUE);
END;
/

If you’re using a read replica for your RDS for Oracle instance, unified audit records generated on the replica are written to OS .bin files, which need to be purged separately.

If you enabled Database Activity Streams for Amazon RDS for Oracle, then trail management is handled by this feature. Enabling DAS revokes access to purge the unified audit trail.

For more details on the procedures used for audit trail management, see Summary of DBMS_AUDIT_MGMT Subprograms.

Database activity monitoring using database auditing and AWS services

Database activity monitoring (DAM) refers to a suite of tools that you can use to support the ability to identify and report on fraudulent, illegal, or other undesirable behavior, with minimal impact on user operations and productivity. In this section, we review how to integrate audit information with various AWS services and third-party tools for storing audit records for longer retention and for analyzing security threats. AWS Marketplace offers several database activity monitoring solutions, such as Imperva SecureSphere, IBM Guardium Data Protection, DataSunrise Database & Data Security, and Database Activity Monitor (DAM) for AWS.

The following diagram shows the options for database activity monitoring with database auditing.

Integrate standard auditing with CloudWatch

Amazon RDS for Oracle generates audit records that are stored as .aud or .xml operating system files in the RDS for Oracle instance when standard auditing is enabled with the audit_trail parameter set to OS/XML/(XML,EXTENDED). These audit files are typically retained in the RDS for Oracle instance for 7 days.

You can configure Amazon RDS for Oracle to publish these OS audit log files to CloudWatch, where you can perform real-time analysis of the log data, store the data in highly durable storage, and manage the data with the CloudWatch Logs agent. AWS retains log data published to CloudWatch Logs for an indefinite time period in your account unless you specify a retention period.

Publishing your logs allows you to build richer and more seamless interactions with your DB instance logs using AWS services. You can also monitor your logs in near-real time for specific phrases, values, or patterns (metrics). In addition, CloudWatch Logs also integrates with a variety of other AWS services. This integration means you can expand the value of published logs over a variety of use cases, such as the following:

• Setting alarms on abnormal conditions, such as unusually high connection attempts
• Correlating logs with other application logs
• Retaining logs for specific security and compliance purposes
• Analyzing trends in log data over time

You can also export database logs to Amazon S3. When your logs are in Amazon S3, you can configure lifecycle policies to archive the logs and set a retention policy in accordance with your organizational needs. When your logs are in Amazon S3, you can also query logs using Amazon Athena for long-term trend analysis.

To publish Oracle DB logs to CloudWatch Logs, complete the following steps:

1. On the Amazon RDS console, choose Databases in the navigation pane.
2. Choose the DB instance you want to modify.
3. Choose Modify.
4. In the Log exports section, choose the logs that you want to start publishing to CloudWatch Logs.
5. Choose Continue, and then choose Modify DB Instance on the summary page.

This diagram shows Amazon RDS for Oracle integration with CloudWatch Logs.

Fine-grained audit records and standard audit records that you create by setting audit_trail to DB or DB,EXTENDED aren’t published by Amazon RDS for Oracle to CloudWatch Logs. However, audit records created as operating system files in .aud and .xml formats can be published to CloudWatch Logs.

Audit Amazon RDS for Oracle in Multi-AZ and replica environments

With Multi-AZ deployments of Amazon RDS for Oracle, all the auditing features and integrations work transparently across failover operations. However, there are a few considerations for read replicas if you’re using them for disaster recovery protection or for serving read-only workloads:

• Database Activity Streams isn’t supported on replicas. However, if you’re using a replica for disaster recovery purposes and you promote it to a read/write instance, you can enable DAS on it.
• Because your read replica instance is in read-only mode, unified audit records generated on the standby are written to OS .bin files. However, you can still access them using the UNIFIED_AUDIT_TRAIL view.
• Standard audit records are created in OS files in the read replica even if the parameter AUDIT_TRAIL is set to DB. These can be pushed to CloudWatch Logs.
• Fine-grained audit record actions in the read replica are recorded as XML files in OS, which can be pushed to CloudWatch Logs.

Summary

In this post, we showed you various security auditing options and best practices to help support your compliance and regulatory reporting requirements associated with running your database workloads on Amazon RDS for Oracle.

In Part 2 of this series, we take a deeper dive into monitoring Amazon RDS for Oracle using Database Activity Streams. We welcome your comments.

About the Authors

Tom Harper is the Manager of EMEA Relational Databases Specialist Team, based in Manchester, UK. He works in the Enterprise space to support innovation in the database domain. His team helps customers adopt the best migration strategy and design database architectures on AWS with relational managed solutions.

Jobin Joseph is a Senior Database Specialist Solution Architect based in Dubai. With a focus on relational database engines, he assists customers in migrating and modernizing their database workloads to AWS. He is an Oracle Certified Master with 20 years of experience with Oracle databases.