What best practices can I use so that the master user account of my Amazon RDS for Oracle instance doesn't get locked?

Resolution

Identify the root cause

A common reason that the master user account locks is because you configured the following custom parameters:

• FAILED_LOGIN_ATTEMPTS: The account locks when the number of consecutive failed login attempts exceeds the value that's set for this parameter.
• PASSWORD_LIFE_TIME: The password usage duration exceeds the value that's set for this parameter, so you can't log in with the password. The account is then marked as expired.
  Note: If you set a PASSWORD_GRACE_TIME value and you don't change the password within the grace period, then the password expires and further connections are rejected.
• INACTIVE_ACCOUNT_TIME: The master account locks when you don't use it to log in to the database for the number of consecutive days that's set for this parameter.
  Note: The minimum value for this parameter is 15 days. There's no maximum value.

For more information, see Create profile on the Oracle website.

To identify why the account is locked, run the following command to check the master account's current profile settings:

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 scenarios

FAILED_LOGIN_ATTEMPTS parameter

You create a profile with the FAILED_LOGIN_ATTEMPTS parameter that allows only one incorrect password input. You then use the wrong password to log in to the account, and receive the following error:

"ERROR:
ORA-01017: invalid username/password; logon denied."

You try to log in again. But because the FAILED_LOGIN_ATTEMPTS parameter is set to 1, you receive the following error:

"ERROR:
ORA-28000: The account is locked."

PASSWORD_LIFE_TIME parameter

You use the PASSWORD_LIFE_TIME parameter in your master account profile to limit the password usage duration. You then exceed the value that's set for this parameter, and receive the following error:

"ERROR:
ORA-28001: the password has expired."

Unlock the account

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshoot AWS CLI errors. Also, make sure that you're using the most recent AWS CLI version.

To unlock your account, use the Amazon RDS console, AWS CLI, or a different database account.

Amazon RDS console or AWS CLI

First, reset the admin user password for your DB instance. Then, you can unlock and reopen the account.

A different database account

If you have another database account with the ALTER USER privilege, then you can use this account to unlock the master user account.

To identify accounts with the ALTER USER privilege, run the following command:

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

Example output:

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

To identify accounts that grant ALTER USER privilege through a role, run the following command:

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'

Example output:

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

Note: Because the example is an RDS internal account, RDSADMIN appears in the output. It doesn't appear in your output.

To unlock the account, complete the following steps:

1. Run the following command to connect to the database with the account that has ALTER USER privilege:

   -bash-4.2$ sqlplus EXAMPLE-USER@oraSQL*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

   Note: Replace EXAMPLE-USER with the account that has ALTER USER privilege.

2. Run the following command to unlock the master user account:

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

   If the password is expired, then run the following command to reset the password:

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

3. To disconnect from the database and exit from SQL*Plus, run the following command:

   SQL> exitDisconnected from Oracle Database 19c 
   Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.12.0.0.0

   After you log in with the master user account, you receive the following message:

   bash-4.2$ sqlplus EXAMPLE-MASTER-USERNAME@oraSQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 14 13:36:36 2021Version 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:00Connected to:
   Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
   Version 19.12.0.0.0SQL> SHOW USER;USER is "EXAMPLE-MASTER-USERNAME"

You can't reset the password for your master account

Your master account might be blocked from a password reset because you set the PASSWORD_REUSE_MAX and PASSWORD_VERIFY_FUNCTION parameters.

PASSWORD_REUSE_MAX

If you use the same password more than the value that you set for the PASSWORD_REUSE_MAX parameter, then you receive the following error:

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

For more information about the latest events for your RDS instance, see Viewing Amazon RDS events.

To resolve this issue, reset the password for your master account to one that you you've never used for the Amazon RDS console. If you have another database account with the ALTER USER privilege, then you can also change the password from that account. To reuse the password without restriction, update the PASSWORD_REUSE_MAX parameter of the master account's profile to UNLIMITED.

PASSWORD_VERIFY_FUNCTION

The PASSWORD_VERIFY_FUNCTION parameter verifies strict password policies, such as the minimum length of the password or the requirement to use the minimum number of characters. If you try to reset the password and the password doesn't meet the defined rules, then you receive the following error:

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

To resolve this issue, be sure that the new password meets the rules that you defined in your PASSOWRD_VERIFY_FUNCTION parameter.

Use the DEFAULT profile so that the master user account doesn't get locked

If your organization security policies allow, use the DEFAULT profile instead of a custom profile. The DEFAULT profile provides your master user account with unlimited failed login attempts. Also, the password of the master user account never expires or is deactivated because of inactivity. However, it's a best practice to periodically reset the password on the Amazon RDS console.

To view the limits of the DEFAULT profile, run the following command:

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'
   );

Example output:

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.

To check the current profile of your master account, run the following command:

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

To change the profile of your master account to DEFAULT, run the following command:

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

Note: If you modified your DEFAULT profile password parameters that might affect login, then reset the DEFAULT profile to factory settings. To reset to factory settings, run 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 you to reset the master account password with restricted settings, then use the following best practices:

• Use the Amazon RDS console to periodically reset the password before it expires.
• If you have a PASSWORD_VERIFY_FUNCTION parameter 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 parameter so that the account remains activated.
• Use Secrets Manager to manage master user passwords for your DB instances and Multi-AZ DB clusters.

Track failed login attempts

To track login attempts that aren't valid, modify the Oracle initialization AUDIT_TRAIL parameter to db in the DB instance's custom parameter group. For more information, see AUDIT_TRAIL on the Oracle website.

If your DB 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 new 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?

After you modify the parameter in the custom parameter group, you must reboot your DB instance for the RDS for Oracle database to take effect.

After the status of your RDS Instance becomes available, log in to the database with the master account or any account with the AUDIT_TRAIL privilege. If you didn't turn on auditing for unsuccessful login attempts, then run the following command to turn it on:

AUDIT SESSION WHENEVER NOT SUCCESSFUL;

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

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';

Note: To track the failed login attempts data for all of your database accounts, remove the EXAMPLE-USER from the WHERE clause in the preceding command.