OCI Database Passwords Should Be Rotated Periodically

1. Rotate the password in the actual database
2. Update the monitoring credential in OCI (preferably using an OCI Vault secret with rotation)

​

1. Rotate the password in the database itself

• Connect to the DB as a privileged user (e.g., SYS or admin).
• Run a password change:
  • For Oracle DB:
    ALTER USER monitoring_user IDENTIFIED BY "NewStrongPassword!";
    
• Confirm login works with the new password.

​

2. Store the credential in OCI Vault (recommended)

1. In OCI Console, go to Identity & Security → Vault.
2. Select your Vault (or create one if needed).
3. Under the vault, go to Secrets → Create Secret:
   • Name: e.g. db-monitoring-password-<dbname>.
   • Secret type: Plaintext.
   • Secret content: the new DB user password.
   • Click Create Secret.

• In the secret’s details page, choose Configure Rotation.
• Set:
  • Rotation interval (e.g., 30 days, 60 days).
  • A rotation function (OCI Function) if you want fully automatic rotation (function changes DB password and updates secret).
• Save.

​

3. Update Database Monitoring credentials in OCI

​

A. For managed Oracle Databases (Database Management)

1. In OCI Console, go to Observability & Management → Database Management → Managed Databases.
2. Click your database that is being monitored.
3. In the DB Management page, look for Administration → Credentials (or Database Credentials tab, depending on UI).
4. Find the credential used for monitoring (often labeled as “DB Management credential”, “Monitoring credential”, or similar).
5. Click Edit / Update Credential (or Create Credential if none exists):
   • User name: the monitoring user in the database.
   • Password source:
     • Preferred: select Vault Secret and choose the secret you created above.
     • Alternative: directly provide the new password (less secure; you’ll need to edit this manually each rotation).
6. Save/Update the credential.

• Open Performance Hub or Metrics; confirm data is loading.
• If there’s a Test Connection button for the credential, use it.

​

B. For External Databases (if applicable)

1. Go to Observability & Management → Database Management → External Databases.
2. Select the specific external DB.
3. Go to Associated Credentials / Database Credentials.
4. Update the monitoring credential the same way:
   • Set the username.
   • Use the Vault secret (recommended) or direct password.
5. Save and verify connectivity.

​

4. Establish a rotation process (policy)

• If using Vault with automated rotation:
  • Ensure the rotation function updates both:
    1. The DB user password in the database.
    2. The secret value in OCI Vault.
• If rotating manually:
  • Create a schedule (e.g., every 60–90 days):
    1. Change DB password.
    2. Update Vault secret (or OCI console credential) immediately.
    3. Validate Database Management connectivity.

• Your database is already onboarded to OCI Database Management (Database Monitoring).
• You have a database user (monitoring user) whose credentials are stored in OCI Database Management.
• You can use the OCI CLI with appropriate permissions.

​

1. Identify the managed database and current DB credential

1. List all managed databases:

oci db-management managed-database list

2. Note the id of the target database (call it MANAGED_DB_ID).
3. List existing DB credentials for that managed database:

oci db-management database-credentials list \
  --managed-database-id MANAGED_DB_ID

​

2. Rotate the password inside the database

ALTER USER monitoring_user IDENTIFIED BY "NewStr0ngP@ssw0rd!";

​

3. Update the password stored in OCI (Database Management credential)

1. Put the new password into a local file (avoid shell history):

read -s NEW_PWD
echo -n "$NEW_PWD" > /tmp/new_db_pwd.txt
chmod 600 /tmp/new_db_pwd.txt
unset NEW_PWD

2. Update the database credential (replace placeholders):

• MANAGED_DB_ID – from step 1.
• CRED_NAME – name of the existing credential (or use its ID).
• DB_USER – database username (e.g. monitoring_user).

oci db-management database-credentials update \
  --managed-database-id MANAGED_DB_ID \
  --credential-name CRED_NAME \
  --username DB_USER \
  --password file:///tmp/new_db_pwd.txt

oci db-management database-credentials update --help

3. Clean up the temporary file:

shred -u /tmp/new_db_pwd.txt

​

4. Verify Database Monitoring is still working

1. Test a Database Management connection:

oci db-management managed-database get \
  --managed-database-id MANAGED_DB_ID

2. In the OCI Console (Database Management → Databases → [your DB]) confirm:

• Metrics are being collected.
• Performance/monitoring pages load without authentication errors.

​

5. Automate periodic rotation (optional but recommended)

1. Store DB credentials in OCI Vault (recommended).
2. Create a script that:
   • Generates a new strong password.
   • Connects to DB and runs ALTER USER ... IDENTIFIED BY ....
   • Updates the Vault secret (if used).
   • Calls oci db-management database-credentials update with the new password.
3. Run the script on a schedule (cron, OCI DevOps pipeline, or external scheduler).

0 2 1 * * /usr/local/bin/rotate_oci_db_monitoring_pwd.sh >> /var/log/oci-db-rotation.log 2>&1

​

1. High-level approach

1. Store DB passwords in OCI Vault (never hardcode them).
2. Use Python + OCI SDK to:
   • Read the current password from Vault.
   • Connect to the database and check when passwords were last changed.
   • If older than your policy (e.g., 90 days), generate a new password.
   • Run ALTER USER in the DB to rotate the password.
   • Update the password in OCI Vault.
3. Run this Python script on a schedule (e.g., OCI Scheduled Task / Cron on Compute / Function).

​

2. Prerequisites

1. OCI SDK for Python

pip install oci cx_Oracle

2. OCI config
   Create ~/.oci/config or use instance principal. Example profile:

[DEFAULT]
user=ocid1.user.oc1..xxxx
fingerprint=xx:xx:xx:xx
key_file=/path/to/oci_api_key.pem
tenancy=ocid1.tenancy.oc1..xxxx
region=us-ashburn-1

3. Vault setup
   • Create a Vault, Key, and Secret in OCI.
   • Store the DB user password as a secret (e.g., db-admin-password-secret).
   • Give the script principal (user/instance) permissions to SECRET-READ and SECRET-UPDATE.
4. DB connectivity
   • Have connection details: host/port/service_name or wallet for Autonomous.
   • Open security lists/NSG for the machine running the script.

​

3. Core Python logic

• Reads current password from OCI Vault
• Connects to DB as a user (e.g., ADMIN)
• Checks password age using DBA_USERS
• Rotates password if older than MAX_AGE_DAYS
• Updates password in Vault

import oci
import cx_Oracle
import datetime
import random
import string
from base64 import b64decode, b64encode

# ========= CONFIG =========
OCI_PROFILE = "DEFAULT"
VAULT_SECRET_OCID = "ocid1.vaultsecret.oc1..xxxx"  # DB password secret
DB_USER = "APP_USER"
DB_ADMIN_USER = "ADMIN"        # or SYS AS SYSDBA if needed
MAX_AGE_DAYS = 90

# DB connectivity
DB_HOST = "mydb.subnet.vcn.oraclevcn.com"
DB_PORT = 1521
DB_SERVICE = "mydb_high"       # service name
DB_ADMIN_PASSWORD_SECRET_OCID = "ocid1.vaultsecret.oc1..yyyy"  # if admin pwd also in Vault

# ========= OCI CLIENTS =========
config = oci.config.from_file("~/.oci/config", OCI_PROFILE)
secrets_client = oci.secrets.SecretsClient(config)
kms_vault_client = oci.vault.VaultsClient(config)
secrets_mgmt_client = oci.secrets.SecretsManagementClient(config)

def get_secret_value(secret_ocid: str) -> str:
    secret_bundle = secrets_client.get_secret_bundle(secret_ocid).data
    content = secret_bundle.secret_bundle_content
    if isinstance(content, oci.secrets.models.Base64SecretBundleContentDetails):
        return b64decode(content.content).decode("utf-8")
    else:
        raise RuntimeError("Unsupported secret content type")

def update_secret_value(secret_ocid: str, new_value: str):
    encoded = b64encode(new_value.encode("utf-8")).decode("utf-8")
    details = oci.secrets.models.UpdateSecretDetails(
        secret_content=oci.secrets.models.Base64SecretContentDetails(
            content_type="BASE64",
            content=encoded
        )
    )
    secrets_mgmt_client.update_secret(secret_id=secret_ocid, update_secret_details=details)

def make_conn(user, password):
    dsn = cx_Oracle.makedsn(DB_HOST, DB_PORT, service_name=DB_SERVICE)
    return cx_Oracle.connect(user=user, password=password, dsn=dsn, encoding="UTF-8")

def get_password_age_days(conn, username):
    sql = """
        SELECT password_change_time
        FROM dba_users
        WHERE username = :uname
    """
    with conn.cursor() as cur:
        cur.execute(sql, uname=username.upper())
        row = cur.fetchone()
        if not row or not row[0]:
            return None
        # row[0] is a datetime
        delta = datetime.datetime.now(row[0].tzinfo) - row[0]
        return delta.days

def generate_password(length=20):
    chars = string.ascii_letters + string.digits + "!@#$%^&*()-_+=[]{}"
    return "".join(random.choice(chars) for _ in range(length))

def rotate_db_password():
    # Get admin password for DB
    admin_pwd = get_secret_value(DB_ADMIN_PASSWORD_SECRET_OCID)

    # Connect as admin to check password age
    with make_conn(DB_ADMIN_USER, admin_pwd) as admin_conn:
        age_days = get_password_age_days(admin_conn, DB_USER)
        if age_days is None:
            print(f"User {DB_USER} not found or password_change_time null.")
            return

        print(f"Password age for {DB_USER}: {age_days} days")
        if age_days < MAX_AGE_DAYS:
            print("Rotation not required.")
            return

        # Generate new password
        new_pwd = generate_password()

        # Rotate password in DB
        sql = f"ALTER USER {DB_USER} IDENTIFIED BY \"{new_pwd}\""
        with admin_conn.cursor() as cur:
            cur.execute(sql)
            admin_conn.commit()
        print(f"Password rotated in DB for {DB_USER}")

        # Update app user password secret in Vault
        update_secret_value(VAULT_SECRET_OCID, new_pwd)
        print("Updated password in OCI Vault")

if __name__ == "__main__":
    rotate_db_password()

• For Autonomous DB, you often use ADMIN and connect via wallet. Replace host/port/service with wallet tnsnames.ora and use dsn="db_alias" (after configuring TNS_ADMIN).
• Ensure DB user has privileges (ALTER USER via admin account).

​

4. Integrate with Monitoring

1. Schedule the script:
   • On a Compute instance using cron, or
   • As an OCI Function triggered by OCI Events + Scheduled or DevOps build pipeline.
2. Optional: send metrics/alerts:
   • After checking age, publish a custom metric to OCI Monitoring for "password_age_days".
   • Set an OCI alarm to notify (email/Slack) when age > threshold.

​

5. Minimal Monitoring-Only Variant

• Remove the ALTER USER and update_secret_value parts.
• Just:
  • Query DBA_USERS.password_change_time
  • Publish to OCI Monitoring or log to OCI Logging
  • Trigger Alarms if age > limit.

• Use the OCI Console or SQL*Plus/SQL Developer to:
  • Enforce password aging in the Oracle database profile (e.g., ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 90;).
  • Rotate application and admin user passwords manually or via an external secret manager/automation tool (e.g., OCI Vault + scripts), outside of Terraform.