How do I prevent the master user account of my Amazon RDS for Oracle DB instance from getting locked?

Resolution

The master user account in Amazon RDS is vital to monitoring, managing, and performing administrative tasks to operate your DB instance. This account is your first database account, and it includes the necessary administrative roles and privileges to maintain and operate your database. For the complete list of privileges and roles that the master user gets for each database engine, see Master user account privileges. If the master user account gets locked and you can't log in, your administration abilities are disrupted and your critical business operations might be affected. You can use the Amazon RDS console or a different database account to regain access to your master account.

Identify the root cause

One of the common reasons for the locking of the master user account is applying custom profile settings to the master account. Some of these settings include the following:

• FAILED_LOGIN_ATTEMPTS: When the number of consecutive failed attempts to log in to the master account exceeds the value set for this parameter, the account is locked.
• PASSWORD_LIFE_TIME: When the duration of using a certain password exceeds the value set for this parameter, you can't log in to the account using that password and the account is marked as expired. You must reset the password to log in again. If you set a value for PASSWORD_GRACE_TIME and the password isn't changed within the grace period, then the password expires and further connections are rejected.
• INACTIVE_ACCOUNT_TIME: If the master account doesn't log in to the database for the number of consecutive days defined by this parameter, then the account is locked. The minimum value for this parameter is 15 days. There is no maximum value.

For more information on password parameters, see Oracle documentation for password_parameters.

Run the following query to check the current profile settings of the master user account to identify why the account is locked:

SELECT
    profile,
    resource_name,
    limit
FROM
    dba_profiles
WHERE
    resource_type = 'PASSWORD'
    AND   profile IN (
        SELECT
            profile
        FROM
            dba_users
        WHERE
            username = 'EXAMPLE-MASTER-USERNAME'
    );

Examples:

Suppose that you create a profile with the FAILED_LOGIN_ATTEMPTS setting that allows only one incorrect password input.

SQL> CREATE PROFILE sec_profile LIMIT FAILED_LOGIN_ATTEMPTS 1;
Profile created.

You can apply this profile to the master user by running the following query:

SQL> ALTER USER EXAMPLE-MASTER-USERNAME PROFILE sec_profile;
User altered.

Disconnect from the database and exit from SQL*Plus:

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

Log in to the account with the incorrect password:

bash-4.2$ sqlplus EXAMPLE-MASTER-USERNAME@ora
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 14 13:16:33 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied

Further log ins aren't allowed because FAILED_LOGIN_ATTEMPTS is set to 1. Instead, you get the following error message:

bash-4.2$ sqlplus EXAMPLE-MASTER-USERNAME@ora
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 14 13:16:44 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
ERROR:
ORA-28000: The account is locked.

When the master account is limited by the PASSWORD_LIFE_TIME parameter, and the duration of using the same password exceeds the value set for this parameter, you see the below error message when you try to login:

-bash-4.2$ sqlplus EXAMPLE-MASTER-USERNAME@ora
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 14 20:14:03 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
ERROR:
ORA-28001: the password has expired

Unlock the master account

You can unlock the mater account using either of the following ways:

Using Amazon RDS Console or AWS Command Line Interface (AWS CLI)

By resetting the password, you can unlock the account and reopen the account after expiry. For more information, see How do I reset the master user password for my Amazon RDS DB instance to unlock master user?

Using a different database account

If you already have another database account (for example, EXAMPLE-USER) with the ALTER USER privilege, then you can use that account to unlock the master user account.

Identify accounts with the ALTER USER privilege by running the following query:

SELECT
    grantee,
    privilege
FROM
    dba_sys_privs
WHERE
    privilege = 'ALTER USER'
    AND grantee IN (
        SELECT
            username
        FROM
            dba_users
        WHERE
            oracle_maintained = 'N'
    );

The output looks similar to the following:

GRANTEE                                                                     PRIVILEGE
--------------------------------------------------------------------------------------------------------------------------------
EXAMPLE-USER                                                                ALTER USER

Run the following query to identify accounts for which the privilege is granted through a role:

SELECT DISTINCT
    dba_users.username
FROM
    dba_role_privs
    JOIN dba_users ON dba_role_privs.grantee = dba_users.username
                      AND granted_role IN (
        SELECT
            role
        FROM
            role_sys_privs
        WHERE
            privilege = 'ALTER USER'
    )
        AND dba_users.oracle_maintained = 'N'

The output looks similar to the following:

USERNAME
-----------------------------------------------------------------------------------
EXAMPLE-MASTER-USERNAME
RDSADMIN
USER1
EXAMPLE-USER

Note: Ignore RDSADMIN from the output because it's an RDS internal account.

Connect to the database using the account EXAMPLE-USER:

-bash-4.2$ sqlplus EXAMPLE-USER@ora
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 14 13:35:55 2021 Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Last Successful login time: Tue Sep 14 2021 13:33:23 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

Unlock the master user account by running the following query:

SQL> ALTER USER EXAMPLE-MASTER-USERNAME ACCOUNT UNLOCK;
User altered.

If the password is expired, reset the password by running the following query:

SQL> ALTER USER EXAMPLE-MASTER-USERNAME IDENTIFIED BY EXAMPLE-MASTER-USER-NEW-PWD;
User altered.

Disconnect from the database EXAMPLE-USER and exit from SQL*Plus:

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

You can successfully log in with the master user account. You get the following message:

bash-4.2$ sqlplus EXAMPLE-MASTER-USERNAME@ora
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 14 13:36:36 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Tue Sep 14 2021 13:35:12+00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> SHOW USER;
USER is "EXAMPLE-MASTER-USERNAME"

Unable to reset the password for your master account: Your master account might be blocked from resetting the password under certain situations:

Example 1:

If you use the same password for the master account more than the number of times defined by the PASSWORD_REUSE_MAX parameter, then you see the following event in the Amazon RDS console:

Unable to reset your password. Error information: ORA-28007: the password cannot be reused ORA-06512: at line 1

To see the latest events for your RDS instance, see Viewing Amazon RDS events.

To resolve this issue, reset the password for your master account to a new password wasn't used in the past using the Amazon RDS console. Also, if you have another database account with the ALTER USER privilege, then you can log in to that account and change the password. If allowed, update the PASSWORD_REUSE_MAX of the master account's profile to UNLIMITED so that you can reuse the password any number of times.

Example 2:

Suppose that the master account has the PASSWORD_VERIFY_FUNCTION parameter setting that verifies strict password policies, such as the minimum length of the password or the requirement to use the minimum number of characters. You might get the following error when you try to reset the password and the password doesn't meet the defined rules:

ORA-28003: password verification for the specified password failed.

To resolve this issue, be sure that the rules defined in your PASSOWRD_VERIFY_FUNCTION are met for the new password.

Prevent master user account from getting locked

If your organization security policies allow, avoid setting custom profiles for your RDS master user account and retain the DEFAULT profile. With the DEFAULT profile, your master user account is provided with unlimited failed login attempts. Also, the password of the master user account never expires or gets deactivated due to inactivity. However, it's a best practice to reset the password periodically using the Amazon RDS console.

You can view the limits of the DEFAULT profile by running the following query:

SELECT
   resource_name,
   limit
FROM
   dba_profiles
WHERE
   resource_type = 'PASSWORD'
   AND profile = 'DEFAULT'
   AND resource_name IN
   (
      'FAILED_LOGIN_ATTEMPTS',
      'PASSWORD_LIFE_TIME',
      'PASSWORD_VERIFY_FUNCTION',
      'INACTIVE_ACCOUNT_TIME',
      'PASSWORD_REUSE_TIME',
      'PASSWORD_REUSE_MAX'
   );

The output looks similar to the following:

RESOURCE_NAME                    LIMIT
-------------------------------- --------------------------------------------------------------------------------------------------------------------------------
FAILED_LOGIN_ATTEMPTS            UNLIMITED
PASSWORD_LIFE_TIME               UNLIMITED
PASSWORD_REUSE_TIME              UNLIMITED
PASSWORD_REUSE_MAX               UNLIMITED
PASSWORD_VERIFY_FUNCTION         NULL
INACTIVE_ACCOUNT_TIME            UNLIMITED

6 rows selected.

If your master account has a custom profile, then switch the account to use the DEFAULT profile.

Check the current profile of your master account by running the following query:

SELECT PROFILE FROM dba_users WHERE USERNAME = 'EXAMPLE-MASTER-USERNAME';

Change the profile of your master account to DEFAULT by running the following query:

SQL> ALTER USER EXAMPLE-MASTER-USERNAME PROFILE default;
User altered.

Note: If you have your DEFAULT profile password parameters modified, then reset the DEFAULT profile back to factory settings for parameters that might affect logging in. To do so, use the following command:

SQL> ALTER PROFILE default LIMIT
  failed_login_attempts UNLIMITED password_life_time UNLIMITED
  inactive_account_time UNLIMITED;
Profile altered.

If your organization has policies that require resetting the master account password with restricted settings, then be sure to follow the following best practices:

• Reset the password periodically using the Amazon RDS Console before it expires due to the PASSWORD_LIFE_TIME setting.
• If you have a PASSWORD_VERIFY_FUNCTION with strict requirements for password verification, adhere to these requirements when you set up the new password.
• Use your master user account based on the INACTIVE_ACCOUNT_TIME setting so that the account remains activated.

Track failed login attempts

You can track the invalid login attempts to your master account using the auditing feature in Oracle. You can do so by modifying the Oracle initialization parameter audit_trail to db for your RDS for Oracle instance. You can modify this parameter in the DB instance's custom parameter group.

If your RDS for Oracle instance uses a default parameter group, then you can't change the parameter values. You must create a new custom parameter group, set the parameter value, and attach the newly created parameter group to your instance. Then, reboot the instance to associate the new parameter group with the instance. For more information, see How do I modify the values of an Amazon RDS DB parameter group?

You must reboot your RDS instance after modifying the parameter in the custom parameter group. This is because the audit_trail parameter is static and requires rebooting the RDS for Oracle database to take effect. For more information, see Rebooting a DB instance.

After the status of your RDS Instance turns available, log in to the database using the master account or any account with the audit privilege. If you've not already configured auditing unsuccessful attempts, run the following command to turn on auditing for unsuccessful login attempts:

AUDIT SESSION WHENEVER NOT SUCCESSFUL;

Then, run the following query to track the unsuccessful or failed login attempts by your master account.

Note: You can track this data for any database account by removing the EXAMPLE-USERfrom the WHERE clause.

SELECT username,
       os_username,
       userhost,
       TO_CHAR(timestamp, 'MON-DD-YYYY HH24:MI:SS') what_time
FROM   dba_audit_trail
WHERE  returncode = 1017
       AND username = 'EXAMPLE-USER';

Oracle documentation for CREATE PROFILE

---