This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
If you're interested in what action a user has and how they get it, try this:
--------------------------------
--This query is primarily looking at the APP/OPTION, by user, group, and site / org
--this query is not handling the INDEPENDANT attribute of groups. It assumes everything is INDEPENDENT
--when we start dealing with NON independent groups ...
-- we must UNION in mg.independent=0,
-- giving bitwise APP/OPTION to any site/org combo that is mg.independent=0 ...
-- for that particular user
DECLARE @Org varchar(8)
DECLARE @Site varchar(8)
DECLARE @Group varchar(30)
DECLARE @User varchar( 30)
DECLARE @App varchar(10)
DECLARE @Option varchar(25)
DECLARE @Maintbl varchar(18)
SET @org = '%'
SET @Site = '%'
SET @Group = '%'
SET @USER = '%'
SET @App = '%'
SET @Option = '%'
SET @Maintbl = '%'
--restrict the output by any combination of the above 7 attributes (standard SQL wildcarding)
Select mu.userid, app as 'aa.app', optionname as 'aa.option', maintbname as 'aptbl.tbl.MainTable', gu.groupname as 'gu.groupname', resolvedsite, resolvedorgid
--,sa.siteid as 'sa.siteid', sa.orgid as 'sa.orgid' - comment out if you don't want to see support for resolved site and org
--,* --comment out to eliminate all supporting detail
from maxuser mu
left outer join groupuser gu on gu.userid = mu.userid
left outer join maxgroup mg on mg.groupname= gu.groupname
left outer join siteauth sa on sa.groupname = gu.groupname and sa.siteid like @Site and sa.orgid like @Org
left outer join (select siteid as resolvedsite, orgid as resolvedorgid from site) sit on (mg.authallsites=1 and sit.resolvedsite like @Site and sit.resolvedorgid like @Org) or (sit.resolvedsite=sa.siteid and sit.resolvedorgid=sa.orgid)
left outer join applicationauth aa on aa.groupname = gu.groupname
left outer join (select maintbname, app as maxappl from maxapps) aptbl on aptbl.maxappl=aa.app
where mg.independent=1
and mg.groupname like @Group
and mu.userid like @User
and aa.app like @App
and aa.optionname like @Option
and maintbname like @Maintbl
order by mu.userid, app, optionname, gu.groupname
----------------------
Regards,
Bob Chamberlain
Xanterra Parks & Resorts
Yellowstone National Park