IBM i user authentication with SQL

[worksofliam]

It is usual for a web app, specifically on IBM i, to authenticate against user profiles on their IBM i.

Most IBM i shops don't have a typical users table with encrypted password columns.

There is no particular easy way to authenticate users for your apps. You can use a combination of operating system functions to get this to work.

This method consists of two pieces:

1. An ILE RPGLE program which calls QSYGETPH to authenticate a user by name and password combination
2. An SQL stored procedure to call the program.

They both return a char(1) output parameter which indicates whether the user and password combination is valid. You may want to increase the password length column.

The reason we want the stored procedure is so we can authenticate users from anywhere - for example, a PHP, Python or Node.js web app.

If you have any suggestions for improvements to these snippets, please feel free to share them!

ILE RPG Program

**FREE

Dcl-Pi AUTH;
  pUserID   Char(10);
  pPassword Char(32);
  Result    Char(1);
End-Pi;

Dcl-PR GetProfile  ExtPgm('QSYGETPH');
  UserID         Char(10)   const;
  Password       Char(32767) const options(*varsize);
  Handle         Char(12);
  ErrorCode      Char(256)  Options(*Varsize : *NoPass);
  PswLength      Int(10)    const Options(*NoPass);
  CCSIDCode      Int(10)    const Options(*NoPass);
End-PR;

Dcl-Pr CloseProfile ExtPgm('QSYRLSPH');
  Handle         Char(12);
End-Pr;

Dcl-S ResultHandle Char(12);

Dcl-S errorOut Char(256);
Dcl-S pwLength Int(3);

pwLength = %Len(%Trim(pPassword));

//pPassword = %Trim(pPassword);
ResultHandle = '';
Result = *Off;

GetProfile(pUserID:pPassword:ResultHandle:errorOut:pwLength:37);

//Indicates is incorrect
If ResultHandle <> x'000000000000000000000000';
  Result = *On;
  //We don't want to keep handles open
  
  CloseProfile(ResultHandle);
Endif;

*InLR = *On;
Return;

SQL procedure

create or replace procedure SCHEMA.USER_AUTHENTICATE (IN username Char(10), IN password Char(32), OUT result Char(1)) 
LANGUAGE RPGLE  
EXTERNAL NAME SCHEMA.AUTH GENERAL;

[jsanguin]

Thanks for this excellent tip. I am working in a modernization project and this will certainly be our login validation. Question here is if you have anything similar we could use for changing the password. Thanks for your help.

[carlosir]

Thank you Liam!!!. Carlos