Locking and unlocking users is a common and important task for an Oracle DBA. User accounts may need to be locked for security reasons, due to multiple failed login attempts, or when an employee leaves the organization. In this blog, we will understand why user accounts get locked, how to check user status, and how to lock and unlock a user in Oracle Database using simple commands.

Why Do We Lock a User in Oracle?

Some common real-time reasons include:

• Security reasons
• User has left the organization
• Temporary access restriction
• Multiple failed login attempts
• Maintenance or audit activities

Step 1: Check User Account Status

Before taking any action, always check the current status of the user account.

SELECT username, account_status
FROM dba_users;

Possible Account Status Values

• OPEN – User account is active
• LOCKED – User account is locked manually
• EXPIRED & LOCKED – Password expired and account locked

Step 2: Lock a User Account

To lock a user account, use the following command:

ALTER USER test ACCOUNT LOCK;

Step 3: Verify User Is Locked

After locking the user, verify the status again:

SELECT username, account_status
FROM dba_users

Step 4: Unlock a User Account

ALTER USER test ACCOUNT UNLOCK;

Step 5: Verify User Is Unlocked

SELECT username, account_status
FROM dba_users
WHERE username = 'TEST'

Optional: Unlock User and Reset Password Together

ALTER USER test IDENTIFIED BY Test@123 ACCOUNT UNLOCK;

Common Real-Time Scenario: Auto Account Lock

Oracle can automatically lock a user if the number of failed login attempts exceeds the limit defined in the profile.

To check profile settings:

SELECT profile, resource_name, limit
FROM dba_profiles
WHERE resource_name = 'FAILED_LOGIN_ATTEMPTS';