How to change password in multiple Oracle databases?

Question

Is there a tool that allows me to change the password in multiple Oracle databases?

It expires every month, and I would like to change them all at the same time.

Answer 1

I found a useful utility for this: http://www.bsutils.com/PassAid.html .

Answer 2

Sometimes it's easiest to be easier. Create a SQL * Plus script with lookup variables that look like this:

connect myuser/&& oldpass@db1 ; alter user myuser identified by &&newpass replace &&oldpass; connect myuser/&& oldpass@db2 ; alter user myuser identified by &&newpass replace &&oldpass; connect myuser/&& oldpass@db3 ; alter user myuser identified by &&newpass replace &&oldpass; -- and so forth through your list of instances 

(Of course, you would replace "myuser" with your user ID and "db1", etc. with your SQL * Net aliases.) Create a script. Launch it by entering the old and new passwords once, and it will change them all. You will need to edit the script every time you add or remove a database, but this should be pretty rare. Please note that passwords will be displayed on the screen during operation.

Answer 3

You can change your password in one database (I have not done this for several years - so try this carefully - the last time I did this was 7.3.4 and 8i), then copy the hash from the database to the database data. It worked. So ... In the database 1

 SQL> password Changing password for SCOTT Old password: New password: Retype new password: 

Then in the same database

 SQL> SELECT password FROM dba_users WHERE username='SCOTT'; PASSWORD --------------- F81184D39902C27 

Now go to another database and change this password:

 SQL> ALTER USER scott IDENTIFIED BY VALUES 'F81184D39902C27'; User altered. 

You can write a small program that will be connected to several changes. I only have 11i to check this out.

Answer 4

I would say that if you need to connect to multiple databases with the same credentials, you should probably use your authentication to use other parameters, including LDAP / Active Directory.

Answer 5

If your password in each database is the same, you also have a simple Unix shell script:

 #!/bin/bash read -p " Enter Username :" USERNAME ; echo read -p " Enter old Password :" -s oldpass ; echo read -p " Enter new Password :" -s newpass ; echo read -p " Repeat new Password :" -s newpass2 ; echo if [ "${newpass}" = "${newpass2}" ] then sqlplus "${USERNAME}"/${oldpass}@db1 << _EOF connect "${USERNAME}"/${oldpass}@db1; alter user "${USERNAME}" identified by ${newpass} replace ${oldpass}; connect "${USERNAME}"/${oldpass}@db2; alter user "${USERNAME}" identified by ${newpass} replace ${oldpass}; connect "${USERNAME}"/${oldpass}@db3; alter user "${USERNAME}" identified by ${newpass} replace ${oldpass}; _EOF else echo "given new passwords did not match". fi 

Hope it helps.

Answer 6

If you have control of the Oracle grid on your systems, you can create a task (within the tasks, and then create an SQL task) and specify the target group (if one is defined) or manually select which target databases to connect to through the checklist, and then do the work for these databases.

Answer 7

You can create a repeating list of all your databases:

  sqlplus <username>/<oldpass>@<database>; alter user <username> identified by <newpass>; exit --Repeat this for as many databases as you need 

OR

You can create a table of all your desired databases, and then create a parameter for the databases. Then draw the name of your database so that it is read from the table.