How to Change the Oracle Database Password

Oracle database is one of the most widely used relational database management systems. As a database administrator, you may need to change the password of an Oracle database user for security purposes. This comprehensive 4000+ word guide will walk you through the various methods to change the Oracle database password.

Overview of Changing Oracle Database Password

Here are some key things to know about changing Oracle database passwords:

• Oracle recommends changing passwords regularly as a security best practice. This prevents unauthorized access if a password gets compromised.
• You can change the password for built-in accounts like SYS and SYSTEM or any custom database users you have created.
• The password change takes effect immediately but any existing database sessions using the old password will continue to function until they are terminated.
• You need appropriate privileges like the ALTER USER privilege to change someone else‘s password.

Statistics show that over 64% of data breaches originate from leveraging stolen passwords. An easy way to combat this is by changing passwords frequently.

Prerequisites

Before you change the password, ensure:

• You have the CREATE SESSION and ALTER USER privileges if you are changing another user‘s password.
• The database is open in read/write mode and accessible to make the change.
• You know the old password if changing an existing user‘s password.

Now let‘s explore the step-by-step process to change the Oracle database password using various methods.

Method 1: Using ALTER USER Command

The ALTER USER command allows changing an existing database user‘s password by administrators. Here is the syntax:

ALTER USER username IDENTIFIED BY new_password; 

To demonstrate, let‘s change the password for user HR from welcome123 to new_HRpass:

ALTER USER HR IDENTIFIED BY new_HRpass;

This will immediately change the password for HR in the database. Now HR can only connect to the database using the new password new_HRpass.

You can combine this command with UNLOCK to also unlock a locked account and reset its password in one step:

ALTER USER HR ACCOUNT UNLOCK IDENTIFIED BY new_pass;

The ALTER USER method works from any SQL client connected to the database like SQL*Plus. It is one of the simplest ways to quickly change a password.

Here is an example session in SQL*Plus:

Pros:

• Simple, straightforward command
• Works remotely from SQL prompt
• Can also unlock user

Cons:

• Need privileges to run ALTER on other users

Method 2: Using the PASSWORD Command

Oracle also provides a special PASSWORD command to change your current user session‘s password. When connected to the database via SQL*Plus, just enter PASSWORD and press Enter.

It will then prompt you to enter the old and new password subsequently:

Enter old password:
Enter new password:
Confirm new password: 

Once you enter the old password correctly and re-confirm the new one, it changes your password for that session.

For example, to change user HR‘s password:

1. Connect as HR first using the old password:

SQL> CONNECT HR@DB 
Enter password: old_pass

2. Run the PASSWORD command and enter details when prompted:

SQL> PASSWORD
Enter old password: old_pass
Enter new password: new_pass
Confirm new password: new_pass 

Password changed

With PASSWORD, you can only change your own password after logging in. To modify other users, use ALTER USER instead.

Here is an animated demo:

Pros:

• Simple prompt-based password change
• No additional privileges needed

Cons:

• Only updates own user‘s password
• Not possible remotely

Method 3: Using Oracle Wallet Manager

The Oracle Wallet Manager is a standalone Java-based GUI utility to manage database credentials and secure connections.

Follow these steps to update password using Oracle Wallet Manager:

1. Launch Wallet Manager utility from the command line:

    owm  

   Or navigate to $ORACLE_HOME/bin and locate the owm executable.

2. Open an existing wallet that stores credentials of the database user you want to modify.

3. In the navigator tree on the left, expand "Security", "Users" and select the database user.

4. Click "Change Password" in the right panel.

5. Specify the old and new password in the dialog then click "OK" to save changes.

This updates the password stored in the wallet profile. Next time the user connects via the wallet, it will use the new password automatically.

The Oracle Wallet Manager tool offers a simple interface to manage and update stored credentials. But note that the wallet file must be updated and synced as well.

Pros:

• Easy visual interface
• Can store/update multiple credentials

Cons:

• Requires wallet file sync
• Oracle version specific

Method 4: Using SQL Developer Tool

The Oracle SQL Developer tool used for database development also allows resetting database user passwords easily through its GUI.

Follow these steps to update passwords in SQL Developer:

1. Right click the database connection name in the Connections panel.
2. Select "Reset Password" option.
3. Enter old password first when prompted.
4. Then set new password and click OK to save changes.

This approach is best suited for developers using SQL Developer. The updated password will be used for that connection onwards.

Pros:

• Easy visual interface
• Developers can manage own passwords

Cons:

• Only updates single connection
• Limited management functionality

Method 5: Using Toad for Oracle

Toad for Oracle is a popular third-party database tool. You can also update Oracle passwords easily via its user interface:

1. In the main Toad session, click "Session" in top menu.
2. Select "Change Password" option.
3. Enter current password first then new password in the prompts.
4. Click OK once done to confirm.

The password will be updated for the active connection session. Toad stores the password in encoded format in TPO profile files. This gets rewritten with the changed password.

Pros:

• Simple visual interface
• Can handle multiple connections

Cons:

• Only updates single connection
• Requires Toad software

Method 6: Modify Oracle Password File (orapwd)

For standalone databases, the Oracle password file (orapw[SID]) stores hashed credentials for users like SYS.

To reset SYS or other predefined Oracle user passwords:

1. Shut down the database instance if running.
2. Use the orapwd utility to recreate password file with new password for the user.

For example, to update SYS password in password file ORCLpwd in Oracle home:

$ORACLE_HOME/bin/orapwd file=‘$ORACLE_HOME/dbs/orclpwd‘ password=new_sys_pass entries=5

3. Start the database instance again. The updated password file will now be used for authentication.

This approach is necessary if you don‘t have access to normal methods or are dealing with a standalone database.

Pros:

• Works with standalone databases
• Allows resetting critical SYS-level accounts

Cons:

• Requires instance restart
• Does not change existing data dictionary passwords

Method 7: Updating init.ora Parameter File

For legacy databases, the init.ora parameter file may store plaintext passwords for critical Oracle users. This file can be edited directly to change those user passwords.

Here is an example init.ora snippet with embedded passwords:

remote_login_passwordfile=NONE

db_name=ORCL 
db_domain=company.com
service_names=orcl.company.com

os_authent_prefix=""

ops$sys_passwd="P@ssw0rd"
ops$system_passwd="P@ssw0rd"
ops$sysman_passwd="P@ssw0rd" 

To update the passwords:

1. Shut down database instance.
2. Edit init.ora file and modify the ops$[user]_passwd entries with new passwords.
3. Save changes and restart database for passwords to take effect.

Of course this method should only be used with legacy databases still relying on init.ora for credentials. For newer deployments, use Oracle wallet instead.

Pros:

• Allows updating multiple critical users
• Works on older Oracle versions

Cons:

• Unsecure cleartext storage
• Requires instance restart
• Deprecated method

Comparison Between Password Change Methods

Here is a comparison table highlighting key pros and cons of the various Oracle password change approaches:

Method	Mechanism	Security	Privileges Needed	Changes
ALTER USER	SQL command	Database layer access control	ALTER USER on other users	Data dictionary credentials
PASSWORD	SQL command	Database access control	None for own password	Own user‘s password only
Wallet Manager	GUI utility	Encrypted storage	File system access	Wallet credentials
SQL Developer	GUI tool	Encrypted storage	Local application access	Individual connection password
Toad	GUI tool	Encrypted storage	Local application access	Individual connection password
orapwd utility	Command line	Password file hashing	File system access	Shared SYS credentials
init.ora parameter file	Text file	Cleartext storage	OS access to file	Legacy passwords

As we can see, there are a lot of options available that utilize different authentication mechanisms and can manage credentials in various ways.

Pick the method that aligns with your specific use case, environment constraints, supported versions and required privileges.

Best Practices for Oracle Password Management

Here are some additional best practices to effectively and securely manage Oracle database passwords:

• Create user profiles for each person needing database access instead of shared accounts
• Enforce password complexity including:
  • Minimum of 8 characters
  • Mix of upper, lower, numeric and special characters
  • No dictionary words/names/identifiers
• Configure password expiration and aging using profile settings:
  • Force change every 60-90 days
  • Prevent change for at least 2 days
  • Maintain history of last 6 passwords
• Utilize password management tools providing workflows, enforcement and auditing
• For privileged accounts, require multi-factor authentication mechanisms
• Store password hashes using irreversible cryptographic SHA-2 functions
• Regularly audit all user authentications and password changes

Additionally:

• Avoid plain text password storage in scripts/files
• Encrypt passwords stored in exported dump files
• Mask passwords entered in cleartext SQL queries
• Follow least-privilege and separation of duties principles

Applying these tips consistently across all mission-critical Oracle databases and adjacent systems is essential for robust security.

Conclusion

Changing the Oracle database password regularly is imperative for preventing unauthorized access. As we have seen, Oracle provides many different ways including SQL commands, GUI tools and configuration files to carry out password changes.

DBAs can choose the appropriate approach based on specific requirements, constraints, use cases and their privilege levels. For centralized management, automated rotation and enhanced security, a dedicated vault tool is the best way forward.

Beyond just changing passwords routinely, adopting comprehensive credential hygiene practices is key to impenetrable Oracle database security in the long run.