Secure Oracle ATP with Private Endpoint and Wallet

How to configure Oracle ATP with private endpoint & wallet for secure, private database access. Step-by-step guide for enhanced security.

Overview: Securing and Accelerating Oracle PeopleSoft with Autonomous Transaction Processing Private Endpoints

In today's landscape of escalating cyber threats and stringent compliance requirements, securing mission-critical enterprise applications like Oracle PeopleSoft is paramount. Organizations running PeopleSoft often manage vast amounts of sensitive data, from financial records to HR information, making robust database security and network isolation non-negotiable. Oracle Autonomous Transaction Processing (ATP), a key component of Oracle Cloud Infrastructure (OCI), offers a compelling solution by providing a self-driving, self-securing, and self-repairing database service. When combined with private endpoints and secure wallet-based connectivity, ATP elevates the security posture and operational efficiency for PeopleSoft deployments, whether for core application databases, reporting, or data warehousing.

A private endpoint for Oracle Autonomous Database ensures that your database instance is only accessible from within a specified Virtual Cloud Network (VCN) and its connected networks (e.g., via FastConnect or VPN Connect). This eliminates exposure to the public internet, drastically reducing the attack surface. For PeopleSoft environments, this means that your application servers, batch servers, and other components can connect directly and securely to the ATP database without traversing public networks, adhering to the principle of least privilege and network segmentation best practices. The client wallet, an essential component, provides the necessary credentials and trust certificates for mutual TLS (mTLS) authentication, guaranteeing that both the client and the database verify each other's identity before establishing a connection. This comprehensive approach delivers an enterprise-grade, highly secure, and performant database foundation for PeopleSoft applications.

This article will guide you through the detailed process of provisioning an Oracle Autonomous Transaction Processing database with a private endpoint, configuring the necessary network components, and establishing secure connectivity using the client wallet. We will also delve into critical security considerations, best practices, and address common questions relevant to PeopleSoft administrators and architects.

Prerequisites

Before embarking on the implementation, ensure you have the following prerequisites in place:

Oracle Cloud Infrastructure (OCI) Account: You need an active OCI tenancy with sufficient credits and appropriate IAM policies configured to create and manage Autonomous Databases, VCNs, subnets, and network security groups (NSGs).
OCI CLI Configured: The OCI Command Line Interface (CLI) should be installed and configured on your local machine or a designated OCI Compute instance. This includes setting up API keys for authentication. If not, follow the official Oracle documentation for OCI CLI Installation and Configuration.
Virtual Cloud Network (VCN) and Subnet: An existing VCN with at least one private subnet is required. This subnet will host the private endpoint for your ATP database. For PeopleSoft deployments, it's common to have a dedicated private subnet for application servers, and another for databases. Ensure these subnets can communicate if they are separate. For our examples, we will assume a VCN CIDR of `10.0.0.0/16` and a private subnet CIDR of `10.0.1.0/24`.
Network Security Group (NSG) or Security List: You will need to create or modify an NSG or Security List to allow ingress traffic on TCP port 1522 (the default listener port for ATP) from your PeopleSoft application servers' subnet to the ATP private endpoint. NSGs are generally preferred for their granular control and flexibility.
SQL Client: A SQL client tool such as Oracle SQL Developer, SQLcl, or even `sqlplus` (installed as part of Oracle Instant Client) will be necessary to test connectivity. For PeopleSoft, this often means ensuring the Oracle Client is installed on your application server.
Basic Networking Knowledge: Understanding of VCNs, subnets, routing tables, and security rules in OCI is beneficial.


# Example of OCI CLI configuration check
oci setup config --help
# If configured, you should be able to run a simple command like:
oci iam availability-domain list

Step-by-Step Implementation

1. Prepare Your Virtual Cloud Network (VCN) and Subnet

First, identify your existing VCN and the private subnet where the ATP private endpoint will reside. If you don't have a suitable subnet, create one. Crucially, we need to configure network security to allow your PeopleSoft application servers to connect to the ATP private endpoint.

Identify VCN and Subnet OCIDs:

You'll need the OCIDs of your VCN and the target private subnet. Replace `compartment-ocid` with your actual compartment OCID.


# List VCNs in your compartment
oci network vcn list --compartment-id ocid1.compartment.oc1..aaaaaaaayyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy --display-name "MyPeopleSoftVCN"

# Example output (truncated)
# {
#   "data": [
#     {
#       "cidr-block": "10.0.0.0/16",
#       "compartment-id": "ocid1.compartment.oc1..aaaaaaaayyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy",
#       "display-name": "MyPeopleSoftVCN",
#       "id": "ocid1.vcn.oc1.phx.aaaaaaaaae3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
#       "lifecycle-state": "AVAILABLE",
#       ...
#     }
#   ]
# }

# List subnets within your VCN
oci network subnet list --compartment-id ocid1.compartment.oc1..aaaaaaaayyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy --vcn-id ocid1.vcn.oc1.phx.aaaaaaaaae3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx --display-name "PeopleSoftAppSubnet"

# Example output (truncated)
# {
#   "data": [
#     {
#       "cidr-block": "10.0.1.0/24",
#       "compartment-id": "ocid1.compartment.oc1..aaaaaaaayyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy",
#       "display-name": "PeopleSoftAppSubnet",
#       "id": "ocid1.subnet.oc1.phx.aaaaaaaaag2xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
#       "lifecycle-state": "AVAILABLE",
#       ...
#     }
#   ]
# }

Make note of your VCN OCID (e.g., `ocid1.vcn.oc1.phx.aaaaaaaaae3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx`) and the target private subnet OCID (e.g., `ocid1.subnet.oc1.phx.aaaaaaaaag2xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx`).

Create or Configure a Network Security Group (NSG)

NSGs offer more granular control than Security Lists as they allow you to define security rules that apply only to specific resources (like your ATP private endpoint) within a subnet, rather than the entire subnet. We will create an NSG and add an ingress rule to allow TCP traffic on port 1522 from your PeopleSoft application server subnet (e.g., `10.0.2.0/24`).


# 1. Create a Network Security Group for the ATP private endpoint
oci network nsg create \
    --compartment-id ocid1.compartment.oc1..aaaaaaaayyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy \
    --vcn-id ocid1.vcn.oc1.phx.aaaaaaaaae3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx \
    --display-name "ATP_Private_Endpoint_NSG"

# Example output (truncated, get the NSG ID from here)
# {
#   "data": {
#     "compartment-id": "ocid1.compartment.oc1..aaaaaaaayyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy",
#     "display-name": "ATP_Private_Endpoint_NSG",
#     "id": "ocid1.networksecuritygroup.oc1.phx.aaaaaaaaaq1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
#     "lifecycle-state": "AVAILABLE",
#     "vcn-id": "ocid1.vcn.oc1.phx.aaaaaaaaae3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
#     ...
#   }
# }

# Make note of the NSG OCID: ocid1.networksecuritygroup.oc1.phx.aaaaaaaaaq1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

# 2. Add an ingress rule to the NSG
# This rule allows TCP traffic on port 1522 from the PeopleSoft application subnet (10.0.2.0/24)
oci network nsg rule add \
    --nsg-id ocid1.networksecuritygroup.oc1.phx.aaaaaaaaaq1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx \
    --ingress-security-rules '[
        {
            "protocol": "6",
            "source": "10.0.2.0/24",
            "sourceType": "CIDR_BLOCK",
            "tcpOptions": {
                "destinationPortRange": {
                    "max": 1522,
                    "min": 1522
                }
            }
        }
    ]'

# If your PeopleSoft application servers are in the same subnet as the ATP private endpoint (10.0.1.0/24),
# you would use that CIDR block as the source.

# You might also want to add an ingress rule for your jump host or admin workstation's IP
# to allow SQL client connections for testing and administration.
# Example for a single IP:
# oci network nsg rule add \
#     --nsg-id ocid1.networksecuritygroup.oc1.phx.aaaaaaaaaq1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx \
#     --ingress-security-rules '[
#         {
#             "protocol": "6",
#             "source": "203.0.113.42/32",  # Your admin workstation public IP
#             "sourceType": "CIDR_BLOCK",
#             "tcpOptions": {
#                 "destinationPortRange": {
#                     "max": 1522,
#                     "min": 1522
#                 }
#             }
#         }
#     ]'

2. Provision Oracle Autonomous Transaction Processing (ATP) with a Private Endpoint

Now, let's create the ATP instance, specifying that it should use a private endpoint within our chosen subnet and be associated with the NSG we just configured.


oci db autonomous-database create \
    --compartment-id ocid1.compartment.oc1..aaaaaaaayyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy \
    --display-name "PeopleSoftATPDB" \
    --db-name "PSFINPROD" \
    --admin-password 'MySecureP@ssw0rd123' \
    --cpu-core-count 2 \
    --data-storage-size-in-tbs 1 \
    --db-workload "OLTP" \
    --is-auto-scaling-enabled true \
    --is-private-endpoint true \
    --subnet-id ocid1.subnet.oc1.phx.aaaaaaaaag2xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx \
    --nsg-ids '["ocid1.networksecuritygroup.oc1.phx.aaaaaaaaaq1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]' \
    --freeform-tags '{"Environment": "Production", "Application": "PeopleSoft"}'

# Explanation of parameters:
# --compartment-id: Your compartment OCID.
# --display-name: A human-readable name for the database.
# --db-name: The actual database name (max 14 characters, alphanumeric, no special chars).
# --admin-password: The password for the ADMIN user. Store securely!
# --cpu-core-count: Number of CPU cores. Start small and scale up.
# --data-storage-size-in-tbs: Storage in TBs.
# --db-workload: OLTP for Transaction Processing. Can also be "DW" for Data Warehouse or "AJ" for JSON.
# --is-auto-scaling-enabled: Recommended to allow CPU and storage to scale automatically.
# --is-private-endpoint true: CRUCIAL for creating a private endpoint.
# --subnet-id: The OCID of the private subnet where the endpoint will be created.
# --nsg-ids: A JSON array of NSG OCIDs to apply to the private endpoint. This is where your security rules are enforced.
# --freeform-tags: Optional, but good for organization.

The provisioning process can take several minutes. You can check the status:


oci db autonomous-database get --autonomous-database-id ocid1.autonomousdatabase.oc1.phx.aaaaaaaaab3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Wait until `lifecycle-state` shows "AVAILABLE". Note down the `autonomous-database-id` from the output.

3. Download the Client Wallet

The client wallet contains the necessary connection information and security credentials (mTLS certificates) to securely connect to your ATP database. You will download a ZIP file containing `tnsnames.ora`, `sqlnet.ora`, and the mTLS certificates.


# First, identify your ATP database OCID if you don't have it handy
# oci db autonomous-database list --compartment-id ocid1.compartment.oc1..aaaaaaaayyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy --display-name "PeopleSoftATPDB"

# Download the wallet
oci db autonomous-database generate-wallet \
    --autonomous-database-id ocid1.autonomousdatabase.oc1.phx.aaaaaaaaab3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx \
    --file-base-name "Wallet_PSFINPROD" \
    --password 'MyWalletP@ssw0rd456' \
    --output-file "Wallet_PSFINPROD.zip"

# Explanation of parameters:
# --autonomous-database-id: The OCID of your ATP database.
# --file-base-name: A prefix for the files inside the wallet.
# --password: A password to protect the downloaded ZIP file. Choose a strong one!
# --output-file: The name of the ZIP file to save locally.

Unzip the downloaded file: `unzip Wallet_PSFINPROD.zip`. This will create a directory (e.g., `Wallet_PSFINPROD`) containing `tnsnames.ora`, `sqlnet.ora`, `cwallet.sso`, `ewallet.p12`, `keystore.jks`, and `truststore.jks`.

4. Configure Connectivity from a Client (PeopleSoft Application Server)

Now, we'll configure a PeopleSoft application server (or any client host within the allowed subnet) to connect to the ATP database using the downloaded wallet. This involves placing the wallet files, setting environment variables, and configuring connection strings.

On the PeopleSoft Application Server:

Transfer the Wallet: Securely transfer the contents of the unzipped wallet directory (e.g., `Wallet_PSFINPROD`) to your PeopleSoft application server. A common location for Oracle client files on PeopleSoft servers is within the Oracle client installation directory, or a dedicated directory under `PS_HOME`.

For example, you might create a directory like `/$PS_HOME/atp_wallet` and place all the unzipped files there.
```
# Example commands on the PeopleSoft application server (Linux)
mkdir -p $PS_HOME/atp_wallet
# Use scp or sftp to transfer Wallet_PSFINPROD.zip from your local machine to the server
# Then on the server:
unzip Wallet_PSFINPROD.zip -d $PS_HOME/atp_wallet
        
```
Configure `TNS_ADMIN`: Set the `TNS_ADMIN` environment variable to point to the directory containing your wallet files. This tells the Oracle client where to find `tnsnames.ora` and `sqlnet.ora`.

For PeopleSoft, this variable should be set in the environment where the PeopleSoft processes (e.g., Application Server domains, Process Schedulers) are launched. This is often in the domain's `psappsrv.cfg` (for app servers) or a shell script that starts the domains.
```
# Example for a shell environment (e.g., in .bashrc or a startup script)
export TNS_ADMIN=$PS_HOME/atp_wallet

# For PeopleSoft Application Server configuration (psappsrv.cfg):
# [PSTOOLS]
# TNS_ADMIN=$PS_HOME/atp_wallet
# This might not be directly supported for TNS_ADMIN in older PeopleTools versions.
# The safest bet is to set it in the environment script that starts the domain.
        
```

Verify `sqlnet.ora` and `tnsnames.ora`: The `sqlnet.ora` file within the wallet directory should already be configured correctly by Oracle to use the wallet for mTLS. It will look something like this:


# sqlnet.ora content from ATP wallet
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY="/path/to/your/wallet")))
SQLNET.AUTHENTICATION_SERVICES = (TCPS)
SSL_SERVER_DN_MATCH = yes

Important: Ensure the `DIRECTORY` path in `WALLET_LOCATION` is updated to the actual path on your PeopleSoft server (e.g., `/$PS_HOME/atp_wallet`).

The `tnsnames.ora` file will contain entries for various service levels (e.g., `_high`, `_medium`, `_low`). For PeopleSoft, `_medium` or `_high` are generally recommended for optimal performance and concurrency.


# Excerpt from tnsnames.ora (private endpoint entry)
PSFINPROD_high = (description= (address=(protocol=tcps)(port=1522)(host=psfinprod.atp.ocid1.autonomousdatabase.oc1.phx.aaaaaaaaab3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.adb.oraclecloud.com))(connect_data=(service_name=psfinprod_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))
PSFINPROD_medium = (description= (address=(protocol=tcps)(port=1522)(host=psfinprod.atp.ocid1.autonomousdatabase.oc1.phx.aaaaaaaaab3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.adb.oraclecloud.com))(connect_data=(service_name=psfinprod_medium.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))
PSFINPROD_low = (description= (address=(protocol=tcps)(port=1522)(host=psfinprod.atp.ocid1.autonomousdatabase.oc1.phx.aaaaaaaaab3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.adb.oraclecloud.com))(connect_data=(service_name=psfinprod_low.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))

Notice the `host` entry points to the private endpoint FQDN, and the `protocol` is `tcps` for secure TLS connection.

PeopleSoft Database Configuration: In your PeopleSoft configuration files (e.g., `psappsrv.cfg` for application server, `psprcs.cfg` for process scheduler), you would typically use the TNS alias for the database connection string.
```
# Excerpt from psappsrv.cfg or psprcs.cfg
[PSTOOLS]
DbName=PSFINPROD_medium  # Use the TNS alias from tnsnames.ora
DbServer=
        
```
Ensure that `DbServer` is left blank when using `DbName` with a TNS alias defined in `tnsnames.ora` and `TNS_ADMIN` is correctly set. PeopleSoft will then resolve `PSFINPROD_medium` using the `tnsnames.ora` file located via `TNS_ADMIN`.

5. Test Connectivity

From your PeopleSoft application server (or a designated jump host within the allowed subnet), test the connection using a SQL client.

Using SQLcl or SQL Plus:


# Ensure TNS_ADMIN is set correctly in your current shell
export TNS_ADMIN=$PS_HOME/atp_wallet

sqlcl /nolog

SQL> connect admin/MySecureP@ssw0rd123@PSFINPROD_medium
# Or if using sqlplus
sqlplus admin/MySecureP@ssw0rd123@PSFINPROD_medium

Connected.
SQL> select sysdate from dual;

SYSDATE
---------
28-MAY-24

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PSFINPROD                      READ WRITE NO

If you can connect successfully, your ATP database with a private endpoint and wallet-based authentication is correctly configured!

Using Python with `cx_Oracle` (for custom integrations/scripts):

For PeopleSoft, custom integrations or data extracts might use Python. Ensure `cx_Oracle` (or `python-oracledb` for newer versions) is installed.


pip install cx_Oracle

Create a Python script (e.g., `atp_connect.py`):


import os
import cx_Oracle

# Set TNS_ADMIN environment variable
# This can also be set globally in the shell before running the script
os.environ['TNS_ADMIN'] = '/path/to/your/wallet' # E.g., '/u01/app/psft/ps_home/atp_wallet'

try:
    # Connect using the TNS alias and ADMIN user
    connection = cx_Oracle.connect("admin", "MySecureP@ssw0rd123", "PSFINPROD_medium")
    cursor = connection.cursor()

    # Execute a simple query
    cursor.execute("SELECT sysdate FROM dual")
    for row in cursor:
        print(f"Current Date: {row[0]}")

    # Verify the database name
    cursor.execute("SELECT name FROM v$database")
    for row in cursor:
        print(f"Database Name: {row[0]}")

except cx_Oracle.Error as error:
    print(f"Error connecting to Oracle ATP: {error}")
finally:
    if 'connection' in locals() and connection:
        connection.close()
        print("Connection closed.")

Run the script:


python atp_connect.py

This should successfully connect and print the current date and database name, confirming programmatic access.

Security Considerations

Implementing ATP with private endpoints significantly enhances security, but several aspects require careful attention:

Network Isolation: The private endpoint ensures the database is not publicly accessible. This is the primary security benefit. Ensure your VCN is properly segmented, and only authorized subnets can reach the ATP private endpoint.
Network Security Groups (NSGs): NSGs are critical. Adhere to the principle of least privilege: only allow ingress traffic on port 1522 from the specific CIDR blocks of your PeopleSoft application servers, batch servers, and administrative jump hosts. Avoid broad `0.0.0.0/0` rules.
Client Wallet Protection: The wallet files contain sensitive cryptographic material.
- Password Protection: The wallet ZIP file is password-protected during download. Keep this password secure.
- File System Permissions: Ensure the unzipped wallet directory and its contents have restrictive file system permissions on the PeopleSoft application servers, allowing read access only to the necessary operating system users (e.g., the PeopleSoft user `psadm`).
- Secure Transfer: Always use secure methods (e.g., SCP, SFTP, OCI Object Storage pre-authenticated requests) to transfer the wallet files to your client machines.
IAM Policies: Restrict IAM policies for creating, managing, and downloading wallets for Autonomous Databases. Only authorized administrators should have these permissions.
Database User Management: Beyond the `ADMIN` user, create specific database users for your PeopleSoft application with the minimum necessary privileges. Avoid using `ADMIN` for application connections. Implement strong password policies and regularly rotate passwords for all database users.
Auditing: Leverage ATP's built-in auditing capabilities to monitor database access and activities. Integrate with OCI Logging and Monitoring services for centralized log analysis and anomaly detection.
No Public IP Exposure: Confirm that your PeopleSoft application servers themselves are not directly exposed to the public internet if they are meant to be internal. Use a load balancer or bastion host for external access if required, always securing the path to the application and database.

Best Practices

To maximize the benefits and security of your ATP with private endpoint deployment for PeopleSoft:

Dedicated Subnet for ATP: Consider placing your ATP private endpoints in a dedicated private subnet separate from your application servers. This allows for clear network segmentation and simplifies NSG rules.
Use NSGs over Security Lists: NSGs provide more granular control by allowing you to associate security rules directly with the ATP private endpoint, rather than the entire subnet. This makes managing security rules

Secure Oracle ATP with Private Endpoint and Wallet