About UsCustomersResources

Query Logs

Available query logs sources for Oracle data sources:

  • System tables / views

  • Database Activity Streams on an Amazon RDS instance

If you are using a different hosting provider and you want to ingest query logs, please contact our sales team.

Database Activity Streams on an Amazon RDS instance

To enable Database Activity Streams on an Amazon RDS instance running Oracle, follow the step-by-step instructions below using the AWS Management Console.

Step 1: Create a Database Audit Specification

Execute query to create a database audit specification called select_star_query_logs, ensuring that all matched events are sent to the Kinesis DAS. Note that the policy is not applied to the SYS, RDSSEC, or RDSADMIN user. These are the user accounts that RDS uses to maintain the database and are not used to execute queries that Select Star care about.

The query:

CREATE AUDIT POLICY select_star_query_logs
ACTIONS ALL
WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') NOT IN (''SYS'', ''RDSSEC'', ''RDSADMIN'')'
EVALUATE PER SESSION;

Step 2: Sign in to the AWS Management Console

  1. Open the AWS Management Console at https://console.aws.amazon.com/rds/.

  2. Sign in with your AWS credentials.

Step 3: Navigate to the RDS Dashboard

  1. In the AWS Management Console, search for RDS in the top search bar.

  2. Click on RDS to open the Amazon RDS Dashboard.

Step 4: Select Your RDS Instance

  1. In the RDS Dashboard, click on Databases in the left-hand menu.

  2. Locate your Oracle RDS instance in the list of databases.

  3. Click on the DB Identifier of the Oracle instance you wish to enable the Database Activity Stream for.

  4. Verify DB instance class used by instance with supported DB instance classes for database activity streams

Step 5: Start the Database Activity Stream

  1. On the database details page, click on the Actions dropdown menu located in the top-right corner.

  2. Select Start activity stream from the dropdown menu.

Step 6: Configure the Database Activity Stream

  1. The Start database activity stream: name window will appear, where name is your RDS instance.

  2. Configure the following settings:

    • AWS KMS key: Choose a KMS key from the list of available AWS KMS keys. This key is used to encrypt the key that encrypts the database activity stream data.

    • Database activity events: Check the box to Enable engine-native audit fields.

  3. Choose Immediately if you can restart the RDS instance right away and start the database activity stream immediately. If you select During the next maintenance window, the RDS instance will restart during the next scheduled maintenance window, and the activity stream will start at that time.

You can refer to AWS documentation about starting a database activity stream for details.

Entries in the activity stream are encrypted using unique batch keys before being written to Kineses. To facilitate decryption, the session keys is itself encrypted using the selected KMS key and included in the batch. Anyone with access to the KMS key will be able to decrypt the batch keys and use those to access raw audit events from the database.

Step 7: Start the Activity Stream

  1. After configuring the settings, click on Start database activity stream.

  2. The status of the database will indicate that the activity stream is starting.

Step 8: Locate AWS KMS key and AWS Kinesis stream

  1. Once on the database details page, open the Configuration tab.

  2. In the Configuration tab, locate in section Database activity stream following:

    • AWS KMS key: This shows the current KMS key used by the Database Activity Stream. Note this key may be different one that one used for the RDS instance.

    • Kinesis data stream: Note the configured Kinesis stream.

Step 9. Create CloudFormation stack

Select Star recommends setting up integration using AWS CloudFormation, which allows you to make necessary changes to the RDS instance environment automatically, transparently, safely, and auditably. It deploys AWS Firehose and AWS Lambda function to store relevant information in AWS S3 bucket and enable us to access the AWS S3 bucket.

The source code of the CloudFormation template, build scripts, and real-time logs of the continuous deployment system are available on our public repository selectstar/cloudformation-templates, hosted on Github, to be freely audited.

You can pass the link to CloudFormation to the infrastructure team to enable the integration to be created.

1. A simple form will be displayed in Select Star. In option Source Type select "RDS Database Activity Stream".

2. Click Open CloudFormation. A new window will open to proceed to the creation of a CloudFormation stack by AWS Management Console. Make sure you are logged into the AWS account in which the RDS instance is hosted.

3. The Create Stack form will be displayed. Some of the values will be filled in by default, under Parameters, enter:

  • KinesisStreamARN: The ARN of the Kinesis stream where RDS Database Activity Streams are delivered. Example: arn:aws:kinesis:us-east-2:000000000:stream/aws-rds-das-db-ZQO7M43PGGUXJEZVYSALTO76KA

  • KmsKeyARN: The ARN of the KMS key used for encryption of RDS Database Activity Streams. Example: arn:aws:kms:us-east-2:000000000:key/f319545f-a0d4-4bfc-896f-5d37fe921ffb

  • RdsResourceId: The ARN of the RDS instance or cluster producing the logs. Example: db-ZQO7M43PGGUXJEZVYSALTO76KA

4. Review the information and under Capabilities choose "I acknowledge that AWS CloudFormation might create IAM resources".

5. Choose Create stack.

  1. Wait until the stack changes its status to "CREATE_COMPLETE" from "CREATE_IN_PROGRESS " in the tab "Stack info." The operation should take up to 5 minutes. You need to refresh the tab to see the progress.

7. After completing stack creation, the Role ARN and Bucket name is available from the "Outputs". Copy and save the RoleArn and Bucket name for later use.

8. Logs are published in batches, which can be buffered up to 15 minutes. After configuring and executing a database query, new files should appear in the bucket under the processed prefix.

Step 10. Confirm authorization

1. Return to Select Star. You should see a form that allows you to provide "Role ARN" . Fill form in the required information:

  • AWS region: Identifier of AWS Region where your Oracle instance is hosted eg. us-east-2

  • Role ARN: Identifier of AWS IAM Role to use by Select Star.

  • S3 bucket: The name of the AWS S3 bucket which stores query logs. Available in the "Outputs" tab of AWS CloudFormation.

  • S3 object prefix: The name of the AWS S3 prefix which stores query logs. For deployment using AWS CloudFormation, this is processed/ by default.

2. Click Connect.

PreviousOracle (beta)NextSalesforce (beta)

Last updated