BusinessObjects Board

user, group and universe report

security Supervisor (4.x - 6.x)
#1

I’m looking for the ability to create a report in SQL that shows which group and which universe a user belongs to. Would anyone know an example of how to do this? Thanks.

Mikey (BOB member since 2004-06-18)

#2

This is the code i use to get the which user has access to which universe. You canmodify to include the user group.

SELECT
  OBJ_M_ACTOR.M_ACTOR_N_ID,
  OBJ_M_ACTOR.M_ACTOR_C_NAME,
  OBJ_M_UNIVERSES.M_UNI_N_ID,
  OBJ_M_REPOSITORY.M_REPO_C_NAME
FROM
  OBJ_M_UNIVERSES,
  OBJ_M_REPOSITORY ,
  OBJ_M_ACTOR,
  OBJ_M_ACTORLINK,
  OBJ_M_ACTORLINK  ACTOR_GROUP_USERS,
  OBJ_M_RESLINK
WHERE
  ( ACTOR_GROUP_USERS.M_ACTL_N_ACTORID= OBJ_M_ACTOR.M_ACTOR_N_ID  )
  AND  ( OBJ_M_ACTORLINK.M_ACTL_N_ID=ACTOR_GROUP_USERS.M_ACTL_N_ID(+)  )
  AND  ( OBJ_M_UNIVERSES.M_UNI_N_ID=OBJ_M_RESLINK.M_RES_N_RESID AND OBJ_M_RESLINK.M_RES_N_RESTYPE = 2  )
  AND  ( OBJ_M_RESLINK.M_RES_N_ACTLINKID=OBJ_M_ACTORLINK.M_ACTL_N_ID  )
  AND  ( OBJ_M_UNIVERSES.M_UNI_N_REPOID=OBJ_M_REPOSITORY.M_REPO_N_ID  )

Raja Saripalli :us: (BOB member since 2005-12-14)

#3

Thanks you so much for the example since I’m new to Supervisor and don’t know these table layouts at all.

I’m having problems trying to link the user to its coresponding group. It looks like the OBJ_M_ACTOR.M_ACTOR_C_NAME holds both the name of the user and the group. If this is the case, how do I link user to group so that I get a list of users and the groups that they belong to?

Mikey (BOB member since 2004-06-18)

#4

Hi

More information available here

Regards

#5

Thank you for this information on the tables. It is helpful for understanding the data, but I’m still having a hard time figuring out how to link the user to it’s coresponding group when the user and the group are both in the same column.

For instance, I would need the following output:

M_ACTOR_C_NAME-----M_ACTOR_C_NAME
USER 1 -------------------- GROUP A
USER 1 -------------------- GROUP B
USER 1 -------------------- GROUP C
USER 2 -------------------- GROUP A
USER 2 -------------------- GROUP B
USER 3 -------------------- GROUP A

Mikey (BOB member since 2004-06-18)

#6

You can give this universe a go (one I knocked up for Users/Groups/BCA Jobs). Works for 6.5.1 repository, but could be adapted for others (lesser).

Beck

beckster :uk: (BOB member since 2003-02-18)