About Us Customers Resources

Oracle (beta)

Follow these steps to connect your Oracle instance to Select Star.

  1. Create a Service Account

  2. Create a new datasource in Select Star

  3. Enable query logging (optional)

  4. Choose databases and schemas

For instances operating within a non-publicly accessible environment, such as a private network, please refer to our guide on Integrating Private Network Data Sources for detailed instructions and best practices.

1. Create a Service Account

We recommend creating a dedicated service account for Select Star with metadata-only roles and permissions.

  1. Execute the following command:

    -- Create the user
CREATE USER <username> IDENTIFIED BY <password>;
-- Create the role;
CREATE ROLE <role>;
-- Grant the role to the user;
GRANT <role> TO <username>;

-- Grant privileges to the role
GRANT CREATE SESSION TO <role>;
GRANT SELECT_CATALOG_ROLE TO <role>;

-- Grant access to all tables
GRANT SELECT ANY TABLE TO <role>;

2. Create a new data source in Select Star

  • Display Name - The Name you want to give to your new datasource.

  • Hostname - Your hostname defines the location where your database is hosted.

  • Port - The communication endpoint used to connect clients to the Oracle instance.

  • Username - Specify the User to connect to the Oracle instance. It should have enough privileges to read all the metadata.

  • Password - Password.

  • SID - A unique identifier for an Oracle database instance (System Identifier).

Select Star currently does not support Popularity and Lineage for this integration. If needed, please feel free to contact us so we can explore alternative solutions.

3. Enable query logging (optional)

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

If you are using different hosting provider and you want ingest query log please contact our sales team.

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.

  • Bucket name: The name of the AWS S3 bucket which stores query log.

2. Click Connect.

4. Choose databases and schemas

After you fill in the information, you'll be asked to select the databases you'd like to load into Select Star.

Select Star will not read queries or metadata for databases, schemas, or tables that are not loaded. Please load all data for which you expect to see metadata.

You can change the databases and schemas you have loaded if needed.

Your metadata should start loading automatically, once the sync is complete, you'll be able to see it in Select Star.

See the link below for more information on how to navigate through data warehouses.

Getting Started: Data Warehouse
PreviousMySQL (beta)NextSalesforce (beta)

Last updated