Report on Users / Roles / Groups and Permissions

[ej]

Which version of Cognos 8 were you using?  I am on 8.3 and on Oracle 10g.  The first two scripts worked like a charm when run as the COGNOS_ADMIN user.  The third, however did not, as there does not seem to be a DBA_GROUP_INFO table or view under the COGNOS_ADMIN user.  I am unclear if this is because 8.3 does not have this table/view, or if something was not installed properly. 

Note to others who are on Oracle and are using these scripts, the groups script shows Cognos groups.  If you are using LDAP security instead of COGNOS security the information doesn't show in this script.

If you are using Oracle as content store DB

here are some homemade select that could give you some information

list of users

SELECT cmobjprops1.cmid, cmobjprops1.busphone, cmobjprops1.email,
          cmobjprops1.faxphone, cmobjprops1.givenname, cmobjprops1.homephone,
          cmobjprops1.mobilephone, cmobjprops1.pagerphone,
          cmobjprops1.paddress, cmobjprops1.surname, cmobjprops1.TIMEZONE,
          cmobjprops1.clocaleid, cmobjprops1.prodlocale, cmobjprops1.objid,
          cmobjprops1.useaccessibility, cmobjprops33.cmid AS cmid1,
          UPPER (cmobjprops33.NAME)
     FROM cmobjprops1, cmobjprops33
    WHERE cmobjprops1.cmid = cmobjprops33.cmid

List of Groups (List of English groups)

SELECT cmobjnames_base.cmid, cmobjects.pcmid, UPPER (cmobjnames_base.NAME),
          cmobjects.created, cmobjects.modified, cmobjects.disabled,
          cmobjnames_base.isdefault
     FROM cmobjects, cmobjnames_base
    WHERE cmobjects.classid IN (26, 54)
      AND cmobjnames_base.mapdlocaleid = 24
      AND cmobjects.cmid = cmobjnames_base.cmid

List of users by group

SELECT   v_group_user.user_id, v_user.ldap_id,
            LTRIM (RTRIM (UPPER (v_user.ldap_id))), v_group.cmid,
            v_group.name_en name_en, v_group.name_fr name_fr, v_group.created,
            v_group.modified, v_group.disabled, v_user.last_name,
            v_user.first_name, v_user.email
       FROM dba_group_info v_group,
            (SELECT cmid GROUP_ID, refcmid user_id
               FROM ops$cgs.cmreford1) v_group_user,
            (SELECT c33.cmid user_id, UPPER (c33.NAME) ldap_id,
                    c1.surname last_name, c1.givenname first_name,
                    c1.email email
               FROM cmobjprops1 c1, cmobjprops33 c33
              WHERE c33.cmid = c1.cmid(+)
             UNION
             SELECT cmid user_id, UPPER (name_en) ldap_id, name_en last_name,
                    '' first_name, '' email
               FROM dba_group_info) v_user
      WHERE v_group.cmid = v_group_user.GROUP_ID
            AND v_group_user.user_id = v_user.user_id(+)
   ORDER BY 1, 3

I hope this will help you

[Schmidty]

Guys

I ahve run this script and the third doesnt work. The dba_group_info doesnt exist. We are using 8.3

Any ideas ?


SELECT   v_group_user.user_id, v_user.ldap_id,
            LTRIM (RTRIM (UPPER (v_user.ldap_id))), v_group.cmid,
            v_group.name_en name_en, v_group.name_fr name_fr, v_group.created,
            v_group.modified, v_group.disabled, v_user.last_name,
            v_user.first_name, v_user.email
       FROM dba_group_info v_group

 

Doesn’t work. Dba_group_info doesn’t exist

 


            SELECT cmid GROUP_ID, refcmid user_id
               FROM ops$cgs.cmreford1 v_group_user

 

Doesn’t work. ops$cgs.cmreford1 doesn’t exist

 

            SELECT c33.cmid user_id, UPPER (c33.NAME) ldap_id,
                    c1.surname last_name, c1.givenname first_name,
                    c1.email email
               FROM cmobjprops1 c1, cmobjprops33 c33
              WHERE c33.cmid = c1.cmid(+)
           

Works

 

             SELECT cmid user_id, UPPER (name_en) ldap_id, name_en last_name,
                    '' first_name, '' email
               FROM dba_group_info) v_user
      WHERE v_group.cmid = v_group_user.GROUP_ID
            AND v_group_user.user_id = v_user.user_id(+)

 

Doesn’t work. Dba_group_info doesn’t exist

[tonico]

Hello,
Try this code for cognos 8.4. for Oracle Repository 10g. Disclaimer: I am not the creator of this code, it was found somewhere on the net, sorry I dont know the author....but I am grateful to him/her.

   SELECT
          v_user.first_name,
          v_user.last_name,
          v_user.objid AS user_id,
          SUBSTR (v_user.objid,
                  INSTR (v_user.objid, ':', -1) + 1,
                  LENGTH (v_user.objid)
                 ) AS user_guid,
          v_group.NAME AS group_name,
          v_group_user.cmid AS GROUP_ID,
          v_group_user.refcmid user_id
   FROM   cmreford1 v_group_user
          INNER JOIN
          (SELECT c33.cmid user_id, UPPER (c33.NAME) ldap_id,
                  c1.surname last_name, c1.givenname first_name,
                  c1.email email, c1.objid
             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,
                  cmobjprops1.objid
             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

[andy_mason_84]

Hi,

Does anyone know if you can get what capabilities a user has using this script?

I have a script that pulls the user and groups associated to each user ... but can you get capability info out too?

Cheers,

AM

[Grayson_Basil]

I just inherited Cognos security and there are about 50 customer groups and I have been tasked to provide a list of which user belongs to which group

Will this work and where does one enter the above sql?

Cognos 10.2.1
Oracle DB

Thanks