how to fetch the objects in dm_acl who grants the same sets of permission at the same time

Question

I am doing a JavaFx project connected to Documentum data source . lets say I have a set of groups : group1 , group2 , group3 How can I query all the acl_names from dm_acl who has only and exactly these groups as their r_acceesor_name . For example using the following query I can get the r_accessor_name of a specific acl:

select r_acceessor_name from dm_acl where object_name = 'My_acl_name' enable (row_based)

and the output is :

group1
group2
group3

But what I want to do is the reverse of this .I've tried the following query :

select object_name from dm_acl where r_accessor_name = 'group1' or r_accessor_name = 'group2' or r_accessor_name = 'group3' enable (row_based)

But the problem with the output is that it's too general which makes sense because of keyword OR then I tried this :

select object_name from dm_acl where r_accessor_name = 'group1' and r_accessor_name = 'group2' and r_accessor_name = 'group3' enable (row_based)

which is too specific ==> zero output

you made very confusing question. Try presenting your question to your grandma and then write it here. First your need or problem you need to solve and then what you've done. — Miki, Oct 27 '16 at 07:12
btw, on every question you make regarding Documetnum you start with your project. It is irrelevant — Miki, Oct 27 '16 at 08:10

score 2 · Accepted Answer · answered Oct 27 '16 at 08:09

You confused yourself more than you can think of. Every ACL object has at least two entries: dm_owner and dm_world where first represents repository owner and the last represents every user in repository.

Because of it your objective

... all the acl_names from dm_acl who has only and exactly these groups ...

cannot be achieved. However, beside this, your query

SELECT object_name FROM dm_acl 
WHERE r_accessor_name = 'group1' 
AND r_accessor_name = 'group2' 
AND r_accessor_name = 'group3' 
ENABLE (ROW_BASED)

is the best you can get. Your problem is that you really don't have ACL in your repository that contains at least these 3 groups. This leads you to the your initial question: either you made wrong presumption either you tried to too much to generalize your question that you've omitted the important part of it. ;)

I think your query always returns 0 rows, as the conditions in WHERE clause can not ever match. When you use ROW_BASED hint, each row in a result set has exactly one value for repeating attribute, so when you are trying to say give me object with the name 'a' AND name 'b' it will never return you anything — Sergi, Oct 28 '16 at 15:18

score 1 · Answer 2 · answered Oct 27 '16 at 08:39

I think I read your mind and came to what you really need:

SELECT object_name FROM dm_acl 
WHERE r_accessor_name IN ('group_A', 'group_B', 'group_C')  
GROUP BY object_name
HAVING COUNT(DISTINCT r_accessor_name) = 2 
ENABLE (row_based)

Try it and see if it'll work.

how to fetch the objects in dm_acl who grants the same sets of permission at the same time

2 Answers2