How to Unlock System Account in Oracle?

Unlocking a system account, or any user account, in Oracle is a straightforward process typically accomplished using SQL commands or graphical administration tools. This action changes the status of the account to "unlocked," allowing the user to log in again.

Unlocking with SQL: The ALTER USER Command

The most common and direct method to unlock an Oracle system account (or any user account) is by executing the ALTER USER SQL command with the ACCOUNT UNLOCK clause. This command is executed through SQL*Plus or any other SQL client while connected as a privileged user.

Prerequisites

To unlock an account, you must connect to the Oracle database as a user with the ALTER USER system privilege, such as SYS (connected as SYSDBA) or another administrator with the DBA role.

Steps to Unlock a User Account via SQL

Connect as a Privileged User: Open your SQL client (e.g., SQL*Plus, SQL Developer, DBeaver) and connect to the database as SYSDBA or a user with the necessary administrative privileges.
```
sqlplus / as sysdba
```
(If connecting remotely, use: sqlplus sys/your_sys_password@your_tns_alias as sysdba)
Execute the ALTER USER Command: Use the ALTER USER statement, specifying the account name and the ACCOUNT UNLOCK clause.
To unlock the SYSTEM account:
```
ALTER USER SYSTEM ACCOUNT UNLOCK;
```
To unlock any other locked user account (e.g., HR):
```
ALTER USER HR ACCOUNT UNLOCK;
```
Verify Account Status (Optional but Recommended): After executing the ALTER USER command, you can query the DBA_USERS data dictionary view to confirm that the account's status has changed.
```
SELECT USERNAME, ACCOUNT_STATUS
FROM DBA_USERS
WHERE USERNAME = 'SYSTEM';
```
A successful unlock will show ACCOUNT_STATUS as OPEN. If the password had expired, it might show EXPIRED instead of OPEN, meaning the user will be prompted to change their password on the next login.

Unlocking with Oracle Enterprise Manager (OEM)

For those who prefer a graphical interface, Oracle Enterprise Manager (OEM) provides an intuitive way to manage database users, including unlocking accounts. This graphical tool simplifies various administration tasks and offers a visual approach to database management.

Steps to Unlock a User Account via OEM

Log in to Oracle Enterprise Manager: Access your OEM console using an administrator account.
Navigate to Database Users: From the main dashboard, go to the Targets menu, select Databases, and then click on your specific database instance.
Access Security Settings: In the database homepage, locate and click on the Security menu or tab, and then select Users.
Locate the Locked User: A list of database users will be displayed. Find the SYSTEM user (or the specific user you wish to unlock). Locked accounts are typically indicated by an icon or a status message.
Edit User Properties: Select the user and click on the Edit button or icon.
Change Account Status: In the user properties or attributes section, you will find an option for Account Status. Change its value from LOCKED to UNLOCKED (or OPEN).
Apply Changes: Save or apply the changes to complete the unlocking process. The account's status will be updated in the database.

Why Oracle Accounts Get Locked

Oracle locks user accounts for security reasons, primarily to prevent unauthorized access and protect sensitive data. Common causes for an account becoming locked include:

Failed Login Attempts: If a user exceeds the specified number of incorrect password attempts (defined by the FAILED_LOGIN_ATTEMPTS parameter in the user's assigned password profile), the account will be automatically locked (e.g., LOCKED(TIMED)).
Administrator Action: An administrator can explicitly lock an account using the ALTER USER username ACCOUNT LOCK; command.
Password Expiration: If the PASSWORD_LIFE_TIME parameter in the user's profile is set, the password will expire after a certain period. If a user fails to change their password within the grace period or if the account is already locked, its status might become EXPIRED & LOCKED.
Initial Account Status: Newly created accounts might be in a NEW or EXPIRED state, requiring a password change upon first login.

Important Considerations and Best Practices

When managing and unlocking Oracle accounts, consider these best practices:

Permissions: Always ensure you have the necessary ALTER USER privilege, usually achieved by connecting as SYSDBA or a user with the DBA role.
Password Management:
- If an account was locked due to password expiration (EXPIRED & LOCKED), the user will still need to change their password upon their next successful login.
- For the SYSTEM account, if you suspect the password was compromised, it's crucial to change it immediately after unlocking:
```
ALTER USER SYSTEM IDENTIFIED BY new_strong_password;
```
  Always choose a strong, unique password.
Auditing: Review audit logs to understand why an account was locked, especially if it's a critical account like SYSTEM, to rule out potential security incidents.
Check Status: Regularly verify account statuses using the DBA_USERS view.

Common Account Statuses in DBA_USERS

Account Status	Description	Action Needed After Unlock (if any)
`OPEN`	The account is active and can be used for login.	None.
`LOCKED`	The account has been explicitly locked by an administrator.	Unlock using `ALTER USER`.
`LOCKED(TIMED)`	The account is temporarily locked due to too many failed login attempts.	Unlock using `ALTER USER` or wait for the `PASSWORD_LOCK_TIME` to expire.
`EXPIRED`	The password has expired. The user must change their password upon next login.	User will be prompted to change their password.
`EXPIRED & LOCKED`	The password has expired, and the account is also locked.	Unlock the account first, then the user will be prompted to change their password.

Unlocking the SYSTEM account, or any other user account, is a routine database administration task that can be performed efficiently using either SQL commands or graphical tools like Oracle Enterprise Manager. Always ensure you understand the reason for the lock before unlocking, especially for critical accounts.