Handy DQL: List all groups for a user

The following DQL lists all groups that a user belongs to directly or indirectly:

select * from dm_group
where any i_all_users_names = 'UserName'

Replace UserName with the actual user name under consideration.


7 thoughts on “Handy DQL: List all groups for a user

  1. Hi,

    I want to run the dql that will give me the list of users in
    particular group and all the group each user belongs to.

    Any help is much appriciated.

  2. Here are the queries.

    1. List of users in particular group (‘admingroup’ in this example)

    select i_all_users_names
    from dm_group
    where group_name = ‘admingroup’

    2. All the groups each user belongs to

    select u.user_name, g.group_name
    from dm_user u, dm_group g
    where u.user_name in
    select i_all_users_names from dm_group
    where group_name = g.group_name
    order by u.user_name

  3. Hi,

    Thank you very much for the tips.
    Can you help me in one issue?
    I want to display all available cabinets/folders/documents for a particular login user.
    How can I write a DQL query for achieving this task?
    What are the tables I have to look for this task?

  4. This could get messy depending on what you mean by available and the size of the repository. Part of the issue is resolving security. One simple way to do it would be to log in as the specified user and run a query for all folders or documents. Content Server will resolve the security and will return objects with at least Browse permission for the logged-in user.

  5. Hi doquent,

    Thank you for your reply.
    I am trying to display a folder structure similar to documentum structure.
    I am trying to get the folder structure through DFS(documentum Foundation Services).
    I am using query services which will directly query the database.
    So content server is not applying any security over this query.
    So please guide me how to get the folder structure for a given user.

  6. Are you sure you are directly going to the database? DQL talks to the content server and not to the database directly. If you bypass Content Server you will need to implement security yourself – which will not be a trivial task.

    If you have the user’s credentials then you can query the all the cabinets. The result will only include the cabinets that the user has access to. You can repeat similar interaction when you wish to expand the folders.

  7. Hello, I know this is an old thread, I just wanted to put this out there.

    You can also use the EXECUTE CHECK_SECURITY DQL command. It will look like this:
    EXECUTE check_security WITH user_name=’Some Name’,level=3,object_list=’0900000aaaaaaaaa’

    I just found that in the Content Server guide DQL reference. Hope that is helpful. Good luck!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s