Analyzing User Profiles – User Profiles
It’s always good to begin at the beginning, so let’s do that.
Let’s start with the basics: SQL that mimics DSPUSRPRF *ALL to an outfile. Launch Access Client Solutions (ACS) and then click on Run SQL Scripts. When the window opens, type this:
This Select statement provides information about all of the user profiles on the system. Scroll to the right to see which fields you can report on/analyze. Of course, you can download the whole thing into a spreadsheet (as I described in chapter 1) and analyze it from there, but I’m going to suggest some qualifications to the Select statement above to get to the information you’re looking for more quickly.
You can also use the QSYS2.USER_INFO_BASIC service to get user profile information if you have a large number of profiles on your system and you’re trying to optimize performance. It will return the same information as QSYS2.USER_INFO except that it will not include the USER_OWNER , USER_CREATOR, SIZE, CREATION_TIMESTAMP, LAST_USED_TIMESTAMP, DAYS_USED_COUNT, and LAST_RESET_TIMESTAMP fields.
I continue to see organizations analyze user profiles based on their user class. This method drives me crazy because the user class is not used when access to an object is checked. So thinking a user is in the “wrong” user class is really a waste of time unless you’re moving the system off of QSECURITY level 20. For that example, see chapter 3. Instead, here are examples of some of the analyses I’d perform.
The following example lists profiles having a specific special authority. In the example below, it’s *ALLOBJ. (Simply replace *ALLOBJ with another special authority, such as *SECADM, to get that list.) The profile will be listed regardless of whether the special authority is assigned to the profile itself or is inheriting the special authority via one of its groups. I recommend this list be reviewed at least once a quarter to ensure profiles still require each special authority assigned. Similar to reviewing the members of each group, reviewing the list of profiles with each special authority will catch users who have changed roles as well as catch special authorities that may have inadvertently been assigned to a group and are now available to all group members.
The code below started out as one of the examples provided with ACS (as described in chapter 1), but I’ve modified it to select different fields that better fit the profile attributes I find helpful for this review.
| Note The first field that I’ve selected in this SQL is user_name. That’s the system name of the field (sometimes known as the “short name” because it will be at most only 10 characters long). The SQL name for this field is authorization_name which, in my opinion, is not very descriptive. So I’ve mixed system name and SQL names for the names of the fields I’m selecting. I’m pointing this out because, prior to writing this book, I didn’t realize you could use both types in the same SQL statement! I encourage you to make your SQL as readable (and thus, self-documenting) as possible. |