Note:

• This tutorial requires access to Oracle Cloud. To sign up for a free account, see Get started with Oracle Cloud Infrastructure Free Tier.
• It uses example values for Oracle Cloud Infrastructure credentials, tenancy, and compartments. When completing your lab, substitute these values with ones specific to your cloud environment.

Streamline Continuous Database Monitoring by Automating Oracle Data Safe Registration with OCI CLI

Introduction

Maintaining a robust cybersecurity posture for Oracle databases is essential in today’s complex threat environment. Continuous monitoring is key to proactively identifying vulnerabilities, misconfigurations, and potential threats before they can be exploited. As cyber threats become more sophisticated and targeted, regular assessments for compliance, unusual activities, and unauthorized access become increasingly crucial.

Manually registering multiple databases in Oracle Data Safe using the Oracle Data Safe console can be labour-intensive and time-consuming. This tutorial offers a streamlined approach to automate the registration process using Oracle Cloud Infrastructure Command Line Interface (OCI CLI) commands and scripts within the OCI Cloud Shell interface, significantly reducing the manual effort and time required.

Audience

• Database administrators and OCI security administrators.

Objectives

• Automate Oracle Data Safe registration using OCI CLI to reduce manual efforts and ensure consistent configuration.

  Use case: Automating all of your Oracle Database as a service (DBaaS) registration and management with Oracle Data Safe using OCI CLI.

  In this tutorial, we will walk you through automating the registration of Oracle Database as a service (DBaaS) with Oracle Data Safe using the OCI CLI. We will use a straightforward example to demonstrate the process, focusing on OCI CLI commands tailored to a specific compartment where you manage your Oracle Data Safe operations. This tutorial can also serve as a reference to adapt the procedure for registering your on-premises target databases. For more information, see Register an Oracle On-Premises Database.

  To downloads complete scripts, see GitHub Repository.

Prerequisites

• Access OCI Cloud Shell:

  To begin using OCI Cloud Shell, you must first grant user access through an Oracle Cloud Infrastructure Identity and Access Management (OCI IAM) policy. For more information, see OCI Cloud Shell.

  Example OCI IAM policy to allow access: allow group <GROUP-NAME> to use cloud-shell in tenancy

  If you want to specify a domain, use: allow group <DOMAIN-NAME>/<GROUP-NAME> to use cloud-shell in tenancy

• Permission for Oracle Data Safe:

  Grant user group permission on all Oracle Data Safe resources. For more information, see Create an Oracle Data Safe Administrators Group.

  Example OCI IAM policy: Allow group Data-Safe-Admins to manage data-safe-family in tenancy

• Create an Oracle Data Safe Service Account in All Target Databases:

  Each target database for Oracle Data Safe needs a service account. Autonomous Databases include this by default, but for non-Autonomous Databases, you must create it manually. For more information, see Create an Oracle Data Safe Service Account on Your Target Database.

  SQL Command:

  CREATE USER DATASAFEADMIN identified by password
  DEFAULT TABLESPACE "DATA"
  TEMPORARY TABLESPACE "TEMP";
  GRANT CONNECT, RESOURCE TO DATASAFEADMIN;
  

  Note:

  • Password must be 14+ characters, including an uppercase letter, lowercase letter, number, and special character.
  • Avoid using SYSTEM or SYSAUX tablespaces.

  To grant roles, download and run the datasafe_privileges.sql script from the Oracle Data Safe console. Run the script as SYS. For more information, see Grant Roles to the Oracle Data Safe Service Account on Your Target Database.

  

  

  Sample Script Output:

  SQL> @datasafe_privileges.sql DATASAFEADMIN GRANT ALL
  Enter value for USERNAME (case sensitive matching the username from dba_users)
  Setting USERNAME to DATASAFEADMIN
  Enter value for TYPE (grant/revoke)
  Setting TYPE to GRANT
  Enter value for MODE (audit_collection/audit_setting/data_discovery/masking/assessment/all)
  Setting MODE to ALL
  Granting AUDIT_COLLECTION privileges to "DATASAFEADMIN" ...
  Granting AUDIT_SETTING privileges to "DATASAFEADMIN" ...
  Granting DATA_DISCOVERY role to "DATASAFEADMIN" ...
  Granting MASKING role to "DATASAFEADMIN" ...
  Granting ASSESSMENT role to "DATASAFEADMIN" ...
  Done.
  Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
  Version 21.1.0.0.0
  [oracle@dbcs21c ~]$
  

Task 1: Access OCI Cloud Shell

Access the OCI Cloud Shell to utilize its integrated capabilities.

1. Log in to the OCI Console.

   

2. Click Cloud Shell/Code Editor icon from the console header and select Cloud Shell from the drop-down menu. Note that the OCI CLI running in the OCI Cloud Shell will execute commands against the region selected in the console’s region selection menu when the OCI Cloud Shell started.

   

Task 2: Create Private Endpoints for Target Databases

Oracle Data Safe supports connectivity to Oracle databases using either public or private IP addresses. For databases with private IP addresses, you have two options: an Oracle Data Safe private endpoint or an Oracle Data Safe on-premises connector. In this tutorial, we will focus on creating private endpoints for registering DBaaS databases.

Script Name: generate_private_endpoints_and_commands_for_missing_vcns.sh.

Description: This script identifies VCNs in a specified OCI compartment that lack Oracle Data Safe private endpoints. It generates a CSV file listing the missing VCNs and their associated subnets and creates a shell script containing OCI CLI commands to create the necessary private endpoints for each VCN.

Follow the Steps:

1. Prompt the user for the compartment OCID.

2. Identify VCNs that are missing Oracle Data Safe private endpoints.

3. List the missing VCNs and their associated subnets.

4. Generate a CSV file named list_vcns_without_private_endpoints.csv with details of missing VCNs and subnets.

5. Create a shell script named create_private_endpoints_commands.sh containing OCI CLI commands to create private endpoints for the identified VCNs.

Sample Output:

Task 3: Generate Target Database Credentials in JSON Format

In this tutorial, a single Oracle Data Safe service account is used across all target databases. Therefore, the database credential JSON file will be the same for all target databases.

Script Name: generate_target_db_credentials.sh.

Description: This script prompts the user to enter a username and password, then creates a JSON file named Credentials_Target_DBaaS.json containing the provided credentials. The JSON file is used for securely storing the username and password of the target database.

Follow the Steps:

1. Prompt the user to enter a username.

2. Prompt the user to enter a password.

3. Create a JSON file named Credentials_Target_DBaaS.json containing the username and password.

Sample Script Output:

Task 4: Generate Private Endpoint Connection Options in JSON Format

Script Name: generate_Connection_Options_private_endpoints.sh.

Description: This script automates the process of listing VCNs, subnets, and Oracle Data Safe private endpoints within an OCI compartment. It generates a CSV file containing detailed information about each private endpoint, including associated VCN and subnet names, and creates individual JSON files for each endpoint to define Oracle Data Safe connection options.

Follow the Steps:

1. Prompt the user for the compartment ID.

2. List all VCNs and save the output to vcn_list.txt.

3. List all subnets and save the output to subnet_list.txt.

4. List all Oracle Data Safe private endpoints and save the output to PE_list.txt.

5. Generate a CSV file named list_All_private_endpoints_details.csv with detailed information about each private endpoint.

6. Create JSON files for each private endpoint to specify Oracle Data Safe connection options.

Sample Script Output:

Task 5: Register Autonomous Databases to Oracle Data Safe

When registering an Oracle Autonomous Database Serverless with Secure Access from Everywhere, you do not need to select a connectivity option or specify service account details, as these are included by default. In this tutorial, we demonstrate how to manage and register Oracle Autonomous Database Serverless.

Script Name: generate_Autonomous_database_details_with_data_safe.sh.

Description: This script automates the process of listing Oracle Autonomous Databases in an OCI compartment and checking their Oracle Data Safe registration status. It generates a CSV file containing details of all Oracle Autonomous Databases and creates JSON files for databases that are not registered with Oracle Data Safe. Additionally, the script prepares Oracle Data Safe registration commands for each unregistered database and saves them in a shell script. This enables users to quickly register unregistered databases by running the generated commands.

Follow the Steps:

1. Prompt for compartment ID.

2. Generate a CSV file with Oracle Autonomous Database details.

3. Create JSON files for unregistered databases.

4. Prepare Oracle Data Safe registration commands in a shell script.

5. Run the registration commands to register the databases.

Sample Script Output:

Task 6: Register Oracle Cloud Databases to Oracle Data Safe

Script Name: generate_Cloud_database_details_with_data_safe.sh.

Description: Automates the extraction and processing of Oracle cloud database details and their private endpoints, creating JSON configuration files and generating registration commands for Oracle Data Safe.

Follow the Steps:

1. Prompt for compartment ID.

2. List DBaaS databases and save to Output1.txt.

3. Append subnet IDs for databases missing VM cluster IDs and database system IDs.

4. Save results to Oracle_Cloud_Databases_Details.csv.

5. List private endpoints and save to Datasafe_Private-Endpoint_List.txt.

6. Generate JSON files for each PDB name.

7. Create registration commands in Datasafe_CloudDB_Registration_Commands.sh.

Sample Script Output:

Task 7: Update the Security and User Assessment Schedules

You can configure schedules to automatically save the latest security and user assessments for your target databases to a designated compartment in OCI. For more information, see Schedule Security Assessments and Schedule User Assessments.

Script Name: generate_datasafe_assessment_schedules.sh.

Description: This shell script is designed to automate the process of retrieving Oracle Data Safe target databases, their corresponding security and user assessments, and generating update schedules for OCI. The script prompts the user for a compartment ID, retrieves active target databases in Oracle Data Safe, and generates two update schedule scripts — one for security assessments and one for user assessments.

Follow the Steps:

1. Prompt for compartment ID.

2. Enter compartment ID.

3. List Active Targets: Save to Datasafe_Active_TargetDB_list.txt.

4. Retrieve Assessments: Append details to Datasafe_Active_TargetDBs.txt.

5. Generate Scripts: Create schedule_security_assessments.sh and schedule_user_assessments.sh.

Sample Script Output:

Task 8: Start collecting the Audit logs for the Target Databases

When a target database is registered, Oracle Data Safe automatically detects the available audit trails and creates a corresponding audit trail resource for each target database. Once audit trail collection is started, Oracle Data Safe copies audit records from the target database into its repository for monitoring and analysis. You can control the audit data collection by starting or stopping it as needed. For more information, see Audit Trails.

Script Name: Generate_DataSafe_Audit_Collection_Scripts.sh.

Description: The script, Generate_DataSafe_Audit_Collection_Scripts.sh, automates the process of collecting audit trail data for Oracle Data Safe target databases. It prompts the user for a compartment ID and an audit trail collection start time, retrieves the audit trails with status NOT_STARTED, and generates a single shell script to initiate audit trail collection for all target databases.

Follow the Steps:

1. Enter Compartment ID: Prompts the user for the OCI compartment ID.

2. Enter Start Time: Prompts for the audit collection start time in YYYY-MM-DD format.

3. Fetch Audit Trails: Retrieves audit trails with NOT_STARTED status from OCI.

4. Generate CSV: Extracts relevant audit trail data into audit_trails.csv.

5. Create Script: Generates Data_safe_Target_DB_Audit_Collection_Start.sh with commands to start audit collection for all targets.

Sample Script Output:

Task 9: Prepare an Oracle Data Safe Inventory

Script Name: generate_data_safe_db_inventory.sh.

Description: This shell script interacts with OCI to retrieve and process Oracle Data Safe target database information based on their types: AUTONOMOUS_DATABASE, DATABASE_CLOUD_SERVICE, and INSTALLED_DATABASE. It generates output files with detailed information about each database type.

Follow the Steps:

1. Input Compartment ID: Prompt the user to enter the compartment ID where the databases are located.

2. List and Filter Databases: List all Oracle Data Safe target databases in the specified compartment and filter them by type.

3. Retrieve Database Details:

   • AUTONOMOUS_DATABASE: Retrieve details such as display name, database ID, and infrastructure type.

   • DATABASE_CLOUD_SERVICE: Retrieve details such as database system ID, VM cluster ID (handling null values), and listener port.

   • INSTALLED_DATABASE: Retrieve information including instance ID, IP addresses, and service name.

4. Generate Output Files: Create separate files for each database type with the collected details.

5. Cleanup: Remove temporary files and finalize the output.

Sample Script Output:

Related Links

• Oracle Data Safe

• Oracle Cloud Infrastructure Cloud Shell

• Oracle Data Safe OCI CLI Commands

Acknowledgments

• Author - Alex Kovuru (Principal Cloud Architect)

• Contributor - Indiradarshni Balasundaram (Cloud Engineer)

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.

---

Title and Copyright Information

Streamline Continuous Database Monitoring by Automating Oracle Data Safe Registration with OCI CLI

G14970-01

September 2024

Copyright ©2024,

Oracle and/or its affiliates.