Ask who can do what in a Maximo instance and you usually get an answer from memory. The Security Groups application shows one group at a time, which is fine for changing something and useless for seeing the shape of the whole thing - whether a group still has users, whether two groups grant the same access, whether anyone outside the admin group can reach Security Groups itself.
All of it is four tables away. These are the queries we run at the start of a Maximo security review.
The tables that hold the answer
MAXGROUP- the security groups themselves.GROUPUSER- which user is in which group.APPLICATIONAUTH- which group has which option on which application. This is the one that actually grants access.SIGOPTION- the options an application defines, so you can tell a read from a delete.
Everything below is read-only. Run it against a copy if your policy requires it, but none of these statements write.
Which groups exist, and does anyone use them
SELECT g.groupname,
g.description,
COUNT(gu.userid) AS users
FROM maxgroup g
LEFT JOIN groupuser gu ON gu.groupname = g.groupname
GROUP BY g.groupname, g.description
ORDER BY users DESC, g.groupname;The rows with zero users are the interesting ones. Groups outlive the projects that created them, and an empty group with broad authorisations is a loaded gun waiting for someone to be added to it "temporarily".
What a group can actually reach
SELECT aa.app,
COUNT(*) AS options_granted
FROM applicationauth aa
WHERE aa.groupname = 'YOUR_GROUP'
GROUP BY aa.app
ORDER BY options_granted DESC;Option count is a rough proxy for depth of access, not a precise one. A group with one option on an application might have READ; it might have DELETE. To separate them, join SIGOPTION:
SELECT aa.app,
aa.optionname,
so.description
FROM applicationauth aa
LEFT JOIN sigoption so
ON so.app = aa.app
AND so.optionname = aa.optionname
WHERE aa.groupname = 'YOUR_GROUP'
ORDER BY aa.app, aa.optionname;The grid: groups against applications
The view worth having is every application down one axis and every group across the other. In SQL that is a pivot, and how you write it depends on your database - but the data behind it is one query:
SELECT aa.app,
aa.groupname,
COUNT(*) AS options,
MAX(CASE WHEN aa.optionname = 'READ' THEN 1 ELSE 0 END) AS has_read,
MAX(CASE WHEN aa.optionname = 'INSERT' THEN 1 ELSE 0 END) AS has_insert,
MAX(CASE WHEN aa.optionname = 'SAVE' THEN 1 ELSE 0 END) AS has_save,
MAX(CASE WHEN aa.optionname = 'DELETE' THEN 1 ELSE 0 END) AS has_delete
FROM applicationauth aa
GROUP BY aa.app, aa.groupname
ORDER BY aa.app, aa.groupname;Export that to a spreadsheet and pivot it there. It is quicker than fighting your database's pivot syntax, and the result is something you can hand to an auditor.
Two warnings from doing this on real instances. The grid gets unreadable past about twenty groups, so filter to the ones you are actually reviewing. And a blank cell means no authorisation through this group - a user in three groups gets the union, which is the next query.
What one person can actually do
The question a security review is really asking. Access is the union of every group the user belongs to:
SELECT DISTINCT aa.app,
aa.optionname
FROM groupuser gu
JOIN applicationauth aa ON aa.groupname = gu.groupname
WHERE gu.userid = 'YOUR_USER'
ORDER BY aa.app, aa.optionname;Run it for whoever surprises you. The answer is regularly wider than the person's job description, because group membership accumulates and nobody removes it.
Who can change security itself
The query we run first, and the one that most often produces a raised eyebrow:
SELECT gu.userid,
aa.groupname,
aa.optionname
FROM applicationauth aa
JOIN groupuser gu ON gu.groupname = aa.groupname
WHERE aa.app = 'SECURGROUP'
ORDER BY gu.userid;Anyone in that list can grant themselves anything else in the system. If it contains names you would not have guessed, that is the finding - the rest of the review is detail.
If you would rather not write SQL
Bruno Portaluri publishes a pair of BIRT reports that cover the first two views here - a security overview counting users per group, and an application security overview that renders the group-against-application grid with colour coding for read and write access. They install through Administration > Report Administration like any other report design, and they are a reasonable shortcut if you want the picture without the queries.
They are his work, not ours: Maximo Security Overview report.
We do Maximo security reviews as fixed-price work - the queries above, plus what they turn up. If you have an instance where nobody can say with confidence who can do what, tell us how many groups you are dealing with.