Locking a User's Account in Oracle 19c

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 access can be restored instantly with a single unlock command. This lesson covers how to lock and unlock accounts in Oracle 19c and later, including Oracle 23ai.

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

or

sqlplus admin_username/admin_password@dbname

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

The ACCOUNT_STATUS column will display LOCKED. The LOCK_DATE column records when the lock was applied, which is useful for auditing.

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;

The user can log in immediately after the unlock — no password reset or privilege reassignment is required.

Automatic Account Locking

Accounts can also become locked automatically without administrator intervention in two situations. First, if a user profile enforces a FAILED_LOGIN_ATTEMPTS limit, Oracle locks the account after the specified number of consecutive failed logins. Second, if a profile enforces a password change interval and the user does not change their password within the grace period, Oracle locks the account automatically at the next login attempt. Both behaviors are configured through user profiles.

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

Then kill the session:

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

Additional Methods for Temporarily Disabling Login

Account locking is the cleanest and most reversible approach, but Oracle provides two additional techniques when locking is not appropriate for the situation.

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

A profile with SESSIONS_PER_USER 0 prevents any new sessions from being established. Create the restrictive profile, assign it, then reassign the original when ready:

CREATE PROFILE temp_disable LIMIT
  SESSIONS_PER_USER 0;

ALTER USER john_doe PROFILE temp_disable;

-- Restore original profile when ready
ALTER USER john_doe PROFILE default;

Auditing Account Lock and Unlock Activity

Oracle Unified Auditing, available from Oracle 12c and refined in Oracle 19c and 23ai, provides fine-grained tracking of account management operations. To audit all ALTER USER actions including locks and unlocks:

AUDIT ALTER USER;

In Oracle 23ai, unified audit policies offer more granular control. You can create a named policy that audits only account lock and unlock events:

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

Always verify whether active sessions need to be terminated after locking, and enable auditing to maintain a record of all account state changes for compliance purposes.