SQL to get the groups a user belongs to

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

Mathias,
This seems to work:

select ausers.m_actor_c_name,
agroups.m_actor_c_name
from obj_m_actor ausers,
obj_m_actor agroups,
obj_m_actorlink alink1,
obj_m_actorlink alink2
where ausers.m_actor_n_type = 16
/* and ausers.m_actor_n_status = 128 */
and agroups.m_actor_n_type = 1
and ausers.m_actor_n_id = alink1.m_actl_n_actorid
and agroups.m_actor_n_id = alink2.m_actl_n_actorid
and alink1.m_actl_n_fatlinkid = alink2.m_actl_n_id

Regards,
Kevin

Listserv Archives (BOB member since 2002-06-25)

2

Mathias wrote:
I use the follwing script to check the repository for users who disabled
themselves by entering 3 times a wrong password.

select m_actor_c_name
from obj_m_actor
where m_actor_n_status = 128

Does anybody know how I can get the groups the user belongs to into that
SQL?

Listserv Archives (BOB member since 2002-06-25)

3

Try changing your select statement to this (changes in UPPERCASE):

    select BOUSER.m_actor_c_name USERNAME, BOGROUP.M_ACTOR_C_NAME

GROUPNAME
from obj_m_actor BOUSER, OBJ_M_ACTOR BOGROUP,
OBJ_M_ACTORLINK GROUP_LINK,
OBJ_M_ACTORLINK USER_LINK
where BOUSER.m_actor_n_status = 128
AND BOUSER.M_ACTOR_N_ID = USER_LINK.M_ACTL_N_ACTORID
AND USER_LINK.M_ACTL_N_FATLINKID = GROUP_LINK.M_ACTL_N_ID
AND GROUP_LINK.M_ACTL_N_ACTORID = BOGROUP.M_ACTOR_N_ID
ORDER BY BOUSER.M_ACTOR_C_NAME, BOGROUP.M_ACTOR_C_NAME

Erich Hurst
Compaq Computer Corporation

“It is so easy to break eggs without making omlettes.” – C.S. Lewis

Listserv Archives (BOB member since 2002-06-25)

4

Glenn,

  1. Do you know the meaning of all the possible values of ‘BOUSER.m_actor_n_status’?
    The distinct codes I found in my table are: 1, 33, 65, 77, 97, 128, 160.

  2. Is there a place were, when creating BO reports, to record a description about each report in a given Document?

Many thanks
yannis

Glenn_Fredericks@AAL.ORG 03/11 9:49 AM >>>
Mathias wrote:
I use the follwing script to check the repository for users who disabled
themselves by entering 3 times a wrong password.

select m_actor_c_name
from obj_m_actor
where m_actor_n_status = 128

Does anybody know how I can get the groups the user belongs to into that
SQL?

Listserv Archives (BOB member since 2002-06-25)

5

These are the basic values:

1 = Enabled 2 = Locked 4 = No offline logon 8 = Cannot change
password 32 = Realtime user rights update 64 = Cannot delete document

The others are made of combinations of these - for example 33 is 32 and 1,
77 is 64, 8, 4 and 1.

Barbara Rosen

Database Development & Admin Services
Salomon Smith Barney
Phone: (212) 723-2756
e-mail: barbara.rosen@ssmb.com

Listserv Archives (BOB member since 2002-06-25)