Having recently watched the Helpsystems annual State of IBM i Security study, along with listening to Robin talk about it on the Incredible i Show, it seemed a good time to reflect on how I originally got introduced to the need for security. As the title suggests my solution at the time was a bit rudimentary, but you have to start somewhere!

To give you a bit of background, imagine a customer on a Power5 system running V5R4 looking at moving to a Power8. They’ve been on the IBM platform for years and modernisation isn’t a term they like (once they were on the power8 we had to set QSPCENV so they could continue using some S36 code!)

They did however take the opportunity to move from security level 20 to 30, so it’s not all bad news. In a mostly unrelated project, we even managed to set most users to LMTCPB(*YES) and thus take command line away.

The main thing that caught my interest was yet to come, further down the line I was asked to investigate a spreadsheet package one of their staff had put together. The staff member in question had long since departed and they needed someone to understand and troubleshoot it. I was vaguely aware that it used ODBC to pull data from the Power8 and that there was a library built daily with a set of data extracts for it to use.

What I found was that, while the data extracts were still used, the staff member had also done a bit of poking around and identified the live versions of the files and was using those in conjunction with the extracts.

There was a short discussion that can be summarised as:

Managers: “Who said he could do that, who told him how to do that?”
Me: “Did anyone make sure he couldn’t?”

Although the system had gone to security level 30 (and thus *ALLOBJ had been removed from a lot of profiles) the general permissions on most libraries/files were still very public permissive, meaning that even if it was after the move that the staff member had decided to poke around they could’ve still accessed the live data files with relative ease and Google giving them hints on how to find it.

As it happens, I was discussing this with my boss on the way back from my first i-ug-uk event. He stole a minute of time from some dodgy bloke, Steve Bradshaw, who apparently knows a lot about these servers 😉 his suggestion was to investigate Exit Points.

I’m not going to cover these in detail, as there’s an excellent article on PowerWire from last year by Graham Street explaining them. In summary then these are IBM defined points that various types of access and tasks will pass through, by default there’s nothing on most of them but you can add programs to capture data and to validate access.

One solution here would be to buy a package to slap over the exit points, going back to the introduction to this article Helpsystems are a good example of this and poking around the subject led me into many discussions with their staff around security. To begin with however I have a specific challenge I want to look at, hence the duct tape and string coming out to cobble something together.

Fortunately there’s a lot of useful information on the IBM site around the parameters and which exit points you should be looking at, in my case, I went with QIBM_QZDA_SQL1 and QIBM_QZDA_SQL2 to get what I wanted. Even better I found someone else who had already written a program that would serve as a framework for what I wanted to do, always find code you can re-use rather than writing your own!

I’d love to give credit and link to the original program, but the website seems to be slightly broken at time of writing. In the hopes it one-day resurfaces, link here:

https://www.rpgiv.info/mambo/index.php?option=com_content&task=view&id=448&Itemid=9

The program would block any update/insert statements and record all statements, regardless of type, to a PF for later review. In my case I didn’t want to block insert/update at this stage, I needed to gather data on what was happening first, so I ripped out some logic and put together the below instead (please excuse the H/D/F specs, hadn’t started using a modern source editor at the time!)

https://github.com/DavidShears/IBMi-ODBC-Exit-Point-Monitor/

So in essence, ODBCAUDIT will give me the type of function, the actual request string, and who ran it when.

Thankfully I found that although the user was pulling from live files, there were no updates/inserts going back the other way (of course whether or not the member of staff might’ve done that before they left I don’t know, but whoever was still using the spreadsheet package wasn’t.) It also tagged a couple of other similar processes running that we didn’t have proper visibility of.

I suppose in summary, and the main thing I’d want people to take away from this, is that you shouldn’t make assumptions as to access. Yes in this case the user was authorised to use ODBC, and given some files to point to, but nobody had checked to tie down that access and make sure only these files were accessed. I don’t assume bad intent on the users part here, they probably thought it was more efficient/useful to get live data than a once a day dump, but it could’ve easily gone wrong.