RegContent

DQL: current user groups and group members

Two everyday questions in Documentum security work: which groups is this session in, and who sits inside a given group. Both answers live on dm_group. The trap is treating direct membership and nested membership as the same attribute.

Who is the session user?

DQL exposes the logged-in account as the keyword USER. It is not a string you quote.

select user_name, user_os_name, user_address, user_state
from dm_user
where user_name = USER

If you need a named account instead of the session, replace USER with a literal such as 'alice'.

Groups for the current user

Direct membership only — the user appears on the group’s users_names repeating attribute:

select group_name, description
from dm_group
where any users_names = USER

Effective membership, including users reached through nested groups — use i_all_users_names:

select group_name, description
from dm_group
where any i_all_users_names = USER

Same pattern for a fixed login:

select group_name
from dm_group
where any i_all_users_names = 'alice'

If ACL troubleshooting and group troubleshooting disagree, check which attribute you queried. A user who is only in a child group will miss users_names on the parent and still appear on i_all_users_names.

Users inside a group

Direct user members:

select users_names
from dm_group
where group_name = 'regulatory_authors'

Every user the group resolves to after nesting:

select i_all_users_names
from dm_group
where group_name = 'regulatory_authors'

Those queries return the repeating attribute values as rows (object-based vs row-based presentation depends on the client). To join out to dm_user for email or state:

select u.user_name, u.user_address, u.user_state
from dm_user u, dm_group g
where g.group_name = 'regulatory_authors'
  and any g.i_all_users_names = u.user_name

Nested groups

Groups can contain other groups via groups_names:

select groups_names
from dm_group
where group_name = 'regulatory_authors'

Find every group that directly contains a child group:

select group_name
from dm_group
where any groups_names = 'regulatory_reviewers'

i_all_users_names is the Content Server’s expanded user list for that group object. It is what most security checks effectively care about when resolving “is this user in this group?”

Useful filters

select group_name, group_class, is_private
from dm_group
where group_class = 'role'
enable(return_top 200)

Skip inactive users when you expand membership:

select u.user_name, u.user_address
from dm_user u, dm_group g
where g.group_name = 'regulatory_authors'
  and any g.i_all_users_names = u.user_name
  and u.user_state = 0

user_state = 0 is active in the usual mapping; confirm against your docbase if you use custom state handling.

Habits that save time

Related