Reporting on users and groups using ObjectServer SQL procedures
StephenCook 270000CHBT Visits (2024)
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
security.groups - contaisn the GroupName and GroupID
To solve the problem posed in the RFE, we need to output the contents of the secu
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.
FileFirst 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';
The next chunk of SQL is the actual SQL procedure to produce the report.
1 create or replace procedure groupreport( )
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
7 write into grplist values ('User: ', sqluser.UserName );
8 for each row usergroup in secu
10 for each row grps in security.groups where grps.GroupID = usergroup.GroupID
12 write into grplist values ('\tGroupName: ', grps.GroupName);
(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 secu
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.
The procedure can be executed from nco_sql by issuing the following command:
Some sample output for the procedure is below:
GroupName: MQ Messaging
GroupName: London Support Desk
GroupName: Sydney Support Desk
GroupName: x86 SERVER SUPPORT
GroupName: AIX SUPPORT
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);
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.