Status: Verified
GOAL:
How to programmatically change another users password in _user table
GOAL:
How to programmatically change a Progress Database user's id and password?
GOAL:
Can anybody change the password other than the user himself/herself for database security.
GOAL:
Can a user change another user's password?
FACT(s) (Environment):
All Supported Operating Systems
Progress/OpenEdge Versions
FIX:
The Progress/OpenEdge database does not allow users (regardless of their permission level) to change another user's password in the _user table. The only time this can be done is when creating a new user so the typical solution for this is to delete the _user record then re-add it with the new password.
In most cases this is done because a user has lost or forgotten his password and this is the correct solution for that. Alternatively you typically want the user to log into the system and change their own password.
In some limited cases however an administrator may need to be able to change a user's password, perhaps to reset it, and this solution is designed to demonstrate how to accomplish this without deleting the associated _user record. This solution assumes that the connected user must be an administrator and that he has knowledge of, or access to, the user's original password.
This solution provides example code for switching users, changing a password, then switching back to the original user.
The following code is meant to be broken up into three separate programs:
/* chgPwd.p */
DEFINE VARIABLE cStatus AS CHARACTER NO-UNDO.
RUN chPwd.p ( INPUT "<ldbname>", /* Logical DB Name */
INPUT "<user>", /* User account to change */
INPUT "<admin pwd>", /* Current user's password */
INPUT "<Original Password>", /* Old Password */
INPUT "<New Password>", /* New Password */
OUTPUT cStatus ). /* Status */
MESSAGE cStatus
VIEW-AS ALERT-BOX INFO BUTTONS OK.
/* chPwd.p */
DEFINE INPUT PARAMETER pcDatabase AS CHARACTER NO-UNDO.
DEFINE INPUT PARAMETER pcUser AS CHARACTER NO-UNDO.
DEFINE INPUT PARAMETER pcAdminPass AS CHARACTER NO-UNDO.
DEFINE INPUT PARAMETER pcUserPassOld AS CHARACTER NO-UNDO.
DEFINE INPUT PARAMETER pcUserPassNew AS CHARACTER NO-UNDO.
DEFINE OUTPUT PARAMETER pcStatus AS CHARACTER NO-UNDO.
DEFINE VARIABLE cCurrentUser AS CHARACTER NO-UNDO.
DEFINE VARIABLE lSuccess AS LOGICAL NO-UNDO.
DEFINE VARIABLE lAdministrator AS LOGICAL NO-UNDO.
/* Ensure the database is connected. */
IF NOT CONNECTED(pcDatabase) THEN DO:
pcStatus = "Database ~'" + pcDatabase + "~' not connected.".
RETURN.
END.
cCurrentUser = USERID(pcDatabase).
CREATE ALIAS "MYDB" FOR DATABASE VALUE(pcDatabase).
/* Ensure the user is an administrator first. */
RUN chkAdmn.p ( INPUT cCurrentUser,
OUTPUT lAdministrator ).
IF NOT lAdministrator THEN DO:
pcStatus = "You must be an administrator to perform this function.".
RETURN.
END.
TXN-BLK:
DO ON ERROR UNDO TXN-BLK, LEAVE TXN-BLK TRANSACTION:
FIND FIRST _user
WHERE _user._userid EQ pcUser
. EXCLUSIVE-LOCK NO-WAIT NO-ERROR.
IF AVAILABLE _user AND
NOT LOCKED _user THEN DO:
/* Temporarily switch users, so the DB allows you to make the change. */
lSuccess = SETUSERID(pcUser,pcUserPassOld,pcDatabase) NO-ERROR.
IF NOT lSuccess THEN DO:
pcStatus = "Could not authenticate " + pcUser + " with old password.".
UNDO TXN-BLK, LEAVE TXN-BLK.
END.
/* Assign the user's new password. */
_user._password = ENCODE(pcUserPassNew).
/* Switch back to the original user. */
lSuccess = SETUSERID(cCurrentUser,pcAdminPass,pcDatabase) NO-ERROR.
IF NOT lSuccess THEN DO:
pcStatus = "Could not switch back to original user ~'" + cCurrentUser +
"~' using password provided. Backed out transaction.".
UNDO TXN-BLK, LEAVE TXN-BLK.
END.
pcStatus = "Password successfully changed for " + pcUser.
END.
END.
/* chkAdmn.p */
DEFINE INPUT PARAMETER pcUser AS CHARACTER NO-UNDO.
DEFINE OUTPUT PARAMETER plAdministrator AS LOGICAL NO-UNDO.
DEFINE VARIABLE cAdministrators AS CHARACTER NO-UNDO.
DEFINE VARIABLE cAdministrator AS CHARACTER NO-UNDO.
DEFINE VARIABLE iAdministrator AS INTEGER NO-UNDO.
/* When the security administrators are changed for a db using the
Data Administration tool, those users are granted a number of
permissions on _file and _field. Simply checking one of the
appropriate permissions on _file should suffice to determine if
the specified user is a security administrator. */
FIND MYDB._file "_file".
FIND MYDB._field "_can-write" OF MYDB._file.
/* Get the list of administrators using the ALIAS we created
outside of here. */
cAdministrators = MYDB._field._can-write.
plAdministrator = (cAdministrators EQ "*").
IF NOT plAdministrator THEN
DO iAdministrator = 1 TO NUM-ENTRIES(cAdministrators):
cAdministrator = ENTRY(iAdministrator,cAdministrators).
/* Check for negative token which clearly indicates that
this user is NOT an administrator. */
IF cAdministrator EQ "!" + pcUser THEN DO:
plAdministrator = FALSE.
RETURN.
END.
/* Use MATCHES here just in case a range expression was used to set
administrator to users with a matching expression i.e. 'user*'. */
plAdministrator = (pcUser MATCHES cAdministrator).
IF plAdministrator THEN
RETURN.
END.
.