How to Block a User in Oracle Database (ACCOUNT LOCK)

calendar Dec 31, 2023 / · 9 min read · user Oracle account dba_users dba_profiles v$session unified_audit_trail  ·
Share on: linkedin copy

Lock or Unlock an Oracle User in the Database

Purpose

A contractor leaves on Friday. A service account password shows up in a chat log. A batch user fails to log in 40 times from an unknown host. In each case the first move is the same: shut the account without dropping it. ALTER USER ... ACCOUNT LOCK does that. The user, its objects, its grants and its password stay in the database, but a login attempt is refused.

The reverse statement, ACCOUNT UNLOCK, opens the account again. The pair gives a DBA a safe, reversible switch, which a DROP USER is not.

A lock is not always something a DBA did by hand. A profile can lock an account on its own after repeated failed logins, or after a long period with no logins. The status of the account tells you which case you have, and the cause decides whether a plain unlock is enough.

Lock User

SQL Code

1alter user <username> account lock;

Unlock User

SQL Code

1alter user <username> account unlock;

Check the Result

1select username, account_status, lock_date, expiry_date, profile
2from dba_users
3where username = upper('<username>');

List every account that is locked in any way:

1select username, account_status, lock_date, profile
2from dba_users
3where account_status like '%LOCKED%'
4order by lock_date;

Code Breakdown

  • alter user <username> changes the properties of an existing account. The statement needs the ALTER USER system privilege. A user can change their own password without it.
  • account lock locks the account and disables access to it. account unlock enables access again. When a user is created, the default is ACCOUNT UNLOCK.
  • The statement carries only the ACCOUNT clause, so the password, the profile, the roles and the quotas are not touched.
  • <username> is the account name. Unquoted names are stored in upper case, which is why the check query uses upper().
  • lock_date in DBA_USERS shows when the account was locked, if the status was LOCKED. expiry_date shows when the password expires or expired.
  • account_status like '%LOCKED%' catches LOCKED, LOCKED(TIMED) and the combined values such as EXPIRED & LOCKED.

Key Points

  • Effect on logins. A locked account cannot log in. The refused login ends with ORA-28000: the account is locked. That error has three causes: too many consecutive failed logins, a lock by an administrator, or a common user locked in the root container.
  • Reversible. ACCOUNT UNLOCK restores access. No grants or objects are lost.
  • Several ACCOUNT_STATUS values contain a lock. LOCKED: locked by ALTER USER ... ACCOUNT LOCK, or by failed logins when PASSWORD_LOCK_TIME is UNLIMITED. LOCKED(TIMED): locked by failed logins, and it opens by itself when PASSWORD_LOCK_TIME has passed. Combined values pair an expired password with a lock: EXPIRED & LOCKED, EXPIRED(GRACE) & LOCKED, EXPIRED & LOCKED(TIMED) and EXPIRED(GRACE) & LOCKED(TIMED). Releases with password rollover add values such as LOCKED & IN ROLLOVER; a LIKE '%LOCKED%' filter catches all of them.
  • Combined values need two steps. Unlock the account first, then change the password.
  • Oracle-maintained accounts. A row in DBA_USERS with ORACLE_MAINTAINED = 'Y' was created and is maintained by Oracle-supplied scripts. Such a user must not be changed in any way except by running an Oracle-supplied script. Do not unlock these accounts to make a tool work.
  • Existing sessions. ACCOUNT LOCK disables access to the account, and the Oracle documentation does not say that it ends sessions already connected. Check V$SESSION for such sessions and end them separately if the account must be closed at once.

Insights and Best Practices

Know which lock you have

The status column is the diagnostic. A DBA lock and a failed-login lock look the same to the user, but they have different fixes.

ACCOUNT_STATUSCauseHow it ends
LOCKEDALTER USER ... ACCOUNT LOCK, or failed logins with PASSWORD_LOCK_TIME UNLIMITEDALTER USER ... ACCOUNT UNLOCK
LOCKED(TIMED)Failed logins went over FAILED_LOGIN_ATTEMPTSACCOUNT UNLOCK, or wait until PASSWORD_LOCK_TIME has passed

If the cause is a bad password in a script or a connection pool, an unlock without a fix means the account locks again at the next retry. Find the source of the failed logins first.

Read the profile limits that lock accounts

Every account has a profile, and the profile holds the lock rules. These are the limits that matter:

  • FAILED_LOGIN_ATTEMPTS: the number of consecutive failed logins before the account is locked. If the clause is omitted in CREATE PROFILE, the default is 10.
  • PASSWORD_LOCK_TIME: the number of days the account stays locked after that many failed logins. If omitted, the default is 1 day. Fractions work: 1/24 is one hour. UNLIMITED means the account stays locked until a DBA unlocks it.
  • INACTIVE_ACCOUNT_TIME: the permitted number of consecutive days with no logins, after which the account is locked. The minimum is 15 days.
  • PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME: control expiry, not locking, but they appear in the combined statuses.

Show the limits for one user's profile:

1select p.profile, p.resource_name, p.limit
2from dba_profiles p
3where p.profile = (select profile from dba_users where username = upper('<username>'))
4and p.resource_name in ('FAILED_LOGIN_ATTEMPTS', 'PASSWORD_LOCK_TIME',
5                        'INACTIVE_ACCOUNT_TIME', 'PASSWORD_LIFE_TIME',
6                        'PASSWORD_GRACE_TIME')
7order by p.resource_name;

A limit shown as DEFAULT means the profile omits it and the limit comes from the DEFAULT profile. Check that profile too.

Change a profile and attach it to a user:

1create profile app_service limit
2  failed_login_attempts 5
3  password_lock_time 1/24;
4
5alter user <username> profile app_service;

A short PASSWORD_LOCK_TIME limits damage from a password-guessing run and also limits the harm of a lock-out caused by a mistake. An UNLIMITED value is stronger but every lock then needs a DBA. Choose by how fast your team can respond.

Multitenant databases

In a container database, a common user is created in the root, and a lock on a common user in the root is one of the causes of ORA-28000. To lock or unlock a common user in the root and in every container, use the CONTAINER clause from the root:

1alter user c##admin account lock container = all;

Connect to the root before you run it. CONTAINER = CURRENT limits the change to the current container.

Sessions that are already open

Check who is connected as the account:

1select sid, serial#, username, status
2from v$session
3where username = upper('<username>');

If the account must be shut out now, end those sessions after the lock:

1alter system kill session '<sid>,<serial#>' immediate;

IMMEDIATE tells the database to roll back ongoing transactions, release all session locks, recover the entire session state and return control at once. Your session and the target session must be on the same instance unless you add the target's instance ID as a third value, found in GV$SESSION. Kill the right session: the SID and SERIAL# pair comes from the query above.

Audit lock and unlock events

With unified auditing, a policy records who ran ALTER USER (the predefined ORA_ACCOUNT_MGMT policy lists ALTER USER among its actions), and a second policy records failed logins. Both use CREATE AUDIT POLICY and AUDIT POLICY:

1create audit policy acct_changes actions alter user;
2audit policy acct_changes;
3
4create audit policy failed_logons actions logon;
5audit policy failed_logons whenever not successful;

Read the trail:

1select event_timestamp, dbusername, action_name, return_code, unified_audit_policies
2from unified_audit_trail
3where unified_audit_policies like '%ACCT_CHANGES%'
4   or unified_audit_policies like '%FAILED_LOGONS%'
5order by event_timestamp desc;

RETURN_CODE holds the Oracle error code the action produced and is zero when it succeeded. Creating a policy needs the AUDIT SYSTEM system privilege or the AUDIT_ADMIN role. Unified audit policies work in the mixed-mode auditing that a newly installed database uses by default, so pure unified auditing does not have to be switched on first.

Do not lock accounts you need

Never lock SYS, SYSTEM or the account that owns a running application unless you have a tested way back. A schema owner that nobody needs to log in to is a good candidate for a different design: keep it locked, and let people reach it through proxy connections so every change is tied to a person.

When to Use This Check

  • When an employee or contractor leaves and the account should stop working today
  • When a password is suspected of exposure
  • When a user reports ORA-28000 and you need to know whether a person or a profile locked it
  • When failed logins repeat from one account and you want to stop them while you trace the source
  • Before a long freeze on an application schema, to make sure nobody connects by hand
  • During an access review, to list every locked account and its lock date
  • When tuning FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME for a profile

Troubleshooting Common Issues

Insufficient privileges. The session lacks the ALTER USER system privilege. Ask a DBA, or connect as a user who has it.

The account unlocks and locks again. Something keeps sending a wrong password. Search the audit trail for the failed logins, find the client host and fix the stored credential. Raising FAILED_LOGIN_ATTEMPTS only delays the next lock.

Unlock works but the login still fails. The status may be EXPIRED & LOCKED. Unlock the account first, then set a new password with ALTER USER ... IDENTIFIED BY. Check account_status again after each step.

A common user is still locked in one pluggable database. The lock may exist only in that container. Connect to the container, read DBA_USERS there and unlock it there, or run the statement from the root with CONTAINER = ALL.

The user is still connected after the lock. The documentation describes the lock as disabling access, not as ending sessions. Find open sessions in V$SESSION and end them with ALTER SYSTEM KILL SESSION.

You cannot find the user. Names are case sensitive when quoted. Compare with upper() as the check query does, or query DBA_USERS with a like pattern.

References

Posts in this series