List of Permissions

[cognostechie]

Is there a way to get a report that shows which user has access to which objects or capabilities?

I presume this information must be getting stored in a table in Content Store..I am checking the tables
to see which one can give this info but wondering if anybody already figured this out?

It looks like some tables have encrypted info

[cognostechie]

C'mon guys and gals, don't be shy..

[cognostechie]

Nobody ??

It turns out that Genware has a FM Model that shows this type of info. Anybody else?

[pmcgraw]

have a look at the audit extensions - i think this might have what you are looking for

[cognostechie]

Nope ! I already enabled audit to the very detailed level.

[josepherwin]

What Database is your content store running on?

I got this query running against SQL Server database. You may have to tweak it to your needs.

Once you understand how the below query is running, you will then be able to create an FM model from the Cognos Content store to produce report on User Groups and objects (I dont think this will go down to capabilities though).

This sql have been tested to run on Cognos 8.4

SELECT v_user.first_name, v_user.last_name, v_group.name, v_group_user.cmid as GROUP_ID,
   v_group_user.refcmid user_id FROM cmreford1 as v_group_user
LEFT OUTER JOIN (SELECT c33.cmid user_id, UPPER (c33.NAME) ldap_id,c1.surname last_name, c1.givenname first_name,c1.email email
      FROM cmobjprops1 c1 left outer join  cmobjprops33 c33             
      on c33.cmid = c1.cmid
      UNION
      SELECT cmobjprops1.cmid user_id, UPPER (cmobjprops33.NAME) ldap_id, cmobjprops1.surname last_name, cmobjprops1.givenname first_name, cmobjprops1.email
      FROM cmobjprops1, cmobjprops33
      WHERE cmobjprops1.cmid = cmobjprops33.cmid) v_user
ON v_group_user.refcmid = v_user.user_id
LEFT OUTER JOIN (SELECT cmobjnames.cmid cmid, UPPER (cmobjnames.NAME) name,
             cmobjects.created created, cmobjects.modified modified, cmobjects.disabled disabled
           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
WHERE v_user.first_name = INSERT FIRST NAME HERE
OR v_user.last_name = INSERT LAST NAME HERE
OR v_group.name = ENTER GROUP NAME HERE

[cognostechie]

Thanks ! I wish I could check it right now. Will check it on Monday when work begins again..

[cognostechie]

I added  v_user.ldap_id Name to the first line of SQL and it does show the users and the groups they belong to. I am checking if I can get it linked to the capabilities. Looks like it is assigning a diferent CMID for the capabilities in CMOBJNAMES table.

Thanks !
       

[josepherwin]

Let me know if you succeeded in adding capabilities to this SQL.

It would be a great help to everyone :)

[BI_at_ARINC]

Standard out of the box auditing is not the same as the Audit Extensions available from Cognos Best Practices found here (http://www.ibm.com/developerworks/data/library/cognos/development/utilities/page509.html).  This is an add-on application that you install on your BI server and execute.  It creates a set of tables, provides a model for those tables and allows you to report on the permissions that you're looking for, as well as other things.

The drawback is that it isn't dynamic.  You have to run the audit process and depending on the scope (account or content), it could take a while to complete before you can report on the data.

HTH and good luck.

[cognostechie]

Thanks ! Appreciate it.