hi all,
Just a quick question...probably very simple answer as well!
We have two types of people at our company using an Oracle Database, those with complex passwords and those without (we are slowly moving everyone across to using complex passwords) - the users can be differentiated by their Oracle profile. Problem being that to save user confusion we want everyone to be reset to the same password. Currently, the person who reset the password needs to establish whether the user is a complex passworder or not before connecting to the database server using TELNET and running a unix script.
So to make it more user friendly I was going to use the SELECT PROFILE FROM DBA_USERS so that the person resetting the password doesnt need to check first to see which type of reset the user needs. However, SQL is not my strong point so I was hoping someone could advise if the script I have below is going to work?
ORACLE_SID="LIVE"
. oraenv
echo "database is set to " $ORACLE_SID
echo "Enter the name of end user to be reset "
read USER
export USER
echo "User being changed is " $USER
if [ $USER = "system" ] || [ $USER = "SYSTEM" ]
then
echo "You should NOT be changing sys or owner "
echo "Aborting run"
exit 1
fi
if [ $USER = "sys" ] || [ $USER = "swift_owner" ]
then
echo "You should NOT be changing sys or owner "
echo "Aborting run"
exit 1
fi
if [ $USER = "SYS" ] || [ $USER = "SWIFT_OWNER" ]
then
echo "You should NOT be changing sys or owner "
echo "Aborting run"
exit 1
fi
echo "Changing user $USER password to reset "
sqlplus << EOF1
<username>/<password>
IF
(select profile from dba_users where username = upper($user)) = ISPROF
THEN
alter user $user profile DEFAULT;
alter user $user identified by reset;
alter user $user profile ISPROF;
alter user $user password expire;
ELSE
alter user $user identified by reset;
alter user $user password expire;
commit;
exit;
EOF1