Unexpiring the ORDS_PUBLIC_USER user for APEX

Unexpiring the ORDS_PUBLIC_USER user for APEX

Matt Mulvaney's photo
Matt Mulvaney
·

3 min read

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!

ordsorclapex