Best Practices for Using Database Tools

This topic describes procedures and practices that can help you to use Database Tools more easily and efficiently.

Isolating Database Connections with Compartments

Database connections are a useful feature when granting access to database to a specific user or defined groups of users. To help isolate these connections, use policies to grant access to specific connections based on a user's role, as demonstrated in the following example:

For an Autonomous Data Warehouse that is used for reporting, create a shared database connection named ReportingDataAccess. Assign this connection to a specific set of end users in a group called Data Scientists. The users in this group now have access to the details of the database connection, but are limited to using only this connection and the roles and privileges inherited by the user that the connection is using to access the database.

You can also create an administrator connection to the data warehouse, and assign a different set of users to that connection with a group called DatabaseAdministrators. Using compartments and policies, you can separate the access to these connections as follows:

Reporting Data Access group:

  1. Create a compartment called Reporting.
  2. Create a group in this compartment called DataScientists.
  3. Create a database connection in the Reporting compartment called ReportingDataAccess.
  4. Create a policy that gives access to the connection in the Reporting compartment to the DataScientists group:
    • Allow group DataScientists to read database-tools-family in compartment Reporting
    • Allow group DataScientists to use database-tools-connections in compartment Reporting

Database Administrators group:

  1. Create a compartment called DBA.
  2. Create a group in this compartment called DatabaseAdministrators.
  3. Create a database connection called DatabaseAdmin in the DBA compartment.
  4. Create a policy that gives access to the connection in the DBA compartment to that group:
    • Allow group DatabaseAdministrators to read database-tools-family in compartment DBA
    • Allow group DatabaseAdministrators to use database-tools-connections in compartment DBA

These groups and compartment-specific database connections help isolate which users can access which databases and determine the access they have.

Using Private Endpoints

When creating database connections to an Autonomous Database that is using a private endpoint, create a network security group to provide access from Database Tools to that database. When creating the network security group, you define specific access rules that limit which IPs or CIDR blocks can be used, and the ports or port ranges.

For the SQL worksheet

To configure the minimum set of network security group rules needed to provide access from the Database Tools SQL Worksheet in the OCI Console:

  1. On the Private Endpoint Details page, locate the Reverse Connection Source IPs field in the Private Endpoint Information section. There are two IP addresses.
  2. Edit the network security group and add rules for the two IP addresses, with access to destination port 1522 using TCP/IP. For example, given two IP addresses, 10.0.1.6 and 10.0.1.8, the two rules have the CIDR blocks 10.0.1.6/32 and 10.0.1.8/32 with access to destination port 1522, using TCP/IP.

For SQLcl and Bastions

To configure the minimum set of network security group rules needed to provide access from the Database Tools SQLcl using a bastion in the OCI Console:

  1. On the Bastion details page, locate the private endpoint IP address item in the Private endpoint IP address field. Only one IP address shown.
  2. Edit the network security group and add rules for this IP address with access to the destination port of 1522 using TCP/IP. For example, if given an IP address 10.0.1.9, then the rule has the CIDR block of 10.0.1.7/32 with access to the destination port of 1522 using TCP/IP.

These rules ensure that Database Tools has access to only the selected Autonomous Database through the network security group from these specific IP addresses, and only on port 1522 for bastion access using SQLcl and SQL Worksheet access on the OCI Console.