Two Tips Are Better Than One

November 10, 2010 Patrick Botz

In my two previous tips, I discussed the alternative PUBLIC authority technique and the re-adopted authority utility implemented via a Submit Job (SBMJOB) command exit point. Using these two ideas together provides an environment where everyone can continue to work normally while, at the same time, providing an environment where you can identify special cases requiring additional changes and can test all of your changes without affecting normal production usage.

Let’s see how this works. If you recall, alternative PUBLIC authority involves setting up a group profile; we’ll call it ALTPUBLIC. After adding all of the production users (or all users) to this profile, you can change PUBLIC authority to *EXCLUDE for your data and programs. Users who are members of ALTPUBLIC will continue to work without problems–virtually regardless of any other authority changes you make.

Where Do We Go From Here?

Well, that’s all fine and dandy, but what other authority changes should you make? I usually make the following three changes.

First, create a new application owner user profile, if one doesn’t already exist. We’ll call this profile APPOWNER. Make this new user profile the owner of all of the application programs and data objects.

Second, change the initial program for the application to adopt authority. If you don’t own the initial program and it doesn’t have observability, write a “wrapper” CL program to call the real initial program. Make sure the wrapper program is owned by APPOWNER and that it adopts authority.

Third, implement the Re-Adopt utility. Make sure to add the initial program or your wrapper program to the Re-Adopt configuration table.

Select a Guinea Pig

Assuming that most of the application users do not have *ALLOBJ special authority, these are most likely the only major changes you will have to make. But you don’t have to take my word for it; just select one application user and remove that person’s user profile from membership in ALTPUBLIC. If you created a new wrapper program, you also need to change the initial program in this person’s user profile to the new wrapper program.

Working with the person you selected, tell them you are testing some authority changes and if everything goes as planned they should see no difference or encounter any problems with the application. Tell them if they do have any problems or questions at all to contact you immediately and you will fix the problem right away.

If your test user runs into an issue, gather the information about which menu option or operation they were running. To debug any authority related problem, the information you ultimately need is:

The user profile used by the system at the time the problem was encountered
The program or command that was running when the problem occurred
The object the program or command was trying to access
The authority or special authority required to perform the failing operation

The system audit journal is, by far, the best and fastest place to find most of this information. Appendix D of the Security Reference manual can also be very useful if you find a system command received the authority failure. In any case, as soon as you have this information, make the test person’s user profile a member of ALTPUBLIC again. Have them log out of the application and log back in and they will have lost very little of their productive time.

Problem Types

So what kinds of problems will be encountered? The most likely problems will fall into one of three categories:

Trigger programs
Programs with embedded dynamic SQL statements
Direct access to data from outside of the application

Adopted authority is not available to trigger programs, as they are launched by the database and not directly from by any user program. However, you can compile (or change) programs to run with adopted authority. If your developers can tell you the names of all trigger programs, fix them all at once. If not, it just means your testing process with real users will take longer.

Embedded dynamic SQL statements (i.e., the program has a PREPARE or EXECUTE statement) do not automatically run with adopted authority in effect at the time the program is executed. This is true even if the User Profile attribute of the program is set to *OWNER and/or the “Use adopted authority” attribute of the program is set to *YES. The only official way to get the dynamic embedded SQL in a program to run with adopted authority is to recompile the program (e.g., CRTSQLRPG) with “Dynamic user profile” set to *OWNER. (The default is *USER.)

There’s Always Someone

Most customers I work with have someone–typically named Joe and working in marketing, go figure–who needs to access database information directly, often through Excel. This means they do not use the application, much less the initial program that adopts authority. There are a number of ways to fix this, but the one I like best and that requires the least maintenance over time is to use an ODBC exit program. You can write your own exit point program or you can buy a third-party exit point security product. Writing an ODBC exit point program is very doable for an experienced programmer. The advantage of writing your own is cost. The disadvantage is that you may not have anyone that understands the ODBC exit point, and it does require a bit of investigation.

There is at least one exit point vendor whose product, in addition to allowing you to control who can access the system through ODBC, also allows you to specify the adopted authority that should be used for requests by people permitted to use ODBC. The only disadvantage of buying vendor software is that you may very likely only need to use the software for a very small number of exit points–but you’ll pay for the entire product anyway.

When the first user is able to do their job for a period of time with which you are comfortable (e.g., a day, week, business cycle) without errors, then pick another test user, preferably in another job function to make sure you are exercising additional parts of the application. You’ll find that you encounter most problems with the first one or test users. After a couple of more test users, you’ll find yourself being ready to remove larger groups of people from ALTPUBLIC group membership (e.g., everyone in a department that has similar job functions). At some point, you’ll be comfortable enough to delete the ALTPUBLIC user profile. When it’s deleted the whole system is running with PUBLIC *EXCLUDE.

A Gradual Approach

So there you have it. Combining alternative PUBLIC authority with the re-adopt utility puts you well on the way to moving your system to an exclusionary access control model (i.e., PUBLIC = *EXCLUDE on all application programs and data objects). This approach gives you the ability to test authority changes with a single user while the rest of your users are blissfully unaware that any changes are being made. It also gives you the ability to identify and fix problems over a long period of time.

Patrick Botz is the principal consultant and founder of Botz & Associates Inc. He is also president of Valid Technologies, LLC, a biometric middleware ISV. Pat spent nearly 20 years working at IBM in various security roles including lead IBM i security architect, IBM eServer security team, and the head of IBM Lab Services Security Consulting practice. Check out his Website at www.botzandassociates.com. Send your questions or comments for Patrick to Ted Holt via the IT Jungle Contact page.