Unexpiring the ORDS_PUBLIC_USER user for APEX

I went to use my APEX+ORDS All in one Docker and noticed parts of APEX weren't working. Sometimes ORDS would throw an error, sometimes images wouldn't load. It was annoying

I found that the problem was that user ORDS_PUBLIC_USER had expired & I needed to fix that

Form a DB Connection

Since I use the Docker image, I have to connect this way.

1. Enter bash

     docker exec -it 23cfree /bin/bash
   

2. Open SQL*Plus with sqlplus / as sysdba

     sqlplus / as sysdba
   

   like this...

     bash-4.4$ sqlplus / as sysdba
   
     SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Wed Apr 5 13:27:32 2023 Version 23.2.0.0.0
   
     Copyright (c) 1982, 2023, Oracle. All rights reserved.
   
     Connected to: Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release Version 23.2.0.0.0
   

3. Set the PDB

     ALTER SESSION SET CONTAINER = FREEPDB1;
   

Finds & Unexpire the Users

1. Find the Expired Users

    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                 EXPIRED
    APEX_230100                      LOCKED
    APEX_230200                      LOCKED
   
    7 rows selected.
   

2. So we are going to fix ORDS_PUBLIC_USER as this has expired.

3. Adjust the username in the Declare section as appropriate & run the following

    DECLARE
      CONST_USERNAME CONSTANT VARCHAR2(128) := 
        'ORDS_PUBLIC_USER';
      v_unexpire_command VARCHAR2(4000);
    BEGIN
      EXECUTE IMMEDIATE 'ALTER USER ' || CONST_USERNAME || ' ACCOUNT UNLOCK';
   
      SELECT 'alter user ' || name || q'< identified by values '>' || spare4 || ';' || password || q'<'>'
       INTO v_unexpire_command 
      FROM sys.user$ WHERE name=CONST_USERNAME; 
      EXECUTE IMMEDIATE v_unexpire_command;
    END;
    /
   

   Credits to TuningSQL.com

4. Test the user has been unexpired

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

   We should now see

   
    USERNAME                         ACCOUNT_STATUS
    -------------------------------- --------------------------------
    APEX_LISTENER                    OPEN
    APEX_PUBLIC_USER                 OPEN
    APEX_REST_PUBLIC_USER            OPEN
    ORDS_PUBLIC_USER                 OPEN
    ORDS_METADATA                    OPEN
    APEX_230100                      LOCKED
    APEX_230200                      LOCKED
   
    7 rows selected.
   

Stop it happening again

Adjust the first line, to set the User you wish to assign to a non-expiring profile.

   DECLARE
      CONST_USERNAME constant varchar2(128) := 'ORDS_PUBLIC_USER';
      CONST_NEW_PROFILE_NAME constant varchar2(128) := 'NO_PASSWORD_EXPIRE';

      v_existing_profile varchar2(128);
      v_sql varchar2(32767);
    begin
      select du.profile into v_existing_profile
      from dba_users du
      where du.username = CONST_USERNAME;

      v_sql := 'create profile ' || 
                CONST_NEW_PROFILE_NAME || 
                ' limit ';      
      for v_row in (select dp.resource_name, dp.limit
                    from dba_profiles dp
                    where dp.profile = v_existing_profile
                      and dp.resource_name <> 'PASSWORD_LIFE_TIME')
      loop
        v_sql := v_sql || v_row.resource_name || ' ' || v_row.limit || ' ';
      end loop;

      v_sql := v_sql || 'PASSWORD_LIFE_TIME UNLIMITED';

      declare
        profile_already_exists exception;
        pragma exception_init(profile_already_exists, -2379);
      begin
        execute immediate v_sql;
      exception when profile_already_exists then 
        null;
      end;
      execute immediate 'alter user ' || CONST_USERNAME || ' profile ' || CONST_NEW_PROFILE_NAME;
    end;
/

1. Check the profile moved to the non expiring profile Credits to TuningSQL.com

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

   Its should have moved it to the new profile

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

ENJOY!

Whats the picture? It's John St in Harrogate one cold morning. Visit Yorkshire!