How do I change my password in multiple Oracle databases?

Question

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

It expires every month and I would like to change them all simultaneously.

Answer 1

You could change your password in 1 database (I haven't done this in a number of years - so try this carefully - Last time I did this was 7.3.4 and 8i) then copy the hash from database to database. This used to work. So ... In 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 the other database and alter that password in:

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

You could write a small program that would connect and to the multiple alters. I only have an 11i database to test this on.

Answer 2

Sometimes, simplest may be best. Create a SQL*Plus script with substitution variables that looks like:

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'd replace "myuser" with your userid and "db1" and so forth with your SQL*Net aliases.) Build the script. Run it, entering the old and new passwords once, and it will change them all. You'll need to edit the script every time you add or remove a database, but that should be fairly rare. Note that the passwords will be visible on-screen while it's running.

Answer 3

If you have Oracle grid control on your systems, you can create a job (within jobs, and then create sql job), and specify a target group (if defined) or manually choose which target databases to connect to via a checklist, and then run that job against these databases.

Answer 4

I would say if you have to login to multiple databases with the same credentials you should probably have your authentication use some other options, including LDAP/Active Directory.