!="" (Not equal to empty string) works for most of the cases in a DQL query. Here, Permission table provide permissions info for both user and group. Wherever group_trustee.name is empty, it means that that row is populated for user_trustee.name. There is another column name trustee_type that can help here. If you just want to get group permissions for a group, you can add condition for trustee_type = "group". Let me know if this helps.
GET permission.object_type, isinherited, path.absname, path.msu.type, group_trustee.name, readable_permission
IF trustee_type = "group"
and path.msu.name = "winnas_share"
I tried using ="" and !="" expressions both in DQL and in the SQLite explorer and whilst it worked as expected in SQLite it didn't return any rows when added to my DQL statement.
The scenario is that my customer wants to enforce their standards for granting permissions to shares, such that each share has 3 trustee groups for read only, modify and full control. Users are then assigned to a member group of the associated trustee group.
I've been asked to create a custom report to identify cases where the standard hasn't been followed, for example where users have been assigned directly to a trustee group, or where the trustee group is a member of the user group rather than the other way around. Something like:
Get path.absname, path.msu.name, group_trustee.name, group_trustee.memberusers.name, readable_permission
If group_trustee.memberusers.name !="" and trustee_type="group
Overall there are likely to be around 8-10 non compliant scenarios that I'll need to create a report for, most of which will need to look for blank or non blank fields.
There seems to be some issue with != or = working with multivalued columns in DQL. While that is being looked at, can you utilize following query and perform minor post-processing to report on (Or elimnate) rows with users. Note that isinherited=0 will limit the rows to unique permission set.
Get path.absname, path.msu.name, group_trustee.name, readable_permission, isinherited, group_trustee.memberusers.name
If group_trustee.memberusers.name !="" and trustee_type="group"
and path.msu.name = "Share1"
and isinherited = 0
Format group_trustee.memberusers.name as CSV
One point to note is that above query will provide information for all the users under a group (whether direct or indirect) which may not fulfill your use-case. However, today this should be achievable using some customization outside of DQL. For instance get all groups where direct members are listed using a DI API and then feed the list to DQL query for permissions and path information.
Thanks for your reply Rishi, I'll give that a try next week when back onsite. I was wondering whether another work around might be to use the count operation, such as:
Or does this have the same limitations with multivalued columns?