Oracle Lock and Unlock User Accounts

Use ALTER USER john_doe ACCOUNT LOCK; to block new logins and ALTER USER john_doe ACCOUNT UNLOCK; to remove the lock. Locking preserves the schema and does not end existing sessions. Unlocking does not fix an expired password or a missing CREATE SESSION privilege.

These examples target a local user in the intended PDB. Connect to that PDB's service and confirm the container before changing the account:

SELECT SYS_CONTEXT('USERENV', 'CON_NAME') AS container_name FROM dual;

Database administrators frequently need to temporarily prevent a user from logging in without permanently removing the account or revoking its privileges. Oracle provides the ALTER USER ... ACCOUNT LOCK command for exactly this purpose. The account remains intact: passwords, privileges, and role assignments are all preserved, and the lock can be removed with a single unlock command.

Why Lock an Account Instead of Dropping It?

Locking is the preferred approach for temporary access suspension because nothing about the account changes except its login status. The user's password remains the same. Their granted privileges and roles are untouched. You do not need to re-create the account or reassign privileges when access is restored. Common scenarios include employee leave, security investigations, scheduled maintenance windows, and compliance holds.

Locking a User Account

You must connect as a user with the ALTER USER system privilege or as SYSDBA. From SQL*Plus:

sqlplus / as sysdba

Alternatively, connect using an authorized administration account; SQL*Plus prompts for its password:

sqlplus admin_username@pdb_service

To lock the account, execute the ALTER USER statement with the ACCOUNT LOCK clause:

ALTER USER username ACCOUNT LOCK;

Example:

ALTER USER john_doe ACCOUNT LOCK;

Once locked, any login attempt by that user will be rejected immediately. Existing active sessions are not terminated by the lock; they remain open until they disconnect naturally or are killed explicitly.

Verifying Account Status

Query DBA_USERS to confirm the lock and review related account metadata:

SELECT
 username,
 account_status,
 lock_date,
 expiry_date,
 profile
FROM dba_users
WHERE username = 'JOHN_DOE';

LOCKED can mean an explicit administrative lock or an automatic failed-login lock with PASSWORD_LOCK_TIME=UNLIMITED. LOCKED(TIMED) indicates a failed-login lock whose configured duration has not elapsed. Status can also combine expiry and locking, such as EXPIRED & LOCKED. Status alone does not prove who applied a lock; correlate it with audit records.

Unlocking a User Account

When access should be restored, unlock the account with the ACCOUNT UNLOCK clause:

ALTER USER username ACCOUNT UNLOCK;

Example:

ALTER USER john_doe ACCOUNT UNLOCK;

Unlocking removes the lock, but authentication, password expiry, and login privileges still apply. An expired password must be changed through a supported password-change flow.

Automatic Account Locking

Repeated failed authentication can trigger FAILED_LOGIN_ATTEMPTS. The profile's PASSWORD_LOCK_TIME determines whether the resulting lock is timed or indefinite. Password ageing is separate: expiry requires a password change; it is not itself an account lock.

Why does an account lock again after unlocking?

Check connection pools, scheduled jobs, and saved client credentials for an old password before repeatedly unlocking the account. Inspect the assigned profile below; a value of DEFAULT inherits the corresponding limit from the DEFAULT profile.

SELECT profile, resource_name, limit
FROM dba_profiles
WHERE profile IN ('DEFAULT',
                 (SELECT profile FROM dba_users WHERE username = 'JOHN_DOE'))
  AND resource_name IN ('FAILED_LOGIN_ATTEMPTS', 'PASSWORD_LOCK_TIME')
ORDER BY profile, resource_name;

Access to these dictionary views requires appropriate privileges.

Terminating Active Sessions After Locking

Locking an account does not disconnect users who are already logged in. To terminate an active session, first identify it in V$SESSION:

SELECT sid, serial#, username, status
FROM v$session
WHERE username = 'JOHN_DOE';

If termination is approved, an administrator with ALTER SYSTEM can substitute the actual SID and SERIAL# below. This is a single-instance example; RAC also requires the target instance. Termination interrupts work and rolls back uncommitted transactions.

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Password Expiry and Profiles Are Different Controls

Use account locking for suspension. Password expiry and profile limits serve different purposes and should not be treated as equivalent lockout mechanisms.

Password Expiry

Expiring a user's password forces them to change it at next login, effectively blocking access until the reset is completed by an administrator or the user themselves:

ALTER USER john_doe PASSWORD EXPIRE;

This is useful when you want to force a credential rotation rather than a full lockout.

Restrictive Profile Assignment

Resource-profile limits are not a substitute for ACCOUNT LOCK. Session resource limits depend on RESOURCE_LIMIT enforcement, and replacing a user's profile also changes other inherited settings. Keep the user's existing profile for a temporary account suspension; use the explicit lock/unlock commands instead.

Auditing Account Lock and Unlock Activity

Unified auditing provides fine-grained tracking of account management operations, and it is worth being precise about its current status rather than describing it as simply available and refined over time: starting with Oracle AI Database 26ai, traditional auditing, the older AUDIT statement; form without a named policy, is fully desupported. You cannot create new traditional audit settings or modify existing ones at all; unified audit policies are the only mechanism available for any new auditing configuration. To audit account lock and unlock activity, along with every other ALTER USER action, create a named unified audit policy:

CREATE AUDIT POLICY lock_unlock_policy
 ACTIONS ALTER USER;

AUDIT POLICY lock_unlock_policy;

Audit records are written to the unified audit trail and can be queried from UNIFIED_AUDIT_TRAIL. One detail worth knowing if you query this view directly: the username column is DBUSERNAME, with no underscore, not DB_USERNAME. This supports compliance requirements under frameworks such as SOX and PCI-DSS.

Summary

Account locking is the standard Oracle mechanism for temporarily suspending user access. It is immediate, fully reversible, and preserves all account attributes. The key commands are:

-- Lock an account
ALTER USER username ACCOUNT LOCK;

-- Unlock an account
ALTER USER username ACCOUNT UNLOCK;

-- Verify status
SELECT username, account_status, lock_date
FROM dba_users
WHERE username = 'USERNAME';

Verify whether active sessions need to be terminated after locking, interpret ACCOUNT_STATUS together with profile limits and audit records, and use unified audit policies to record account-management statements in Oracle AI Database 26ai.

Oracle AI Database 26ai reference: ALTER USER; CREATE PROFILE.