[oracle] Changing password with Oracle SQL Developer

Many of my users do not use SQLPlus. I cannot give them alter user. We expire passwords every 60 days.

I can't get the SQLPlus command "password" to work in SQL developer.

1. When I hit run, I get an invalid command error
2. When I hit run script, nothing happens.

I don't want to write them a package to change their passwords since we have a lot of databases. Do I have a better option?

I realise that there are many answers, but I found a solution that may be helpful to some. I ran into the same problem, I am running oracle sql develop on my local computer and I have a bunch of users. I happen to remember the password for one of my users and I used it to reset the password of other users.

Steps:

1. connect to a database using a valid user and password, in my case all my users expired except "system" and I remember that password

2. find the "Other_users" node within the tree as the image below displays

3.within the "Other_users" tree find your users that you would like to reset password of and right click the note and select "Edit Users"

4.fill out the new password in edit user dialog and click "Apply". Make sure that you have unchecked "Password expired (user must change next login)".

And that worked for me, It is not as good as other solution because you need to be able to login to at least one account but it does work.

Try this solution if the Reset Password option (of SQL Developer) did not work:

Step 1: Open Run SQL Command Line (from the start menu, which comes with SQL Developer installation package)

Step 2: Run the following commands:

Note: If password has already expired, Changing password for <user> option will automatically come.

SQL Developer has a built-in reset password option that may work for your situation. It requires adding Oracle Instant Client to the workstation as well. When instant client is in the path when SQL developer launches you will get the following option enabled:

Oracle Instant Client does not need admin privileges to install, just the ability to write to a directory and add that directory to the users path. Most users have the privileges to do this.

Recap: In order to use Reset Password on Oracle SQL Developer:

1. You must unpack the Oracle Instant Client in a directory
2. You must add the Oracle Instant Client directory to the users path
3. You must then restart Oracle SQL Developer

At this point you can right click a data source and reset your password.

See http://www.thatjeffsmith.com/archive/2012/11/resetting-your-oracle-user-password-with-sql-developer/ for a complete walk-through

Also see the comment in the oracle docs: http://docs.oracle.com/cd/E35137_01/appdev.32/e35117/dialogs.htm#RPTUG41808

An alternative configuration to have SQL Developer (tested on version 4.0.1) recognize and use the Instant Client on OS X is:

1. Set path to Instant Client in Preferences -> Database -> Advanced -> Use Oracle Client
2. Verify the Instance Client can be loaded succesfully using the Configure... -> Test... options from within the preferences dialog

3. (OS X) Refer to this question to resolve issues related to DYLD_LIBRARY_PATH environment variable. I used the following command and then restarted SQL Developer to pick up the change:

   $ launchctl setenv DYLD_LIBRARY_PATH /path/to/oracle/instantclient_11_2

you can find the user in DBA_USERS table like

SELECT profile
FROM dba_users
WHERE username = 'MacsP'

Now go to the sys/system (administrator) and use query

ALTER USER PRATEEK
IDENTIFIED BY "new_password"
REPLACE "old_password"

To verify the account status just go through

SELECT * FROM DBA_USERS.

and you can see status of your user.

Your users can still change their passwords by "alter user onlyTheirUserName identified by newpassword". They do not have to have the ALTER USER privilege to change their own password.

Depending on the admin settings, you may have to specify your old password using the REPLACE option

alter user <username> identified by <newpassword> replace <oldpassword> 

To make it a little clear :

If the username: abcdef and the old password : a123b456, new password: m987n654

alter user abcdef identified by m987n654 replace a123b456;

You can now do this in SQL Developer 4.1.0.17, no PL/SQL required, assuming you have another account that has administrative privileges:

1. Create a connection to the database in SQL Developer 4.1.0.17 with an alternative administrative user
2. Expand the "Other Users" section once connected, and right-click the user whose password has expired. Choose "Edit User".
3. Uncheck the "Password Expired..." checkbox, type in a new password for the user, and hit "Save".
4. Job done! You can test by connecting with the user whose password had expired, to confirm it is now valid again.

The built-in reset password option may not work for user. In this case the password can be reset using following SQL statement:

ALTER user "user" identified by "NewPassword" replace "OldPassword";

In an SQL worksheet:

• Type in "password" (without the quotes)

• Highlight, hit CTRL+ENTER.

• Password change screen comes up.

One note for people who might not have the set password for sysdba or sys and regularly use a third party client. Here's some info about logging into command line sqlplus without a password that helped me. I am using fedora 21 by the way.

locate sqlplus

In my case, sqlplus is located here:

/u01/app/oracle/product/11.2.0/xe/config/scripts/sqlplus.sh

Now run

cd /u01/app/oracle/product/11.2.0/xe/config/scripts
./sqlplus.sh / as sysdba

Now you need to connect to database with your old credentials. You can find Oracle provided template in your output:

Use "connect username/password@XE" to connect to the database.

In my case I have user "oracle" with password "oracle" so my input looks like

connect oracle/oracle@XE

Done. Now type your new password twice. Then if you don't want your password to expire anymore you could run

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

The correct syntax for updating the password using SQL Developer is:

alter user user_name identified by new_password replace old_password ;

You can check more options for this command here: ALTER USER-Oracle DOCS

I confirmed this works in SQL Developer 3.0.04. Our passwords are required to have a special character, so the double-quoted string is needed in our case. Of course, this only works if the password has not already expired and you are currently logged in.

ALTER USER MYUSERID
IDENTIFIED BY "new#password"
REPLACE "old#password"

There is another way to reset the password through command prompt ...

1) Go to the Oracle Database Folder ( In my case Oracle Database 11g Express Edition) in the START MENU.

2) Within that folder click "Run SQL Commandline"

Oracle Database Folder image

3) Type "connect username/password" (your username and old password without the quotation marks)

4) The message displayed is ...

ERROR: ORA-28001: the password has expired

Changing password for hr

--> New password:

Enter Username, Password image

5) Type the new password

6) Retype the new password

7) Message displayed is ...

Password changed Connected.

SQL>

8) GO TO Sql developer --> type the new password --> connected