pushing passwords to different DB's
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: pushing passwords to different DB's

  1. #1
    Join Date
    Feb 2009
    Posts
    91

    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

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    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.

  3. #3
    Join Date
    Oct 2010
    Posts
    7

    more info

    What OS are you running? On your OS, what options are allowed in your environment to code - shell script? Perl? Powershell?

  4. #4
    Join Date
    Feb 2009
    Posts
    91
    Running UNIX (Solaris, AIX, HPUX) I can create a ksh script

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    why on earth don't you use an ldap server for user control

  6. #6
    Join Date
    Oct 2010
    Posts
    7

    oid etc

    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.

  7. #7
    Join Date
    Feb 2009
    Posts
    91
    Excellent links thanks for the input

  8. #8
    Join Date
    May 2002
    Posts
    2,645
    Thanks for spamming the post on multiple forums and having multiple groups of people spend their time duplicating work on your behalf.

  9. #9
    Join Date
    Feb 2009
    Posts
    91
    I am running version 11.1.0.7.0 and I don't see the password at all does
    anybody know where it is kept?

    sys> show user
    USER is "SYS"

    desc all_users;
    Name Null? Type

    USERNAME NOT NULL VARCHAR2(30)
    USER_ID NOT NULL NUMBER
    CREATED NOT NULL DATE


    select dump(password) from dba_users;

    DUMP(PASSWORD)
    -----------------------------------------------------------------------
    NULL
    NULL
    NULL
    NULL
    NULL
    ....
    ....
    ....

  10. #10
    Join Date
    Oct 2010
    Posts
    7
    11g changed how passwords are displayed. They are no longer available in dba_users but can still be seen in SYS.USER$. Read this: http://www.experts-exchange.com/Data...Passwords.html

    You can autodetect which version in a ksh script and still get the 'values' password this way, just recoded my auto-schema-refresh script to work with 11g. Note that the username field is now NAME not USERNAME, as well.

    # determine Oracle version - 10 or 11
    oraversion=`strings $ORACLE_HOME/bin/oracle | grep NLSRTL | grep 11`
    if [[ -n $oraversion ]];then
    ELEVEN="YES"
    echo "***"
    echo "Detected 11g, setting to 11g mode."
    else
    ELEVEN="NO"
    fi

    ....

    if [[ ${ELEVEN} = "YES" ]];then
    pwsql="select PASSWORD from SYS.USER$ where NAME='${DEST}';"
    else
    pwsql="select PASSWORD from dba_users where username='${DEST}';"
    fi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width