Access Query

[DDP021]

We have numerous "external" ids on our system (in the range of 1000?...They all begin with the same 3 characters...They are asking us to provide them a listing of which if these ids have access to two particular Application groups and then provide them a listing..We ran a summarize, via the ADMIN Client, checking the USER INFORMATION and APPLICATION GROUP PERMISSIONS boxes..This basically gives us the information we need (plus allot of information from the user setup we DON'T need)...the issue we are running into is, with the amount of user ids, how do we then "Pick out" just the information we need, ie user id, user name that have access to the two application groups they are asking about?...We tried providing them the entire summary of all the ids but with such a huge list of all the ids, they sent back requesting JUST the ID's in question..Anyone know of a way to just pull the information we need from the list?...The summary is generated in a .txt format..Tried saving it as a .xls, .doc to see if there was a way to sort it but couldn't find a way...

[jw]

Hi DDP021,

I assume you use DB2 with CMOD.  Have you tried the following:

select a.id
, substr(c.userid, 1, 15) as s_user
, a.agid
, b.name as appl_group_name
, decode(mod(a.id_perms,2),1,'Yes',0,'No') as access_priv
, a.id_perms
, a.doc_perms
, a.ann_perms
from ondemand.arsagperms a inner join ondemand.arsag b on a.agid = b.agid
left outer join ondemand.arsuser c on a.id = c.uid
where b.name = 'System Log'
and c.userid is not null
union
select a.id
, substr(c.name, 1, 15) as s_group
, a.agid
, b.name as appl_group_name
, decode(mod(a.id_perms,2),1,'Yes',0,'No') as access_priv
, a.id_perms
, a.doc_perms
, a.ann_perms
from ondemand.arsagperms a inner join ondemand.arsag b on a.agid = b.agid
left outer join ondemand.arsgroup c on a.id = c.gid
where b.name = 'System Log'
and c.name is not null
union
select a.id
, 'PUBLIC'
, a.agid
, b.name as appl_group_name
, decode(mod(a.id_perms,2),1,'Yes',0,'No') as access_priv
, a.id_perms
, a.doc_perms
, a.ann_perms
from ondemand.arsagperms a inner join ondemand.arsag b on a.agid = b.agid
left outer join ondemand.arsgroup c on a.id = c.gid
where b.name = 'System Log'
and a.id = 0
order by 2

Hope this helps.

[DDP021]

Thanks JW....Yes,  we use DB2...My group are just the ADMINS (ie defining new Folders, access, etc...I will pass your information onto our DB2 person to see if he can run with it...Appreciate all the help!!!!  Take care