Technical Blog Post
Abstract
Reporting on users and groups using ObjectServer SQL procedures
Body
We've had a couple of RFE requests recently that can be met by creating an SQL procedure to implement the required functionality. The purpose of this blog post is to walk through one of those examples and show how the procedure can be used and how it works.
The RFE in question is http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=19979 the request was to provide the ability to determine via nco_sql which group and user ID belongs to. This can't be done with a single query because the information needed is split across three different tables:
security.users - contains the UserName and UserID
security.groups - contaisn the GroupName and GroupID
security.group_members contains the mapping of UserID's to GroupIDs.
security.groups - contaisn the GroupName and GroupID
security.group_members contains the mapping of UserID's to GroupIDs.
To solve the problem posed in the RFE, we need to output the contents of the security.group_members table in a format that is more human friendly than just a list of integer user and group ids. The solution is to use an SQL procedure that can issue queries across the multiple tables and then write the output to a text file.
File
First we need to create a text file to hold the results. The command below will create a file in our $OMNIHOME/log directory named after the ObjectServer that created it. So for an ObjectServer instance called PCOMS_A, the name of the file will be PCOMS_A_groups.txt.create or replace file grplist getenv('OMNIHOME') + '/log/' + getservername() + '_groups.txt';
By having the command create the file name based on the value of OMNIHOME and the name of the ObjectServer at runtime, the command can be easily put into a file of SQL commands and applied to different ObjectServer instances without having to modify the command to cope with different paths for the OMNIbus installation etc.
Procedure
The next chunk of SQL is the actual SQL procedure to produce the report.
1 create or replace procedure groupreport( )
2 begin
3 -- Procedure reports all users within the ObjectServer and the groups they are members of to an ObjectServer
4 -- text file
5 for each row sqluser in security.users
6 begin
7 write into grplist values ('User: ', sqluser.UserName );
8 for each row usergroup in security.group_members where usergroup.UserID = sqluser.UserID
9 begin
10 for each row grps in security.groups where grps.GroupID = usergroup.GroupID
11 begin
12 write into grplist values ('\tGroupName: ', grps.GroupName);
13 end;
14 end;
15 end;
16 end;
2 begin
3 -- Procedure reports all users within the ObjectServer and the groups they are members of to an ObjectServer
4 -- text file
5 for each row sqluser in security.users
6 begin
7 write into grplist values ('User: ', sqluser.UserName );
8 for each row usergroup in security.group_members where usergroup.UserID = sqluser.UserID
9 begin
10 for each row grps in security.groups where grps.GroupID = usergroup.GroupID
11 begin
12 write into grplist values ('\tGroupName: ', grps.GroupName);
13 end;
14 end;
15 end;
16 end;
(note line numbers will need to be removed prior to submitting the SQL to the ObjectServer)
Lines 1 - 4: Aren't particularly interesting, they are simply the definition of the procedure and a comment giving some information about the procedure.
Line 5: We want to report on all users, so we start loop over the security.users table, using sqluser as the variable referencing the current row we are working with
Line 7: The WRITE INTO command is going to output the value of sqluser.UserName, to the file we created. By doing the output of the user here, we only log the user name once, regardless of the number of groups.
Line 8: We now have an inner loop, where we select those rows in security.group_members that have the same UserID as the user we've just written out to file. As security.group_members holds the mapping of user ids to group id, this gives us the set of groups for this user.
Line 7: The WRITE INTO command is going to output the value of sqluser.UserName, to the file we created. By doing the output of the user here, we only log the user name once, regardless of the number of groups.
Line 8: We now have an inner loop, where we select those rows in security.group_members that have the same UserID as the user we've just written out to file. As security.group_members holds the mapping of user ids to group id, this gives us the set of groups for this user.
Line 10: We now select all the groups that have the same group id as matched in Line 8, from the security.groups table.
Line 12: Output the groups names to file we've written the UserName to. Note we format the group name by tabbing in one tab (\t).
Lines 13 - 16: Simply closing off the FOR EACH ROW loops and the procedure.
Execution
Line 12: Output the groups names to file we've written the UserName to. Note we format the group name by tabbing in one tab (\t).
Lines 13 - 16: Simply closing off the FOR EACH ROW loops and the procedure.
Execution
The procedure can be executed from nco_sql by issuing the following command:
execute groupreport;
Some sample output for the procedure is below:
User: ruben.jones@myco.com
GroupName: MQ Messaging
GroupName: London Support Desk
GroupName: Public
GroupName: Normal
User: john.smith@myco.com
GroupName: Sydney Support Desk
GroupName: x86 SERVER SUPPORT
GroupName: Public
GroupName: Normal
User: anne.jones@myco.com
GroupName: AIX SUPPORT
GroupName: Public
GroupName: Normal
User: ruben.jones@myco.com
GroupName: MQ Messaging
GroupName: London Support Desk
GroupName: Public
GroupName: Normal
User: john.smith@myco.com
GroupName: Sydney Support Desk
GroupName: x86 SERVER SUPPORT
GroupName: Public
GroupName: Normal
User: anne.jones@myco.com
GroupName: AIX SUPPORT
GroupName: Public
GroupName: Normal
Customization
Obviously, the output can be modified to best suit local needs, for example by changing line 12 to be as follows:
write into grplist values ('UserName', sqluser.UserName, ' GroupName: ', grps.GroupName);
write into grplist values ('UserName', sqluser.UserName, ' GroupName: ', grps.GroupName);
In which case the user name and group name will appear on the same line, which may make post processing easier. Adding the user id or group id, is also easily achieved.
[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"","label":""},"Component":"","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}}]
UID
ibm11082067