How to Check Password Lock Time in Oracle

  • By
  • Published
  • Posted in Tips
  • 12 mins read

Oracle is a widely used database management system that offers robust security features. One of these features is the password lock time, which is meant to control the amount of time that a user’s account remains locked after several unsuccessful login attempts. This is important for maintaining security and preventing unauthorized access to the database. As a technical blogger, I find it essential to educate users on how to check the password lock time in Oracle – this will allow users to identify and troubleshoot issues related to locked accounts quickly.

In this article, we will look at the various methods involved in checking password lock time in Oracle. We will also explore some of the reasons users may experience difficulties checking password lock time and offer solutions to fix these issues.

Video Tutorial:

What’s Needed

To check the password lock time in Oracle, you will need the following:

  • An Oracle database instance, hosted either on-premise or in the cloud.
  • Access to an account with DBA privileges to be able to execute necessary SQL queries.
  • Basic knowledge of SQL – While the steps involved are simple, understanding the language will enable you to make queries more efficiently and troubleshoot any issues that arise.

What Requires Your Focus?

Three main areas require special attention when checking password lock time in Oracle:

  • Executing SQL queries – you should be familiar with basic SQL commands to find the relevant information.
  • Identifying user accounts – you will need to determine the specific user accounts that have been locked, whether temporarily or permanently, to check the password lock time.
  • Building scripts – building scripts enables automation, making it easy to check password lock time regularly.

Methods to Check Password Lock Time in Oracle

Now that you have the prerequisites in place, let’s explore some of the popular methods used to check password lock time in Oracle.

Method 1: Using the SYS.USER$ table

This method involves using the SYS.USER$ table, which stores information about all users in the database. Specifically, we can retrieve information about the lock time from the FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME fields.

Steps:

  1. Log in to SQL*Plus with DBA credentials.
  2. Run the command below to display a list of all users in the database and their password status:

    SELECT * FROM SYS.USER$ WHERE ACCOUNT_STATUS = ‘LOCKED’;
  3. Note the USERNAME and PASSWORD_LOCK_TIME columns. The PASSWORD_LOCK_TIME column contains the amount of time remaining before the account is unlocked.

Pros:

  • This method is simple and straightforward – it does not require any external utilities, just SQL queries.
  • The method is faster than checking the password lock time using the Oracle Enterprise Manager as you don’t need to navigate through the user interface.

Cons:

  • Manual execution of SQL queries can be time-consuming.
  • There is a possibility of syntax errors or incorrect execution of the queries, especially for non-experts.

Method 2: Using the Oracle Enterprise Manager

Oracle Enterprise Manager (OEM) is a web-based interface that facilitates the management of Oracle databases. This method involves using OEM in combination with SQL queries to check the password lock time.

Steps:

  1. Connect to the Oracle Enterprise Manager console.
  2. Select the target database.
  3. Navigate to the ‘Security’ tab and select the ‘Users’ option.
  4. Select the user account that you wish to check for password lockout details.
  5. On the user account page, navigate to the ‘Profile’ tab.
  6. The ‘Lock Time’ column will display the amount of time remaining before the account is unlocked.

Pros:

  • The method is user-friendly as it involves no command-line interface.
  • It’s quicker to check password lock time as the information is accessed immediately once the user account is selected.

Cons:

  • The OEM interface may not be readily available for all users, especially when accessing the database remotely.
  • If the user list is long, it may take longer to load the users’ page and locate the specific user account that you’re looking for.

Method 3: Using SQL Snippet Scripts

Incorporating SQL Snippet Scripts can be a powerful way of automating password lock time checks. In this method, we will leverage scheduled SQL scripts that query the Oracle database at set intervals, looking for locked accounts and lock time details.

Here are the steps involved:

Steps:

  1. Create a new SQL file using SQL editor.
  2. Add the following code:
    SELECT USERNAME, ACCOUNT_STATUS, TO_CHAR(PASSWORD_LOCK_TIME, 'yyyy-mm-dd hh24:mi:ss') PASSWORD_LOCK_TIME
FROM DBA_USERS
WHERE ACCOUNT_STATUS ='LOCKED';
  3. Save the SQL script file.
  4. Open command prompt and type ‘sqlplus username/password @path/of/sql/file’ and hit enter.
  5. Note the USERNAME and PASSWORD_LOCK_TIME columns. The PASSWORD_LOCK_TIME column contains the amount of time remaining before the account is unlocked.

Pros:

  • SQL Snippet scripts can automate the process of checking password lock time.
  • The timed script saves time and offers a reliable way of keeping up with security issues.

Cons:

  • The initial setup requires time and effort to determine SQL Snippet Script components with no errors.
  • In the event of changes to Oracle database parameters, the SQL Snippet may require regular updating to avoid code-breaking errors.

Method 4: Using Oracle Audit Trail

Oracle Audit Trail is a built-in database feature designed to log specific database events and retain the data for later analysis. This method involves using the audit trail to check the password lock time for a user.

Steps:

  1. Log in to SQL*Plus with DBA credentials.
  2. Use this command to return a list of all failed login attempts for all users on the database:
    SELECT 
USERNAME, 
COUNT(*) 
FROM AUD$ 
WHERE ACTION# = 101 
AND RETURNCODE != 0 
GROUP BY USERNAME;
  3. Use the following command to show the last locked time of each user:
    SELECT 
USERNAME, 
MAX(NTIMESTAMP#) 
FROM AUD$ 
WHERE ACTION# = 107 
AND RETURNCODE > 0 
GROUP BY USERNAME

Pros:

  • The method is useful when trying to identify the cause of an account lockout as it logs all failed logins.
  • The audit trail functionality allows for the tracking of various database activities beyond password lock time.

Cons:

  • The method can generate a considerable amount of data, which can be overwhelming when trying to pinpoint specific details about password lock time.
  • There is a possibility of the audit trail being disabled, which would render this method unusable.

Why Can’t I Check Password Lock Time in Oracle?

Despite Oracle’s robust security, sometimes users still encounter issues when attempting to check the password lock time. Here are some of the reasons you may be unable to check password lock times, and how to resolve them:

  • Insufficient privileges: To check password lock time, you need to log in with a user account that has DBA privileges to run SQL queries that access important database data. If you don’t have these privileges, request them from your database admin.
  • Locked out of Oracle account: If you are locked out of your Oracle account, access to password lock time details will be unavailable. If you don’t know the password to your account or are locked out, contact your database administrator.
  • Incorrect or incomplete SQL syntax: while performing SQL queries, syntax errors can occur, and queries that do not run the exact syntax can render results incorrect or not run at all. Review and correct SQL queries with expert advice if unsure.

Implications and Recommendations

Oracle database lockouts can have severe consequences such as the disruption of business operations, data breaches, and financial damage. To avoid these issues, setting up password lock time is critical. Similarly, regular checks on the password lock time prevent users from being locked out and ensures enhanced security. For maximum benefit of these measure an IT professional with expertise in Oracle databases can help with the configuration of relevant Oracle settings, password best practices, and automation script setup.

5 FAQs About Checking Password Lock Time in Oracle

Q1: How long does Oracle lock an account when the maximum number of failed logins is reached?

A1: Oracle account lockout depends on several parameters the system administrator defines. The duration can be any value between permanent or 1 – 99999 days. You can learn the lockout duration from the password lock time value displayed on the PASSWORD_LOCK_TIME column.

Q2: Is it possible to reset the password lock time manually?

A2: Yes, you can reset the password lock time manually using ALTER PROFILE or ALTER USER commands.

Q3: How can I check if an account has been permanently locked?

A3: When an account has been permanently locked, its ACCOUNT_STATUS column value will be set to "LOCKED(TIMED)".

Q4: Is the password lock time applied only for failed login attempts for a user?

A4: The password lock time is applied when a user makes a specific number of failed login attempts with an incorrect password within a specified time.

Q5: Can I unlock an account without knowing the password?

A5: Yes, you can unlock an account without knowing the password. You can achieve this using the ALTER USER command to revert the value of the FAILED_LOGIN_ATTEMPTS column to 0. However, this can only be done when the account is temporarily locked, and the password is not expired.

In Conclusion

Knowing how to check password lock time in Oracle is vital for troubleshooting and maintaining security in an Oracle database. The methods we’ve outlined in this article are simple and easy to execute, each with its unique advantages and disadvantages. If you are experiencing any challenges in checking password lock time and resolving the issue, consult a professional with extensive experience in Oracle databases for guidance, install monitoring scripts and test frequently to assess password vulnerability and tolerance in your database.