ORDS Account Management Quickstart

Overview

This document provides practical guidance for managing key Oracle REST Data Services (ORDS) accounts, particularly ORDS_PUBLIC_USER and ORDS-enabled schema users.

• The ORDS_PUBLIC_USER is a specific account that enables the user to proxy to the relevant Oracle REST Data Services-enabled schema.
• The ORDS_METADATA is the owner of the PL/SQL packages used for implementing many Oracle REST Data Services capabilities. It is not accessed directly by Oracle REST Data Services; the Oracle REST Data Services application never creates a connection to the ORDS_METADATA schema.

---

• Checking ORDS_PUBLIC_USER status

/* check initial status */
select 
	username,
	account_status,
	authentication_type,
	profile,
	created,
	expiry_date,
	password_change_date
from 
	dba_users
where 
	username = upper('ords_public_user');

• Locking and expiring ORDS_PUBLIC_USER

/* lock and expire to mimic encountered issue */
alter user ords_public_user account lock;
alter user ords_public_user password expire;

• Checking ORDS_PUBLIC_USER status after locking and expiring

/* check status; should show expired & locked */
select 
	username,
	account_status,
	authentication_type,
	profile,
	created,
	expiry_date,
	password_change_date
from 
	dba_users
where 
	username = upper('ords_public_user');

• Unlock ORDS_PUBLIC_USER at the database and set new password

/* unlock with old password then modify to new password */
alter user ords_public_user identified by Oradoc_db1 account unlock;
alter user ords_public_user identified by welcome123;

• On the system hosting ORDS, run the following commands for interactive experience
  • Modify the config to have the wallet use the new password
  • Interactive password setting example

/home/oracle/ords/bin/ords --config /home/oracle/ords_config config secret db.password

• On the system hosting ORDS, run the following commands for non-interactive experience
  • Modify the config to have the wallet use the new password
  • Non-interactive password setting example

# Use stdin from file to avoid mistyping
cat <<'EOF' > /tmp/newORDS.txt
welcome123
EOF

# Set the new password
/home/oracle/ords/bin/ords --config /home/oracle/ords_config config secret --password-stdin db.password < /tmp/newORDS.txt

rm /tmp/newORDS.txt

# Restart the ORDS process
/home/oracle/ords/bin/ords --config /home/oracle/ords_config serve >& /tmp/ords.log &

• Check status with ORDS_PUBLIC_USER showing as OPEN

select 
	username,
	account_status,
	authentication_type,
	profile,
	created,
	expiry_date,
	password_change_date
from 
	dba_users
where 
	username = upper('ords_public_user');

• ORDS_PUBLIC_USER should now be unlocked and using the newly defined password

---

Resetting password ORDS enabled user schema

• User level that is enabled via ORDS enable schema package

exec ords.enable_schema(p_schema=>'&&new_username');

• Checking to ensure user schema is ORDS enabled

select 
    pattern as url_pattern,
    status as ords_status
from dba_ords_schemas

• Checking user account status

select 
	username,
	account_status,
	authentication_type,
	profile,
	created,
	expiry_date,
	password_change_date
from 
	dba_users
where 
	username = upper('tim');

• Locking and expiring specific user account

alter user tim account lock;
alter user tim password expire;

• Resetting user account with new password

alter user tim identified by timmytime account unlock;
alter user tim identified by tim account unlock;

• User account can now login to ORDS enable utilities

---

Summary

This guide is used as an example and should be modified per your unique requirements.

---

Workflows

• ORDS_PUBLIC_USER flow

flowchart TD
    A[Start: Check ORDS_PUBLIC_USER Status] --> B{Status?}
    B -->|OPEN| Z[No action needed]
    B -->|LOCKED or EXPIRED| C[Alter user: unlock and reset password]
    C --> D[Update ORDS Config with new password]
    D --> E{Mode?}
    E -->|Interactive| F[Run: ords config secret db.password]
    E -->|Non-Interactive| G[Use stdin file with ords config secret --password-stdin]
    F --> H[Restart ORDS process]
    G --> H[Restart ORDS process]
    H --> I[Re-check ORDS_PUBLIC_USER status]
    I --> J{Status = OPEN?}
    J -->|Yes| K[Account ready and usable]
    J -->|No| L[Troubleshoot further]
    K --> M[End]
    L --> M[End]

Loading

• ORDS Enabled User

flowchart TD
    A[Start: Enable ORDS Schema] --> B[Run: exec ords.enable_schema]
    B --> C[Check User Status in DBA_USERS]
    C --> D{Status?}
    D -->|OPEN| Z[No action needed]
    D -->|LOCKED or EXPIRED| E[Reset password with ALTER USER ... IDENTIFIED BY ... UNLOCK]
    E --> F[Re-check User Status in DBA_USERS]
    F --> G{Status = OPEN?}
    G -->|Yes| H[Schema ready for ORDS access]
    G -->|No| I[Troubleshoot further]
    H --> J[End]
    I --> J[End]

Loading

---

Question/comments/concerns

• Please feel free to contact Matt DeMarco

---

Appendix

Associated documentation:

• ORDS Installation and Configuration Guide
• ORDS Database Users
• ORDS Configuration Files
• Jeff Smith blog post