"""
resetPwd.py - Changes an Oracle account's password.

October 21, 2006 by Catherine Devlin
(catherine.devlin@gmail.com, http://catherinedevlin.blogspot.com/)

Purpose of This Script
======================

Command-line utility for password management for an Oracle database 
helpdesk
("I forgot my password, can you reset it?")

  - Enforces password rules of your choice
  - Attempts to help the user find the correct username even if the 
precise spelling is not known
  - Accepts AS SYSDBA/AS SYSOPER logins.

Installation
============

If installed with sqlWrap, the sqlWrap installer automatically puts 
resetPwd.py into your
Python Scripts directory:
https://sourceforge.net/projects/sqlwrappy/

resetPwd.py is a free-standing script, however, and may be copied 
independently
and run from anywhere.  You can copy it directly from 
http://sqlwrappy.sourceforge.net/resetPwd.py

resetPwd.py requires that cx_Oracle be installed::

    cx_Oracle: http://www.cxtools.net/default.aspx?nav=cxorlb

How To Use This Script
======================

At the command prompt, run
`python resetPwd.py`

You will be prompted for username, password, and database name.  The 
database
name should be available in your TNSNAMES.ORA file - resetPwd.py has not 
been
tested with Oracle Easy Connect.

A standard set of password compliance rules is included within the 
script
(search for ComplianceRules).  To adapt them to your installation's 
rules,
add appropriate functions to the script and append them to the 
ComplianceRules
list.
"""
print 'Oracle password reset utility\n\n'

import getpass, re
from os import environ
defaultDb = environ.get('ORACLE_SID')
try:
    import cx_Oracle
except ImportError:
    print '\ncx_Oracle is required - get from www.cxtools.net\n'
    raise

connectRoles = {cx_Oracle.SYSDBA: re.compile('(\S+)\s+AS\s+SYSDBA$', 
re.IGNORECASE), 
                cx_Oracle.SYSOPER: re.compile('(\S+)\s+AS\s+SYSOPER$', 
re.IGNORECASE)}
def findConnectRole(s):
    for (connectRoleCode, connectRoleRe) in 
sorted(connectRoles.items()):
        matchObj = connectRoleRe.search(s)
        if matchObj:
            return (matchObj.group(1), connectRoleCode)
    return (s, 0)
def getLogin():
    uname = passwd = dbname = ''
    connectRoleCode = 0
    while not uname:
        uname = raw_input('Your username > ').strip()
    components = uname.split('@')
    if len(components) > 1:
        uname, dbname = (x.strip() for x in components[:2])
    components = uname.split('/')
    if len(components) > 1:
        uname, passwd = (x.strip() for x in components[:2])
    while not passwd:
        passwd = getpass.getpass('password > ').strip()
    if not dbname:
        dbname = raw_input('database (%s) > ' % (defaultDb)).strip() or 
defaultDb
    (uname, connectRoleCode) = findConnectRole(uname)
    if not connectRoleCode:
        (dbname, connectRoleCode) = findConnectRole(dbname)
    return (uname, passwd, dbname, connectRoleCode)

while True:
    try:
        ora = cx_Oracle.Connection(*getLogin())
        break
    except (RuntimeError, cx_Oracle.DatabaseError), errmsg:
        print "Error attempting to log in:\n%s" % (errmsg)
curs = ora.cursor()

def choose(nhits):
    choice = raw_input('User to reset (1-%d) ===> ' % (nhits))
    choice = choice.strip().lower()
    if (not choice) or choice[0] == 'q':
        return None
    try:
        choice = int(choice)
    except ValueError:
        return choose(nhits)
    if 0 < choice <= nhits:
        return choice
    return choose(nhits)

def seekGuess(guess):
    guess = guess.strip().upper()
    curs.execute("""SELECT username 
                    FROM all_users 
                    WHERE SOUNDEX(username) = SOUNDEX(:guess)
                    OR SOUNDEX(SUBSTR(username,1,length(:guess))) = 
                       SOUNDEX(:guess)""",
                 {'guess': guess})
    hits, nhits = [None], 0
    for row in curs:
        if row[0] == guess:
            return guess
        nhits += 1
        hits.append(row[0])
    if nhits > 0:
        for n in range(1, nhits+1):
            print '  %d    %s' % (n, hits[n])
        chosen = choose(nhits)
        if chosen:
            return hits[chosen]
    else:
        print 'No user %s found' % (guess)
    return None
        
def pickUser():
    userGuess = raw_input('Username to reset (best guess; blank to quit) 
> ')
    return userGuess and seekGuess(userGuess)

# Define all compliance complianceRules to be applied to passwords.
# No return value == no rule violation (password OK);
# any return value is used as the error message for explaining password
# failure.
nonAlphanumericRe = re.compile('[^A-Za-z0-9]')
def alphanumericOnly(passwd):
    if nonAlphanumericRe.search(passwd):
        return 'Only numbers and letters are permitted'
def startWithLetter(passwd):
    try:
        int(passwd[0])
        return 'Password must begin with a letter'
    except ValueError:
        None
numberRe = re.compile('\d')
def containNumber(passwd):
    if not numberRe.search(passwd):
        return 'Password must contain at least one digit'
def longEnough(passwd):
    minLen = 6
    if len(passwd) < minLen:
        return 'Minimum password length is %d characters' % minLen
# end password check functions
# password rule functions must be listed here to be applied
complianceRules = [alphanumericOnly, startWithLetter, containNumber, 
longEnough]

def legal(passwd):
    endResult = True
    for check in complianceRules:
        failure = check(passwd)
        if failure:
            print failure
        endResult = endResult and (not failure)
    return endResult

def changepasswd(user):
    while True:
        passwd = raw_input('New password for %s >>> ' % (user))
        passwd = passwd.strip()
        if passwd:
            if legal(passwd):
                try:
                    curs.execute('ALTER USER %s IDENTIFIED BY %s ACCOUNT 
UNLOCK' % (user, passwd))
                    print 'Password for %s changed to %s' % (user, 
passwd)
                    break
                except (cx_Oracle.DatabaseError), errmsg:
                    if 'ORA-01031' in str(errmsg):
                        print '\nA DBA must GRANT ALTER USER TO your 
Oracle account for you to change passwords.\n'
                    print errmsg

user = pickUser()
while user:
    changepasswd(user)
    user = pickUser()