ORDS REST Account is Locked (HTTP Status Code: 570) but APEX is working?

I experienced a Account is Locked whilst calling a REST URL like this - however APEX works just fine.

I would unlock that User, but I dont know which one it is - the error message declined to mention it.

None of the usual suspects are locked

     COLUMN username FORMAT A32
     COLUMN account_status FORMAT A32
     select username, account_status from dba_users where username like 'APEX%' or username like 'ORDS%';

This will show something like

     USERNAME                         ACCOUNT_STATUS
     -------------------------------- --------------------------------
     APEX_LISTENER                    OPEN
     APEX_PUBLIC_USER                 OPEN
     APEX_REST_PUBLIC_USER            OPEN
     ORDS_METADATA                    OPEN
     ORDS_PUBLIC_USER                 OPEN
     APEX_230100                      LOCKED
     APEX_230200                      LOCKED

     7 rows selected

So you need to look at the ORDS log using something like this

 tail -f ords-20240607.log

In my case, I see the following

Caused by: oracle.dbtools.common.jdbc.ConnectionPoolConfigurationException: ORDS was unable 
to make a connection to the database. ORDS is not able to proxy to the target user because that 
user is locked. The connection pool named: |default|lo| had the following error(s): ORA-28000: 
The account is locked; login denied.

https://docs.oracle.com/error-help/db/ora-28000/
        at oracle.dbtools.common.jdbc.ConnectionPoolExceptions.from(ConnectionPoolExceptions.java:63)
        at oracle.dbtools.common.jdbc.ConnectionPoolExceptions.from(ConnectionPoolExceptions.java:69)
        at oracle.dbtools.common.jdbc.DataSourceConnection.getPooledConnection(DataSourceConnection.java:231)
        at oracle.dbtools.common.jdbc.DataSourceConnection.getPooledConnection(DataSourceConnection.java:189)
        at oracle.dbtools.common.jdbc.DataSourceConnection.getDefaultConnection(DataSourceConnection.java:144)
        at oracle.dbtools.common.jdbc.DataSourceConnection.getConnection(DataSourceConnection.java:58)
        at oracle.dbtools.jdbc.pools.JDBCSchemaTarget.getConnection(JDBCSchemaTarget.java:85)
        at oracle.dbtools.common.config.db.SchemaConnectionFactoryBase.inject(SchemaConnectionFactoryBase.java:70)
        at oracle.dbtools.url.mapping.db.PoolInjector.inject(PoolInjector.java:62)
        ... 82 more
Caused by: oracle.dbtools.common.ucp.ConnectionLabelingException: Error occurred when attempting to 
configure url: jdbc:oracle:thin:@//51dea82f3446:1521/FREEPDB1 with labels: 
{oracle.dbtools.jdbc.label.cleanup=RECYCLE, oracle.dbtools.jdbc.label.schema=BORNAUW}

The clue is at end of the log where it connects, specicall the shchema name

schema=BORNAUW

Now that the schema has been identified, run this to confirm it

     COLUMN username FORMAT A32
     COLUMN account_status FORMAT A32
     select username, account_status from dba_users where username = 'BORNAUW';

This returns

     USERNAME                         ACCOUNT_STATUS
     -------------------------------- --------------------------------
     BORNAUW                          LOCKED 

     1 row selected

Then just unlock it

ALTER USER BORNAUW ACCOUNT UNLOCK;

Try the REST URL again

It works!

ENJOY!

Typical Yorkshire view near Kirby Overblow - Visit Yorkshire!