Imported files from progress to Sql/MySql cant use cridentials?

[Cappie]

Our company serves the medical industry with services from hospital management, hospital stock, practise managent, pharmacy stock etc. As well as a wellness module. Currently only patients in Hospital or clinics assessments can be done. The client requested a stand-alone app where nurses can go out in the hostels to do assessments. Currently it is done on paper, and the captured at the hospital.

I was instructed to do a stand-alone app to serve this purpose. Due to permissions,NOT all nurses/members using the system is allowed to see HIV statusses of patients.

Well, i created a MySql db, and a datadump from progress Unix database.

App is written in C#, with a MySql database. Now, in order to Implement the security, i need to validate the Progress Password. We Use the encopde function to generate the encoded password, wondering now if there is someone from Progress that has got the algorithm/function i can implement in MySql, or a DLL i can reference in C# in order to compare the Passwords?

 

[MaximMonin]

I dont think it is good security to compare encoded password in dump-files to value entered by user.

Alternative method is create web service with proxygen/wsa and call it from c#.
ProgressCheckLogin.p

define input parameter login as character.
define input parameter pwd as character.
define output parameter OutMessage as character initial "ERROR".

define variable state as logical.
state = SETUSERID ( login, pwd, "dbname" ).
if state = false then
do:
pause 1.
return "".
end.
Outmessage = "OK".

 

[Cappie]

Thanx for the swift reply

We are in the process of re-writing the system, we have a couple of webservice calls, that uses pro-datasets. The proof-of-concept is done already, we are just waiting for a sigh-off for project kick-off.
The re-write entails a re-design regarding security, and the stock mainenance around the pharmacy-module, as for South Africa changed legislation around monitoring of Scheduled drugs.

This C# app will go offsite, with no connection at all to the DB. So webservices is obsolete.

The usernames we got, but there is no way to validate the password. We can mimic a login, but they will soon fugure out that we do not validate the passwords.
See single line from datadump for gsm_user table:

"D"|"ADM00"|"xpjlwdlaOKipfzLi"|"MAGRIET VAN BILJON - P9146506".

The funny character string is the encoded password.
Using MySql was the obvious solution, because it is opensource, and we are only using about 11 tables from over 500+ tables from the db.
Now, just to purchace a windwos version of Progress DB, appserver licenses etc. just for a couple of trips to a mine hostel, once a month is not financially viable.

 

[Cappie]

Hi requested a acceptance on skype.....

 

[RealHeavyDude]

The algorithm Progress uses to encode password with the 4GL ENCODE function is one way and is not disclosed. This functionality remained unchanged since the beginning of the '90s. Plus, the usage of the _User table to authenticate users is not enforced because Progress utilizes a revoke philosophy. It's up to the application developer to decide whether users should be authenticated against the Progress database at all and how this authentication is implemented. There is no way that you mimic the logic of the 4GL ENCODE function in a different technology without Progress disclosing the logic - which I doubt that they will. Plus, there is no such thing as a DLL available to the outside world.

As you don't talk about the Progress/OpenEdge version is hard to say whether a re-design of the authentication for the Progress application to use a more common encryption algorithm, which is supported by other technologies too, is possible. OE 10.0B introduced encryption and OE 10.1A introduced the client principal object.

Regards, RealHeavyDude.

 

[Cappie]

That argument i truely understand. I think this is where i lose everyone. I dont want to re-create the function, meaning, i want to make a function call like :
if progress.verifyPassword(textbox1.text, "dbfield") == true
messagebox.show("login ok")

I dont need to know the inner workings of progress.verifyPassword. I dont want to use encode to encode my own password, i just want to verify.
In progress it would be something like
def var pw as character no-undo.
def var pass as character initail "password" no-undo.
assign pw = encode(pass) no-error.
if pw = "encrypteddbentry" then message "Login Ok".

That is why i said we are re-doing the logic around security during the re-write, but until then we still need to verify the passwords that we imported in the MySql database.
We are using Progress 9.1d, and 9.1e, the character interface and the appserver that comes standard with the pakages. The re-write utilizes webservice calls, and Pro-Datasets, that we will be able to verify the passwords with.

We use a Delphi app that make use of the proxy generator, but because the notebooks that will go off-site, this will pose a problem. The database structure for the wellness module is different from the current production DB, whick will only hold 11 tables, where-as the production DB is close to 530 tables.

Basicly : ProgressDB(Unix) --> Dump Files --> Import File-MySql(Windows) --> Assessments Done(Updates,Questionares)Offsite -->MySql(Dump assesed Files)-->ImportFiles(ProgressDB) .

Problem exist here.... The Progress version is for Unix only. Now, to tell client to buy Windows version of Progress ------ NO.

MAYBE, if someone from progress can create a dll, which we can just reference, it would be great. If i wanted to encode myself, i would have rather used something else.
The imported _user table will be just as a reference, we are not going to add users, this will be added in the progress DB, this app would just be an extension.

 

[RealHeavyDude]

Well, if your customer is not willing to pay anything but your time then you're stuck. IMHO the logical solution would be to implement the Windows/Notebook solution in Progress too - or - as Maxim already suggested, use a web service which would do the authentication.

Personally, I doubt that anybody at Progress will be willing to provide such a DLL to you unless you're ( or your client is ) willing to play lot's of money for it ...


Regards, RealHeavyDude.

 

[MaximMonin]

You have to write a lot of code to maintain application. It costs a lot to develop and costs a lot to support (replication to offsite non-progress application is not easy procedure). You said Interenet is not cheap in africa. But I personally think that for client is better to buy wireless inet and do not replicate data. WS authentification requires to send 200 bytes to server and 200 bytes back to client.
The same way you can replicate other tables (if cant offord realtime online ws db connection).
here is script to output any progress table through soap xml:

define input-output parameter TableName as character.
define input parameter MaxRecCount as integer.
define input parameter sortby as character.
define input parameter filtertext as character.

define output parameter TABLE-HANDLE TableData.

define variable i as integer.
define variable j as integer.
define variable fieldcount as integer.
DEFINE variable hQuery           AS HANDLE NO-UNDO.
DEFINE variable hBuffer          AS HANDLE NO-UNDO.
DEFINE VARIABLE hBufferField     AS HANDLE NO-UNDO.
DEFINE variable hQueryData       AS HANDLE NO-UNDO.
DEFINE variable hBufferData      AS HANDLE NO-UNDO.
DEFINE VARIABLE hBufferFieldData AS HANDLE NO-UNDO.

define variable sqlquery as character.
sqlquery = "FOR EACH " + TableName + " NO-LOCK".
filtertext = trim(filtertext).
if filtertext <> "" then
  sqlquery = sqlquery + " where " + filtertext.
if trim(sortby) <> "" then
  sqlquery = sqlquery + " by " + TableName + "." + sortby.

find first system._File where system._File._File-Name = TableName NO-LOCK NO-ERROR.
if not available system._File then RETURN.

create temp-table TableData.

fieldcount = 0.
FOR EACH system._Field OF system._File NO-LOCK By system._Field._Order:
  TableData:ADD-NEW-FIELD (system._Field._Field-name, system._Field._Data-Type). 
  fieldcount = fieldcount + 1.
END.
TableData:TEMP-TABLE-PREPARE ("TableData").

CREATE QUERY hQuery.
CREATE BUFFER hBuffer FOR TABLE TableData:DEFAULT-BUFFER-HANDLE.
hQuery:SET-BUFFERS(hBuffer).

CREATE QUERY hQueryData.
CREATE BUFFER hBufferData FOR TABLE TableName.
hQueryData:SET-BUFFERS(hBufferData).
hQueryData:QUERY-PREPARE(sqlquery).
hQueryData:QUERY-OPEN.
hQueryData:GET-FIRST ().
repeat:
  if hQueryData:QUERY-OFF-END then leave.

  hBuffer:BUFFER-CREATE().

  DO j = 1 TO fieldcount:
    hBufferField = hBuffer:BUFFER-FIELD(j).
    hBufferFieldData = hBufferData:BUFFER-FIELD(j).
    hBufferField:BUFFER-VALUE = hBufferFieldData:BUFFER-VALUE.
  END.
  MaxRecCount = MaxRecCount - 1.
  if MaxRecCount <= 0 then leave.
  hQueryData:GET-NEXT ().
END.