Locking User Accounts in Oracle Database

Lock or Unlock an Oracle User in the Database

Lock User

SQL Code

1alter user <username> account lock;
2/

Unlock User

SQL Code

1alter user <username> account unlock;
2/

Sample Oracle Output:

1
2no rows selected
3SQL>

Purpose:

  • To lock a specific user account in the Oracle database, preventing any further login attempts using that account.

Breakdown:

  • alter user: This SQL command is used to modify various properties of user accounts.
  • <username>: A placeholder for the actual username of the account to be locked.
  • account lock: The specific clause that instructs the database to lock the account.

Key Points:

  • Immediate Effect: The account is locked immediately upon execution of the command.
  • No Password Change: The user's password remains unchanged, but they cannot use it to log in.
  • Reversible: The account can be unlocked later using the alter user <username> account unlock; command.
  • Privileges: Requires the ALTER USER system privilege to execute.

Insights and Explanations:

  • Security Measure: Locking accounts is a common security practice to protect against unauthorized access in scenarios such as:

    • Suspected password breaches
    • Employee term

Posts in this series