Dear Experts,
Please find below a query that I used to generate a report on finding the user access and permissions to a folder structure.
DQL Query :
select uf.r_folder_path as folder_path, f.object_name as folder_name, f.owner_name as owner_name, g.group_name as permission_group, g.i_all_users_names as user_names, m.Perm_Name as permission from dm_sysobject f,dm_acl a,dm_group g,dm_user s, dm_dbo.Permissions_Map m, usac_folder uf where f.acl_name=a.object_name and s.user_name=a.r_accessor_name and s.user_name=g.group_name and m.Perm_Id = a.r_accessor_permit and f.i_folder_id = uf.r_object_id and uf.r_folder_path IS NOT NULLSTRING and folder ('/cabinet/level 1 folder',descend) order by i_all_users_names enable(ROW_BASED)
Results were supposed to look like this : DQL
Folder_path | owner_name | permissions_group | user_name | permissions
-------------------------------------------------------------------------------------------------------------------------
/cabinet/fldr1/fldr2 James div_head Ed Delete
/cabinet/fldr1/fldr2 James div_sup Mary Write
/cabinet/fldr1/fldr2 James div_emps Stacey Read
/cabinet/fldr1/fldr2 James div_emps Dan Read
This query runs perfectly in our Test Environment (The data is less compared to prod). However when we deployed the query to production it was causing the docbase to hang. and we had to restart the CS Machine.
Any insights on how we could fine tune the query or use any alternate approaches to generate this report will be appreciated.
Thanks,
Lasya.
