DEV Community

Cover image for Zero-Ticket Access Provisioning on IBM i: An RPGLE Pattern
Jay
Jay

Posted on

Zero-Ticket Access Provisioning on IBM i: An RPGLE Pattern

#as400 #rpgle #ibmi

If you run IBM i long enough, you'll get this ticket eventually: give the new hire the same access as someone already on the team. A security admin logs in, copies a menu authority, copies a library authority, resets a password, emails it out. Six months later nobody can say who approved what, or when.

Here's how we automated that whole flow directly on IBM i, using RPGLE, embedded SQL, and the IFS and nothing else. No middleware, no extra servers, no message queue. This is a real production program with the names swapped out; the mechanism is what matters, not the specific file or program names.

The shape of the problem

Every request boils down to two questions:

  1. Who needs access?
  2. Whose access do we copy, the "model" user?

That second part is what makes this manageable. You never have to define what access actually means in the abstract. You just say "make user A look like user B" and let the clone operation do the work.

Architecture at a glance 

[Request form / intake system]
        │  writes a JSON file
        ▼
/home/access/inbox/*.json   ◄── watched by a scheduled RPGLE job
        │
        ▼
1. Parse the request (DATA-INTO)
2. Resolve identities (does target exist? does model exist?)
3. Branch: new account / refresh existing / reject
4. Clone the profile (CL wrapper around CPYUSRPRF)
5. Clone the entitlement rows, stamped with source + timestamp
6. Write a history record + emit a JSON audit event
7. Notify requester + manager by email
8. Archive the source file, so the next poll doesn't reprocess it
Enter fullscreen mode Exit fullscreen mode

No trigger, no data queue — the filesystem is the queue. A job schedule entry runs the program periodically, and each run drains whatever landed in the inbox since the last pass.

Step 1 — Watch a folder with SQL, not a directory API

Rather than reaching for the classic IFS APIs, declare a cursor over QSYS2.IFS_OBJECT_STATISTICS, a table function that turns a directory listing into rows you can filter with plain SQL:

EXEC SQL DECLARE REQFILES CURSOR FOR
      SELECT CAST(PATH_NAME AS VARCHAR(500)) FROM
  TABLE(QSYS2.IFS_OBJECT_STATISTICS(
  start_path_name => :INBOX_PATH))
   WHERE object_type = '*STMF';
Enter fullscreen mode Exit fullscreen mode

One query gets you every request file waiting to be processed. No manual string-walking of directory entries.

Step 2 — Parse JSON natively

Since IBM i 7.2 (with PTFs) / 7.3+, RPG's DATA-INTO opcode paired with the YAJL-based parser service program deserializes a JSON file straight into a data structure. No token walking, no manual scanning:

dcl-ds requestDoc qualified;
  requestDate     varchar(10);
  targetUserId    varchar(10);
  targetUserName  varchar(100);
  targetUserEmail varchar(100);
  modelUserId     varchar(10);
  targetSystem    varchar(10);
  siteCode        varchar(8);
  managerEmail    varchar(100);
end-ds;

data-into requestDoc %DATA(%trim(reqPath)
      :'doc=file case=convert countprefix=num_ +
      allowmissing=yes allowextra=yes trim=none')
      %PARSER('YAJL/YAJLINTO': '{ "document_name": "requestDoc" }');
Enter fullscreen mode Exit fullscreen mode

Declare the contract once, as a qualified data structure, and let the parser fill it. It's a much bigger upgrade over hand-rolled JSON parsing than people expect, and it's been usable in production for years. Most RPG shops just haven't run into it yet.

Multi-partition tip: if the same inbox is visible to more than one logical partition (a shared IFS mount, replicated files), compare requestDoc.targetSystem against CURRENT SERVER and skip any file that isn't addressed to the partition currently running. Each partition's job only claims what's its own.

Step 3 — Resolve identity, decide the outcome

Two existence checks against QSYS2.USER_INFO — IBM's SQL services view over user profiles — settle everything:

SELECT COUNT(*) INTO :modelExists
  FROM QSYS2.USER_INFO WHERE AUTHORIZATION_NAME = :modelUserId;
SELECT COUNT(*) INTO :targetExists
  FROM QSYS2.USER_INFO WHERE AUTHORIZATION_NAME = :targetUserId;
Enter fullscreen mode Exit fullscreen mode
Model exists Target exists Outcome
yes no New account — clone, generate a password
yes yes Refresh — reclone entitlements only
no — Reject — nothing to model access on

That three-way branch is basically the whole decision tree here. Everything past this point is just execution and record keeping.

Step 4 — Provision, and reset the password properly

The actual profile work gets delegated to a CL wrapper around CPYUSRPRF. RPGLE doesn't need to know the internals, it just passes the two IDs, a display name, and the target library:

D PROVISION_USER  PR                  extpgm('PROVUSR')
D   OPT                          1A   // '1' = create, '2' = refresh
D   TARGET_ID                   10A
D   MODEL_ID                    10A
D   NAME                       100A
D   SITE                        10A
D   LIB                         10A
Enter fullscreen mode Exit fullscreen mode

For a brand-new account, generate a random password and force an immediate change:

PASSGEN(pwd);
cmdString = 'CHGUSRPRF USRPRF(' + %trim(targetUserId) +
            ') PASSWORD(' + %trim(pwd) + ') PWDEXP(*YES)';
EXEC SQL CALL QSYS2.QCMDEXC(:cmdString);
Enter fullscreen mode Exit fullscreen mode

Then verify. Don't just trust that the CL call succeeded:

SELECT COUNT(*) INTO :confirmed
  FROM QSYS2.USER_INFO WHERE AUTHORIZATION_NAME = :targetUserId;
Enter fullscreen mode Exit fullscreen mode

Only write the audit history record after this comes back non-zero.

Step 5 — Clone entitlements with an audit stamp baked in

This is the part that makes the whole thing defensible later. Don't just copy the access, copy it in a way that records who granted it and why:

BEGSR CLONE_ENTITLEMENTS;
  EXEC SQL SELECT COUNT(*) INTO :alreadyGranted FROM APPAUTL
            WHERE USRID = :targetUserId;
  IF alreadyGranted = 0;
     EXEC SQL DECLARE MODELAUTH CURSOR FOR
      SELECT PGMID FROM APPAUTL WHERE USRID = :modelUserId;
     EXEC SQL OPEN MODELAUTH;
     EXEC SQL FETCH FROM MODELAUTH INTO :pgmId;
     DOW SQLCODE = 0;
       EXEC SQL INSERT INTO APPAUTL VALUES (:targetUserId, :pgmId,
                 'A', :auditDate, :auditTime, 'ACCPROV', 'ACCPROV001');
       EXEC SQL FETCH NEXT FROM MODELAUTH INTO :pgmId;
     ENDDO;
  ENDIF;
ENDSR;
Enter fullscreen mode Exit fullscreen mode

Each inserted row carries more than the raw entitlement: the target user, an active flag, the date and time, and two fixed literals identifying the automation and the program that made the grant. When an auditor asks why someone has a particular access, you can point at the row and answer instead of shrugging.

The alreadyGranted = 0 guard matters more than it looks: it's what stops a retried run from duplicating every grant.

Step 6 — Design the audit trail as three independent trails, not one

One log isn't enough on its own. We build three, because each one answers a different question later:

  1. A history file on the IBM i itself — the record of record, queryable with SQL, for "what did we grant and when."
  2. Stamped rows in the entitlement table — the record inside the access-control system itself, so a security review doesn't need to cross-reference a separate log to see automation-granted access.
  3. A structured JSON event shipped to an external sink (Table Storage, a SIEM, a log pipeline — whatever your org centralizes on): 
yajl_beginObj();
 yajl_addChar('PartitionKey': 'AS400_ACCESS');
 yajl_addChar('RowKey': %trim(requestId));   // ← a real unique key, not a placeholder
 yajl_addChar('Timestamp': %char(%Time()));
 yajl_addChar('TargetSystem': %trim(requestDoc.targetSystem));
 yajl_addBool('AccountCreated': created);
 yajl_addChar('ModeledAfter': %trim(requestDoc.modelUserId));
 yajl_addChar('SiteCode': %trim(requestDoc.siteCode));
yajl_endObj();
postAuditEvent(yajl_copyBufStr());
Enter fullscreen mode Exit fullscreen mode

One mistake we made early on: don't hardcode or leave a placeholder in the row key. If your audit store enforces key uniqueness (Table Storage does), a static RowKey means every new event quietly overwrites the last one. You won't find out until an audit asks for history that isn't there.

Step 7 — Notify, and split the credential in two

Two separate calls, not one combined email:

NOTIFY_STATUS(targetEmail : managerEmail : subject);          // status, both parties
NOTIFY_SECURE(managerEmail : credSubject  : 'Login: ' + targetUserId);
NOTIFY_SECURE(managerEmail : credSubject  : 'Password: ' + pwd);
Enter fullscreen mode Exit fullscreen mode

Splitting the login ID and the password into two messages means a single intercepted email doesn't hand over a usable credential pair. It's a modest control, not a substitute for a real secrets-delivery channel. If you're building this today, seriously consider routing the password through a self-service reset link or a secrets vault notification instead of plain-text email, split or not.

Step 8 — Make the file lifecycle idempotent

A polling job needs a way to stop seeing what it already handled:

cmdString = 'CPY OBJ(''' + %trim(reqPath) + ''') TODIR(''/home/access/archive'') REPLACE(*YES)';
EXEC SQL CALL QSYS2.QCMDEXC(:cmdString);
cmdString = 'RMVLNK OBJLNK(''' + %trim(reqPath) + ''')';
EXEC SQL CALL QSYS2.QCMDEXC(:cmdString);
Enter fullscreen mode Exit fullscreen mode

Archive before you unlink, always with REPLACE(*YES) so a retried archive doesn't fail on a name collision. And because this kind of batch program typically compiles COMMIT(*NONE), there's no transaction boundary wrapping the whole sequence. The entitlement-clone guard from Step 5 is your real safety net if a run dies partway through a file.

None of this is really about RPGLE

Swap out the language and you're left with the same three ideas: a watch-folder intake, clone-from-a-model provisioning, and logging in three places instead of one. A few things from this I'd carry into any platform:

  • If a request gets rejected before your business logic even runs, don't let that failure be invisible. Malformed or misrouted requests still need a log line or an email. The silent failures are the ones that eat a whole afternoon six months later, when nobody remembers why an account never got created.
  • Stamp every automated grant with where it came from. "User X has access" is a much weaker record than "user X has access, granted by this job, from this template, on this date."
  • Generate a real unique key for every audit event at write time, never a placeholder. It's the cheapest insurance you'll ever buy against a debugging session down the line.

If you're on IBM i and still pushing access requests through a ticket queue, this is a weekend build, not a migration project. It pays for itself the first time compliance asks for every grant from last quarter.

Top comments (0)