Tightened security in 20c for Oracle maintained accounts

Posted by

If you cannot wait for a fully autonomous offering, and you’ve jumped into the 20c preview release on Oracle Cloud, obviously the first thing you will probably be installing is Oracle Application Express.

Unlike autonomous, you’ll be installing it manually, which is a quick and easy process, and either in that installation or when adding ORDS later, you’ll be wanting to set the passwords for the public access accounts (typically APEX_PUBLIC_USER and APEX_REST_PUBLIC_USER).

Here’s what that looks like in Oracle Database 19c

SQL> show user
SQL> alter user APEX_PUBLIC_USER identified by MY_SECRET_PASSWORD;

User altered.

And here is what it will look like in Oracle Database 20c Smile

SQL> show user
SQL> alter user APEX_PUBLIC_USER identified by MY_SECRET_PASSWORD;
alter user APEX_PUBLIC_USER identified by MY_SECRET_PASSWORD
ERROR at line 1:
ORA-01031: insufficient privileges

Since Oracle 11, the control of user accounts has been improving, including controlling which accounts are explicitly related to the delivery of Oracle solutions (such as APEX, ORDS, Context, JVM and the like). In particular, the ORACLE_MAINTAINED column was added to DBA_USERS to let administrators know which accounts are managed by the database.


 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(128)
 USER_ID                                   NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(4000)
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 LOCAL_TEMP_TABLESPACE                              VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 PROFILE                                   NOT NULL VARCHAR2(128)
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(128)
 EXTERNAL_NAME                                      VARCHAR2(4000)
 PASSWORD_VERSIONS                                  VARCHAR2(17)
 EDITIONS_ENABLED                                   VARCHAR2(1)
 AUTHENTICATION_TYPE                                VARCHAR2(8)
 PROXY_ONLY_CONNECT                                 VARCHAR2(1)
 COMMON                                             VARCHAR2(3)
 LAST_LOGIN                                         TIMESTAMP(9) WITH TIME ZONE
 ORACLE_MAINTAINED                                  VARCHAR2(1)
 INHERITED                                          VARCHAR2(3)
 DEFAULT_COLLATION                                  VARCHAR2(100)
 IMPLICIT                                           VARCHAR2(3)
 ALL_SHARD                                          VARCHAR2(3)
 PASSWORD_CHANGE_DATE                               DATE

In 20c password management has been tightened for Oracle maintained accounts, of which the APEX accounts are included. Hence, to update their passwords, you’ll need to let the database know that you know you are tinkering with special accounts.

SQL> alter session set "_oracle_script" = true;

Session altered.

SQL> alter user APEX_PUBLIC_USER identified by MY_SECRET_PASSWORD;

User altered.

Then all that is left to do is to sit back and bask in the joy of Application Express on the latest version of the Oracle Database.

One comment

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.