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.
1
-
Would be handy - and - I could see implementing it. If nobody suggest anything I will probably write one for you. I will check back on this question and see if you get a good answer.Philip Schlump– Philip Schlump2009-12-11 20:20:00 +00:00Commented Dec 11, 2009 at 20:20
Add a comment
|
7 Answers
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.
1 Comment
n00b
fyi if you havent used sql plus before it comes bundled with oracle (found in the oracle home/bin directory). you simply create the script, and then run it once logged into sql plus (you can login to any of the db instances you have).
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.
2 Comments
APC
In 11g
DBA_USERS.PASSWORD is empty; the hash is now only available from USR$. But even in earlier versions most users would not have privileges on DBA_USERS, and rightly so.
Alohci
It's relatively easy to compute the password hash from the user name and password for Oracle 10g and earlier, after which "alter user ... identified by values ..." can be used even without access to DBA_USERS. 11g, I believe, has an enhanced security mode where this is much harder.
If your password are all the same on each DB, you also have this 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.
Comments
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.
Comments
I found a good utility for this here: http://www.bsutils.com/PassAid.html.
3 Comments
n00b
sounds nice but would be better if you could compile the source yourself. no telling what the exe does beyond what it states
volni
It's a self extracting exe. The source is all batch.
n00b
didnt realize that thanks. did it using sql plus myself. worked like a charm. thanks for sharing
You can create a repeating list of all you 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 your database name to have it read from the table.
