Group Queries in DQL

Group Queries

DQL To retrieve group to which user belongs

Select group_name from dm_group where any users_names in (select user_name from dm_user where user_os_name = 'karthik')

Select group_name from dm_group where any users_names in ('karthik')

DQL To get all the groups and count of all the active members in those groups

Select gr2.i_supergroups_names, count (gr1.users_names) from dm_group_r gr1, dm_group_r gr2 where gr1.r_object_id = gr2.r_object_id and gr2.i_supergroups_names is not null group by gr2.i_supergroups_names

DQL To get the name of all groups and name of all active users in those groups

select gr2.i_supergroups_names, gr1.users_names
from dm_group_r gr1, dm_group_r gr2
where gr1.r_object_id = gr2.r_object_id and
gr2.i_supergroups_names is not null and gr1.users_names in(select user_name from dm_user where user_state=0)
order by gr2.i_supergroups_names

DQL To get list of users and groups they belong to

SELECT DISTINCT u.user_name,u.user_os_name, g.group_name FROM dm_user u, dm_group g WHERE ANY g.i_all_users_names = u.user_name and r_is_group = 0
order by u.user_name

DQL To get the group names for particular user

SELECT DISTINCT u.user_name,u.user_os_name, g.group_name FROM dm_user u, dm_group g WHERE ANY g.i_all_users_names = u.user_name and r_is_group = 0 and u.user_name ='karthik' order by u.user_name

DQL To get all active users in a particular group

SELECT DISTINCT u.user_name,u.user_os_name,u.user_state, g.group_name FROM dm_user u, dm_group g WHERE ANY g.users_names = u.user_name and r_is_group = 0 and u.user_state=0 and g.group_name='admingroup' order by u.user_name

DQL To add a user to a group

ALTER GROUP group_name ADD members

ALTER GROUP group_name ADD (select user_name from dm_user where user_name = 'karthik')

Ex 1:

ALTER GROUP enterprise ADD (select user_name from dm_user where user_name = 'karthik')

Ex 2:

To add 2 users namely Romeo and Juliet to a group

ALTER GROUP engineering ADD Romeo,Juliet

Ex 3:

To set/define an email address for a particular group

ALTER GROUP enterprise SET ADDRESS 'srksrv786@gmail.com'

DQL To remove user from group

ALTER GROUP group_name DROP members

ALTER GROUP group_name DROP (select user_name from dm_user where user_os_name = 'karthik')

Ex:

ALTER GROUP enterprise DROP (select user_name from dm_user where user_os_name = 'karthik')

Note: Before removing any user from any group make sure that which group the user belongs to by using the following select query

Select group_name from dm_group where any users_names in ('karthik')

DQL To assign a user to many groups without changing users default group

UPDATE dm_group OBJECTS APPEND users_names = 'SR, Karthik' WHERE group_name IN ('fe_general', 'enterprise');

DQL To set a default group for a user

UPDATE dm_user objects set user_group_name ='engineering' where user_name='SR, Karthik'

Note: If you modifying the groups repeatedly using this query, the most recently updated group using this query will be the default group for the user and the previously added groups also will be there in the groups that user belongs to.

For ex,

UPDATE dm_user objects set user_group_name ='enterprise' where user_name='SR, Karthik'

After the execution of this query, default group for Karthik will be enterprise and we check for the group that the user belongs, to all these groups for ex enterprise and also docu groups will be there

DQL To retrieve groups access info on the folders

SELECT r_accessor_name, r_accessor_permit FROM dm_acl
WHERE object_name IN (SELECT acl_name FROM dm_folder
WHERE ANY r_folder_path = '/SASBU/NON BLOCK SPECIFIC')

DQL To remove a group from the repository

DROP GROUP group_name

Ex: Drop Group finance_orders

DQL To get subgroups

Select group_name from dm_group where any i_supergroups_names='enterprise'

Note: Using this query we can find the list of subgroups that the group enterprise (super group) has.

DQL To deactivate user

UPDATE dm_user OBJECTS SET user_state = 1 WHERE user_name = 'username'.

Ex:

UPDATE dm_user OBJECTS SET user_state = 1 WHERE user_name = 'SR, Karthik'

DQL To grant extended priviledges

Grant Config Audit to User User_name

DQL to provide/assign/give groups access on the folders

SELECT r_accessor_name, r_accessor_permit FROM dm_acl
WHERE object_name IN (SELECT acl_name FROM dm_folder
WHERE ANY r_folder_path = '/FolderPath….')

Ex:

SELECT r_accessor_name, r_accessor_permit FROM dm_acl
WHERE object_name IN (SELECT acl_name FROM dm_folder
WHERE ANY r_folder_path = '/Equity/Europe')

DQL To get subgroups

select group_name from dm_group where any i_supergroups_names = 'YourGroupName'

Ex:

select group_name from dm_group where any i_supergroups_names = 'Materials_department'

Please provide your valuable suggestions if any.

Thanks & Regards,

S.R.Karthik