pushing passwords to different DB's
We have over 200 different servers that are running Oracle and every 2 months, we have our passwords expire.
Instead of having the DBA's go into every server to sync the passwords I would to have some sort of way of pushing the encrypted password in the Oracle DB to other databases.
Two things to keep in mind.
1) Every user may not be in every DB so if the user does not exist, the
code should not try to update that users password
2) I have all my DB's in a tnsnames.ora file or I can put into an easy to parse
file, so I can connect to every DB.
Does anybody foresee any problems this may cause? Secondly, does anybody know of a tool or some code around the net I can use as a starting place as
I am not an expert in PL/SQL.
Sorry for all the verbosity
If you grid control installed there is a execute sql feature which you can make use of in this case to change the password on all 200 databases in one click.
What OS are you running? On your OS, what options are allowed in your environment to code - shell script? Perl? Powershell?
Running UNIX (Solaris, AIX, HPUX) I can create a ksh script
why on earth don't you use an ldap server for user control
Agreed on LDAP, although there are licensing costs associated with using OID. Check this out: http://en.wikipedia.org/wiki/Oracle_Internet_Directory Can't vouch for it, but it sounds good and we are likely going to implement it in the next year to manage all database users, if we get the funding this budget cycle.
Also the comment on Enterprise Manager is a cleaner way if you have the oracle password (and don't have to sudo to it). (Most recent version finally works with sudo I think, but most people don't have that yet.)
As for ksh, then you are set. This is easy to do if you run it somewhere you have sqlplus and a tnsnames file. Exactly how you do it would depend on your specific setup and tools available in unix. If your tnsnames file is accurate and doesn't have a bunch of garbage, you could parse that and use it to drive a while loop. Or create a list of servers and databases. Even better, just have a list of hosts and (if you have rsh enabled, we don't for security) you can simple execute commands remotely to do a ps -ef | grep ... and get a list of running databases on each host, then iterate over them.
I go back to this site for ksh help/reminders all the time: http://www.well.ox.ac.uk/~johnb/comp/unix/ksh.html ... look at the "Read Input from User and from Files" section and you'll be on your way to looping through values in a file. Calling sqlplus: http://www.tek-tips.com/faqs.cfm?fid=2220 ... Selecting a value into a variable: http://www.idevelopment.info/data/Or...x/UNIX_3.shtml
Should get you on your way.
Excellent links thanks for the input
Thanks for spamming the post on multiple forums and having multiple groups of people spend their time duplicating work on your behalf.
I am running version 188.8.131.52.0 and I don't see the password at all does
anybody know where it is kept?
sys> show user
USER is "SYS"
Name Null? Type
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
CREATED NOT NULL DATE
select dump(password) from dba_users;
Click Here to Expand Forum to Full Width