sql - content store - users/groups/permissions

[mike.son]

hi all,

hopefully someone can help me

We use two namespace authentications for our cognos-platform (Cognos-Namespace + ActiveDirectory). We use a sql againts the content store to show all groups, users and permissions.

sql-example to select one usergroup:
select cn.NAME as GRUPPE, op.NAME as BENUTZER
from CONTENT_STORE.cmreford1 ro
inner join CONTENT_STORE.cmobjnames cn on ( cn.CMID = ro.CMID and cn.MAPDLOCALEID = 10 )
inner join CONTENT_STORE.cmobjprops33 op on ( op.CMID = RO.REFCMID )
where CN.NAME = '[group]'

But if the group contains an active directory group, we won't see the users of this group, only a timestamp. Is it possible to select these users?

Thanks,

mike.son

[Suraj]

The SQL below may not give everything you need but may help:

use CognosContent -- name of content store db
SELECT v_group.name as group_name,
v_user.ldap_id
FROM cmreford1 as v_group_user JOIN-- GET USERS
(SELECT cmid user_id,UPPER (NAME) ldap_id
FROM cmobjprops33)
v_user
ON v_group_user.refcmid = v_user.user_id JOIN--GET GROUPS

(SELECT cmobjnames.cmid cmid,UPPER (cmobjnames.NAME) name
FROM cmobjects,cmobjnames
WHERE cmobjects.classid IN (26, 54)
AND cmobjnames.mapdlocaleid = 24
AND cmobjects.cmid = cmobjnames.cmid)
v_group ON v_group.cmid = v_group_user.cmid
order by 1,2

--Source ittoolbox.

[mike.son]

hi Suraj Neupane,

thanks for your help. But i resolve the problem with a perl-script. ;D

Now i have the chance to show all groups (including Groups that contains an active directory group) and users in our Content Store. The script uses different CS_Tables amongst others:

CMREFORD1 - Relationship between parent and child
CMOBJPROPS1 - OBJID from AD-GROUP
CMOBJNAMES - Names of the Objects
CMOBJPROPS33 - Users

greets

mike.son

[olivier.mahe]

Hi Mike,

I try to do the same thing. And i can't.

What other tables are you using to do this request ?

Is it possible to do this without using perl script ?

Thank you for your response.

Olivier.MAHE