Kbase P6212: How to change Userid for database connection through AppServer
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
04/11/2008 |
|
Status: Verified
GOAL:
How to change Userid for database connection through AppServer
FACT(s) (Environment):
Progress 9.1x
All Supported Operating Systems
FIX:
In practice it is often desirable to store in the database, the user who updates a record. In a client server standard connection the userid is always available from the USERID function. But in an AppServer application, the USERID function will return the user who started the AppServer broker. If the user is required to enter login information when accessing the AppServer broker, that information has to be stored in order to indicate who updated, created, or deleted a record.
Usually a database trigger is used to take care of this job.
The trigger will determine the user from the USERID and will assign the value to a field in that record or possibly in an audit table record.
Step-by-step instructions:
1) Create a sub-directory, adecomm, in your source structure. For example, you have the source in c:\mysrc, so you will create c:\mysrc\adecomm.
Copy adecomm.pl from %DLC%\src to c:\mysrc. Extract all the files
from the library with: PROLIB adecomm.pl -extract *.*
2) Start a Progress client using the following startup parameters:
prowin32.exe -debugalert -inp 18000 -mmax 65534 -tok 1600 -zn
From Tools, open the AppBuilder. Open ProTools and add c:\mysrc in
the PROPATH if is not already there.
Open as-utils.w in the AppBuilder.
Add a new procedure getSecurityInfo as follows:
/*---------------------------------------------------------------
Purpose:
Parameters: <none>
Notes:
-----------------------------------------------------------------*/
DEF OUTPUT PARAM pcUser AS CHARACTER NO-UNDO.
DEF OUTPUT PARAM pcPasswd AS CHARACTER NO-UNDO.
pcUser = usr-id.
pcPasswd = psswrd.
END PROCEDURE.
Save and compile as-utils.w.
Close the AppBuilder. Close Progress Editor.
NOTE: as-utils.w is loaded as a super procedure by data.p so the
methods are available in any SmartDataObject.
Open your regular AppBuilder used to develop your application.
3) In your SDO, add the following internal procedures:
PROCEDURE setSecurityInfo:
/*----------------------------------------------------------
Purpose:
Parameters: <none>
Notes:
-----------------------------------------------------------*/
DEF INPUT PARAM pcUser AS CHARACTER NO-UNDO.
DEF INPUT PARAM pcPasswd AS CHARACTER NO-UNDO.
DEF VAR hAS AS HANDLE.
hAS = DYNAMIC-FUNCTION('getASHandle':U).
IF VALID-HANDLE( hAS ) THEN
RUN setASSecurityInfo IN hAS ( pcUser, pcPasswd ).
END PROCEDURE.
PROCEDURE setASSecurityInfo:
/*----------------------------------------------------------
Purpose:
Parameters: <none>
Notes:
-------------------------------------------------------------*/
DEF INPUT PARAM pcUser AS CHARACTER NO-UNDO.
DEF INPUT PARAM pcPasswd AS CHARACTER NO-UNDO.
DEF VAR vl AS LOGICAL NO-UNDO.
vl = SETUSERID( pcUser, pcPasswd, DBNAME ).
/* if user and password are valid( defined in _User ) then
SETUSERID will change the user
for Database connection otherwise returns FALSE */
/* since the server if fully allocated to the client, any call of
USERID will return the value set by SETUSERID */
END PROCEDURE.
4) Open the SmartWindow or SmartDialog which contains your SDO.
Assuming the handle of the SDO is h_cust_q, add the following code
to initializeObject :
/* Code placed here will execute AFTER standard behavior. */
DEF VAR vcUser AS CHARACTER NO-UNDO.
DEF VAR vcPasswd AS CHARACTER NO-UNDO.
RUN getSecurityInfo IN h_cust_q ( OUTPUT vcUser, OUTPUT vcPasswd )
NO-ERROR.
/* getSecurityInfo is the method defined in as-util.w */
IF ERROR-STATUS:ERROR THEN LEAVE.
/* setSecurityInfo is defined in SDO */
RUN setSecurityInfo IN h_cust_q ( vcUser, vcPasswd ) .
5) A trigger or any 4GL code running on the AppServer side should be
able to ge.t current user with the USERID function.
LIMITATIONS: This method works if users defined in the Database are the same users which have access to AppServer. If this is not the case then the SDO should define a New Shared variable and store the userid in it. Then the trigger will access that user variable through Shared Variables. The code change has to be in setASSecurityInfo internal procedure..