Table of Contents

How to Reset Your Oracle Password: A Deep Dive for the Disgruntled DBA (and Everyone Else)

Forgotten your Oracle password? Don’t panic. It happens to the best of us, even those crusty DBAs who swear they never forget anything. Think of it as an opportunity to flex your administrative muscles and reaffirm your mastery over the Oracle universe. This guide will walk you through the necessary steps to regain access, focusing on clarity, security, and a dash of dry humor to keep things interesting.

The Short Answer: Resetting Your Oracle Password

The process for resetting an Oracle password varies depending on whether you’re dealing with the SYS or SYSTEM account (the privileged accounts) or a regular user account. In either case, you’ll need access to a privileged account to execute the necessary commands. The core method involves connecting to the database as SYSDBA and using the ALTER USER command. Here’s the basic outline:

Connect as SYSDBA: Use SQL*Plus (or similar tool) and connect to the database with SYSDBA privileges. This is the critical first step.
Issue the ALTER USER command: Use the ALTER USER command followed by the username and the IDENTIFIED BY clause to set the new password. For example: ALTER USER username IDENTIFIED BY new_password;
Consider Password Complexity: Oracle best practices strongly recommend using complex passwords.
Disconnect and Reconnect: Disconnect from the database and reconnect using the new password to verify the change.

This is the general recipe. Now, let’s get into the specifics.

Resetting the SYS or SYSTEM Password

Resetting the password for SYS or SYSTEM requires extra care because these accounts have immense power. Here’s the step-by-step:

Access the Server: Log in to the server where the Oracle database resides, typically via SSH or Remote Desktop.
Start SQL*Plus as OS Authentication: This is crucial! Connect to the database instance as SYSDBA using operating system authentication. This bypasses the need for a password initially. On Linux/Unix, you’ll typically use:
```
sqlplus / as sysdba 
```
On Windows, ensure you’re running the command prompt as an administrator.
ALTER USER Command for SYS or SYSTEM: Once connected, execute the following command:
```
ALTER USER SYS IDENTIFIED BY new_sys_password; -- OR ALTER USER SYSTEM IDENTIFIED BY new_system_password; 
```
Replace new_sys_password or new_system_password with your desired, strong, new password.
Important Security Step: Change the AUTHENTICATION to PASSWORD: After changing the password, immediately change the authentication method for SYS to PASSWORD. This prevents future OS-authenticated logins bypassing the password requirement.
```
ALTER USER SYS IDENTIFIED BY new_sys_password REPLACE 'old_sys_password'; -- If you remember the old password (highly unlikely) --OR ALTER USER SYS IDENTIFIED BY new_sys_password; -- If you DON'T remember the old password (more likely) 
```
Note: From Oracle 12c onwards, OS authentication for SYS is deprecated (thankfully), but still supported in some environments. The correct way is to use password authentication.
Verify the Change: Disconnect from SQL*Plus and reconnect using the new password to ensure it works:
```
sqlplus sys/new_sys_password@your_database as sysdba 
```
Reset Oracle Password File if needed: If you are still facing issues with sysdba connections, you may need to reset the Oracle password file. sql ORAPWD FILE=orapw<SID> PASSWORD=new_sys_password ENTRIES=5 FORCE=Y Run this from the command line as the Oracle user. Replace <SID> with your database SID.

Resetting a Regular User Password

Resetting a regular user’s password is more straightforward, assuming you have SYSDBA privileges.

Connect as SYSDBA: As before, connect to the database instance as SYSDBA.
ALTER USER Command: Execute the ALTER USER command, substituting the username and new password:
```
ALTER USER username IDENTIFIED BY new_password; 
```
Replace username with the actual username and new_password with the new password.
Consider Account Locking: If you suspect the account has been compromised, consider locking the account after resetting the password:
```
ALTER USER username ACCOUNT LOCK; 
```
The user will need to be unlocked before they can log in again, giving you time to investigate.
Inform the User: Notify the user that their password has been reset and provide them with the new password (ideally, a temporary one that they should change upon first login).

Security Considerations

Password Complexity: Enforce password complexity policies. Oracle provides password complexity verification functions that you can integrate into your database security policies.
Password Rotation: Encourage (or enforce) regular password changes.
Audit Trails: Enable auditing to track password changes and other sensitive database operations.
Least Privilege: Ensure users have only the necessary privileges to perform their tasks. This limits the potential damage if an account is compromised.
Avoid Default Passwords: Never use default passwords (e.g., welcome1, password).

Frequently Asked Questions (FAQs)

Here are some common questions and answers related to Oracle password resets:

1. How can I check which users have been locked?

You can query the DBA_USERS data dictionary view:

SELECT username, account_status FROM DBA_USERS WHERE account_status = 'LOCKED(TIMED)';

2. How do I unlock a user account?

Use the ALTER USER command with the ACCOUNT UNLOCK clause:

ALTER USER username ACCOUNT UNLOCK;

3. What if I forget the SYSDBA password?

This is a critical situation. The steps are similar to resetting the SYS password initially using OS authentication. If OS authentication is disabled, you’ll need access to the server’s filesystem to modify the sqlnet.ora file (if it restricts OS authentication). This is a last resort and should be handled with extreme caution. A clean recovery strategy (backup and restore) might be a better option.

4. Can I reset a password using Enterprise Manager (OEM)?

Yes, OEM provides a GUI-based interface for managing users and resetting passwords. Navigate to the user management section and select the user you want to modify.

5. What are password profiles in Oracle?

Password profiles define password policies, such as password complexity, password expiration, and account locking. You can assign different profiles to different users or groups of users.

6. How do I create a password profile?

CREATE PROFILE my_profile LIMIT   PASSWORD_LIFE_TIME 180   PASSWORD_REUSE_TIME 365   PASSWORD_REUSE_MAX UNLIMITED   PASSWORD_VERIFY_FUNCTION verify_password_function   FAILED_LOGIN_ATTEMPTS 3   PASSWORD_LOCK_TIME 1;

7. How do I assign a password profile to a user?

ALTER USER username PROFILE my_profile;

8. What is the `PASSWORD_VERIFY_FUNCTION` used for?

It’s a PL/SQL function that enforces password complexity rules. Oracle provides a sample function called verify_function in the $ORACLE_HOME/rdbms/admin/utlpwdmg.sql script. You can customize this function to meet your specific requirements.

9. How can I see the password profile assigned to a user?

Query the DBA_USERS data dictionary view:

SELECT username, profile FROM DBA_USERS WHERE username = 'your_username';

10. What is the difference between `IDENTIFIED BY VALUES` and `IDENTIFIED BY`?

IDENTIFIED BY VALUES is used when you already have the password hash. IDENTIFIED BY is used when you want to set a new password. You’ll almost always use IDENTIFIED BY.

11. What if the database is in `MOUNT` mode?

You’ll still need to open the database in READ WRITE mode to alter user passwords. Resetting the SYS password, however, sometimes can be done in MOUNT mode as an extreme solution, although it’s not recommended.

12. My ALTER USER command fails with ORA-28003: password verification for the specified user failed. What does it mean?

This means the new password you chose does not meet the requirements of the password complexity verification function defined in the user’s profile. Choose a stronger password.

Conclusion

Resetting Oracle passwords is a fundamental DBA task. By understanding the different methods, security implications, and password policies, you can confidently manage user access and maintain the integrity of your Oracle database. Remember to prioritize security, enforce strong password policies, and document your processes. After all, a well-managed database is a happy database (and a happy DBA). Now, go forth and conquer those forgotten passwords!