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.
Parameter Value
Meaning In Amazon RDS for Oracle
Integrated with Amazon CloudWatch logs
DB
Directs all audit records to the database audit trail (sys.aud$), except for records that are always written to the operating system audit trail
No
DB,EXTENDED
Does all the actions of AUDIT_TRAIL=DB and also populates the SQL Bind and SQL text columns of the SYS.AUD$ table
No
XML
Directs all audit records in XML format to an operating system file
Yes
XML,EXTENDED
Does all the actions of AUDIT_TRAIL=XML, adding the SQL Bind and SQL Text columns
Yes
OS
Directs all audit records to an operating system file
Yes
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:
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:
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:
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:
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.
Audit argument of DBMS_FGA.ADD_POLICY
Trail for FGA audit records
Direct Integration with Amazon CloudWatch logs
DB
SYS.FGA_LOG$ (DBA_FGA_AUDIT_TRAIL)
DB,EXTENDED
SYS.FGA_LOG$ with query SQL Text and SQL Bind variable
NO
XML
In XML format to an operating system file
YES
XML,EXTENDED
In XML format to an operating system file with query’s SQL text and SQL Bind variables
YES
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$:
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:
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:
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:
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:
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:
The following code creates a job that’s invoked every 100 hours to purge all types of audit trails in the database:
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:
On the Amazon RDS console, choose Databases in the navigation pane.
Choose the DB instance you want to modify.
Choose Modify.
In the Log exports section, choose the logs that you want to start publishing to CloudWatch Logs.
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.
Read MoreAWS Database Blog
