Home
Guides
How to audit administrative actions in your Oracle RDS and Oracle databases on EC2

How to audit administrative actions in your Oracle RDS and Oracle databases on EC2

How to Configure DataSunrise SSO Authentication Based on SAML (Okta) How to Configure DataSunrise SSO Authentication Based on OpenID (Okta) How to Search for Sensitive Data in Images Hosted on AWS S3 How to Deploy DataSunrise with Terraform Template on Microsoft Azure How to Integrate DataSunrise with SQL Server Always On Cluster How to Deploy DataSunrise in Microsoft Azure Using Azure Resource Manager How to Perform DataSunrise Static Data Masking for MongoDB How to Configure DB Audit Trailing for MS Azure MySQL How to Configure DB Audit Trailing for MS Azure PostgreSQL How to Configure DataSunrise to Mask Data for Amazon Athena How to Upgrade RHEL OS version of existing DataSunrise servers How to Integrate DataSunrise with AWS Database Activity Streams for Getting Auditing Results for AWS Aurora PostgreSQL How to Set Up SSL Certificates for DataSunrise Database Proxy How to Generate Reports in DataSunrise How to Hide Schemas From Users in Redshift How to Configure Reading RDS Postgres Audit Logs in DataSunrise? How to audit administrative actions in your Oracle RDS and Oracle databases on EC2 How to Check if DataSunrise Receives Traffic From the Client Application and Protected Database How To Remove a Procedure or a Function From a Database

Preface

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the AWS Cloud. It provides cost-efficient, resizable capacity for an industry-standard relational database and manages common database administration tasks.

DataSunrise is an AWS Advanced Technology Partner certified on Security competency in Data Protection and Encryption along with other AWS validated qualifications. DataSunrise can run on-premises or an EC2 box or as a cluster on multiple EC2 instances, in a virtual machine or on bare-metal. DataSunrise Data and Database Security Suite (DataSunrise) for all types of Amazon RDS acts as a database application firewall (DAF) acts as a man-in-the-middle for all sessions, queries, and commands from any client to Amazon RDS instance. And since DataSunrise is the software and not a SaaS solution, you are responsible to set up and configure your DataSunrise instance the right way.

The primary goal of this article is to introduce the approach on how to audit privileged accounts activity. We will see how to setup DataSunrise to audit DBA activity in Oracle RDS. However all general steps apply to any Amazon RDS instance.

Overview of Oracle RDS and prerequisites

As you probably know Amazon RDS supports access to databases using any standard SQL client application and does not allow direct host access with SSH etc. This architecture does not allow you to install database agents in Amazon RDS and limits you on using powerful DBA privileges like SYSDBA. Amazon RDS employs a shared responsibility model that excludes direct human intervention to the computing platform. However with Amazon RDS you can perform your tasks slightly different and unified ways. For example DBA can access database logs or backup Amazon RDS instances with snapshots using AWS Management Console, AWS CLI, or RDS API. On the other hand you are not able to access Amazon RDS using SSH or RDP connections for database or OS – related (and probably harmful) activity. So please keep in mind that having the required IAM role you can modify and manage your Amazon RDS instance to meet your system requirements without connecting to Amazon RDS via SSH/RDP.

One important aspect is related to Oracle SYSDBA and similar privileges/roles. SYSDBA role is designated to RDSADMIN user only (AWS uses “rdshm” OS user) and RDSADMIN password is unknown. Also with Oracle RDS you can’t know SYS user password. And all these things mean that:

you can’t connect to your Oracle RDS using RDSADMIN or SYS user;
your database users can not obtain SYSDBA or other Oracle Database powerful role;
you can not connect remotely to Oracle RDS instance using SYSDBA or other powerful role of Oracle Database.

These limited privileges secure and protect every single RDS instance. Oracle RDS creates for you a limited DBA user (e.g “admin” user by default) so you can connect to Oracle RDS instance using this user. Later on you can create another user and this new user will not get more privileges then your “admin” user has. Again this is related to the shared responsibility model of management in AWS. Amazon RDS team has set this red line that you can’t cross.

We will leave setting up Oracle RDS is outside the scope of this article and to continue with the next steps you will need Oracle RDS instance up and running and we hope you can start a new Oracle RDS or you already have access to an existing Oracle RDS instance. If you would like to know best practices for running Oracle RDS please see AWS documentation.

In the next section we will look at what is available for audit of DBA activity.

DBA activity on Oracle RDS and audit options

The limitations of RDS instances raise many questions on how to audit DBA – specific actions like ALTER SYSTEM, CREATE USER, DROP DATABASE etc. And how can you audit that internal RDS users like RDSADMIN? Ok, let us review all available options.

You can view internal RDS activity using Amazon RDS Database Log Files You can view, download, and watch database logs using the Amazon RDS console, the AWS Command Line Interface (AWS CLI), or the Amazon RDS API Amazon RDS API. Amazon provides Point-In-Time Recovery service and claims that RDS uploads transaction logs for DB instances to Amazon S3 every 5 minutes . So Database Log Files and CloudTrail service both help you analyze RDSADMIN user activity along with extra information on Oracle RDS events. All these options are good until you need real-time transaction monitoring and alerting.
With DAF instances running on separate EC2 VMs you can audit all sessions and queries running through to your Amazon RDS instance. The purpose of DAF instances is to become your database guard and since your need to monitor DBA activity we will see this option in detail further. Instances DataSunrise on EC2 boxes are able to monitor and secure network activity to Amazon RDS.

DataSunrise on AWS overview and prerequisites

Setting up and configuring secured DataSunrise instances involves several important steps. To prepare your secured DataSunrise instance on the AWS environment please follow the steps described in our DataSunrise AWS Security Best Practices document. To mention few steps are the following:

assign proper IAM roles to your EC2 instances with DataSunrise instances;
create and assign VPC Security Group(s) to your Amazon RDS instance and EC2 instances having DataSunrise software;
use secured and unique passwords for every account.

The architecture below consists of a database instance (RDS or on EC2 instance) behind DAF, separate Audit Storage database (RDS or on EC2 instance), and DataSunrise instance that serves as a proxy server for user connections.

As an option DataSunrise provides CloudFormation scripts to deploy in AWS secured and cost-efficient database security solutions. Following your creation of Amazon RDS instance, these scripts automate all required tasks to deploy EC2 instances, installing DataSunrise on these EC2 instances, setting up Amazon Load Balancer as well as the creation of all other required AWS resources. We will skip the CloudFormation option and will continue with a single EC2 instance scenario. We have prepared videos on how to install DataSunrise instance, please watch one of the videos and follow the required steps:

Installing DataSunrise on Linux https://www.youtube.com/watch?v=FWoGY2qc0F8
Installing DataSunrise on Windows https://www.youtube.com/watch?v=wKlFUdUUbSE

At the end of the installation process your DataSunrise instance should be accessible from your Web browser. You will need DataSunrise instance up and running so you can access your DataSunrise instance Web Console with required privileges.

The next prerequisite is Database Configuration you should create in DataSunrise instance to start a proxy for Amazon RDS. Please refer to DataSunrise User Guide, section “3.1 Creating a Target Database Profile and a Proxy” and section “5.1.6 Creating Database Users Required for Getting the Database’s Metadata”. Since the DataSunrise in proxy mode acts as a man-in-the-middle by intercepting all non-AWS TCP packets to Amazon RDS instance, you can use the same standard Oracle Database port 1521 since the DataSunrise instance is running on another EC2 instance. Finally, make sure your Amazon RDS instance is NOT available from any other non-AWS IP / name and port other than through DataSunrise instance. All these steps will ensure that all your client applications can access your Oracle RDS instance through your DataSunrise instance only.

Configuring DataSunrise to audit DBA

As we have mentioned earlier, upon creating your Oracle RDS you receive limited DBA account and its password, by default Oracle RDS offers “admin” database user to access your instance. And as you remember Amazon RDS disables SYSDBA privilege for you. And that narrows down the possible area of potential threats made to Amazon RDS instance. If your Oracle RDS is accessible from your desktop machine try to connect to your Oracle RDS as SYSDBA to prove that is true, see an example below.

You will see that no SYSDBA, no SYSOPER, or other SYS – related privileges are available in Oracle RDS either using TCP or SSH.

Therefore you have to take care to audit network connections – remote connections using the right tool such as DataSunrise DAF. We will configure DataSunrise instance to capture any type of actions that your DBA can perform remotely to Amazon RDS instance.

Summary of the next steps

To audit DBA actions we will perform the following steps:

Identify your DBA user names / accounts. DataSunrise keeps Database Users under its Configuration menu. If you have more then one DBA then create a new Database Users Group under Configuration → Database Users. In our example we will use DBA called “admin” that was generated by our Oracle RDS instance.
Using Configuration → Object Groups create a new entry and add a single item with regular expression “.*”.
Create a new Audit Rule to include Database User and Query Group to Audit DBA activity.
Check DBA activity in DataSunrise instance

1. Identify and configure your DBA users in DataSunrise

Let’s proceed with all these steps. Firstly under Configuration → Database Users we check the “admin” user is known to our DataSunrise instance. If DataSunrise does not have one then create the user “admin” manually. If you have several Oracle RDS instances and the same “admin” DBA user name used you can choose <Any> Instance. Please do not forget to click the Save button to save your settings on each page.

In the picture above we have created ADMIN user and included the one to DBA Team group. If you have created multiple DBA users please add them to the “Oracle DBA Team” User Group in DataSunrise instance.

2. Configure a new Query Group

Second step – we will create our new Query Group “AnyQuery” and add just one entry “.*” in the Query item. Please see the settings in the picture below.

When you create a new Query for the Query Group (see “Add” button on the picture) please check in the “Regular Expression” checkbox.

At this stage we have “ADMIN” database user, “Oracle DBA Team” User Group registered in DataSunrise instance and our “AnyQuery” Query Group with one Query that has “.*” regular expression pattern to match any query. Halfway done.

3. Create and configure a new Audit Rule

Next we will create a new Audit Rule with the name “Oracle: admin queries” using what we have created in DataSunrise instance. Please open Web Console and enter Audit → Rules. Click Create to create the new rules. Please see the details on the pictures below.

We will use Filter Statements and Query Group tab on the page to connect our rule with corresponding settings we have done earlier, see details in the following pictures.

When you select “Oracle DBA Team” User Group for DB User Group session parameter you make DataSunrise capture any session from any IP / host having a user name on the “Oracle DBA Team” list. When you add another database user item to the “Oracle DBA Team” User Group this rule will check the new user in this rule automatically. And since you use the Query Group tab on the Audit Rule page and selected “AnyQuery” there then DataSunrise will check any expression or query that your DBA team will execute through the DataSunrise instance. Later on you will see these events under Audit → Transactional Trails.

Additionally and optionally you can send Audit event details to an external SIEM using Syslog protocol or push alerts to other external systems (SMTP/email, Jira, ZenDesk, instant messengers). To configure Syslog – compatible server connection please navigate to Configuration → Syslog Settings and configure required settings on that page. See “7.7 Syslog Settings (CEF Groups)” and “10.6 Syslog Integration Settings” in our User Guide for more details. To send alerts to other then Syslog recipients, add new items to Subscribers (Wed console: Configuration → Subscribers → Add Server… Add Subscriber menu items). You can find more information on Subscribers in section “7.5 Subscriber Settings” of the DataSunrise User Guide. Later you can use Syslog setting or/and Subscribers in your DataSunrise rules (see first picture above); please see corresponding sections in DataSunrise User and Administrative Guides. When configured you can pick up your Syslog and Subscriber items on your Audit Rule. Please do not forget to click the Save button on the Audit Rule page to save new settings.

This way we selected query types that we need to audit (and probably to notify some people with alerts) for concrete Amazon RDS instance or several instances if you choose “<ANY>” item in the Instance drop-down box on the page of the rule. There are several options to skip auditing typical queries like DBMS tools, for more information see section “6.4.2 Query Group Filter” in DataSunrise User Guide in connection to Skip Group of Query parameter of rules.

4. Check DBA activity in DataSunrise instance

To check our new Audit Rule captures queries we will execute CREATE USER and DROP USER queries using the Oracle SQL Developer tool. We will connect to the Oracle RDS instance through DataSunrise instance.

Following that we can check Transactional Trails in DataSunrise instance.

Since we set Log Event in Storage option in our Audit Rule, we can find these events on

Audit → Transactional Trails page on our DataSunrise instance Web console.

Notes on EC2 with Oracle Database instance

There are some considerations on using database instances on Amazon EC2 instances along with DataSunrise. Since you setup and configure these instances you may for some reason allow local connections (SSH, RDP etc.) or remote connections (database TCP) which bypass DataSunrise instance (or your Load Balancer). All these connections must be strictly limited to ensure a maximum level of security and management. In your VPC we recommend implementing strict rules using Security Groups and Network ACLs. The goal of all these restrictions should be to eliminate direct access to your database instance from any IP or network but DataSunrise on your EC2 instance to database port only.

Next steps you can see in the section “Configuring DataSunrise settings” of this article. And as you probably still think how to audit your potentially harmful SYSDBA (and similar) roles we will look into DataSunrise audit capabilities to help with that.

Starting from DataSunrise 6.2 you can include session parameters in Filter Sessions conditions. Please see below our Audit Rule with the condition to audit SYSDBA and similar privilege in addition to the “Oracle DBA Team” in DB User Group. We assume we have configured our DataSunrise instance to protect Oracle Database running on EC2 instance and there is no other way to connect to the database server but through DataSunrise instance in proxy mode only.

Since our DAF protects Oracle Database on EC2 then we will try to connect to Oracle through DataSunrise proxy. On the pictures below we connect to the database using Oracle SQL Developer via DataSunrise instance and use “sys” user name and SYSDBA role. We set that this is permitted in our Oracle Database instance. See the connection test result on the following picture.

If the test passed with Success status we can try to execute some queries the same way as we did earlier in the section “Configuring DataSunrise to audit DBA” of this article. Oracle SQL Developer will execute queries through DataSunrise instance. Then on DataSunrise Web console we can check Audit → Transactional Trail or Session Trails to see all “sys” user queries and sessions. Our Audit Rule captures database user “sys” despite of that particular user is not on the list of our “Oracle DBA Team” we created earlier in DataSunrise instance. When we open Audit → Session Trail and one particular item we will see that DataSunrise instance saves details on Oracle Database roles as well as other useful information.

This way we have configured DataSunrise instance to audit any DBA activity including SYSDBA and similar system privileges Oracle Database has.

Conclusion

We have seen that Oracle RDS requires less effort to secure Oracle Database instance and audit DBA activity. On EC2 instances it requires to roll up your sleeves and set Session parameters and thanks to DataSunrise version 6.2 you can audit Oracle Database built-in roles. For more information please see DataSunrise User Guide and references attached.