Need to list what roles have Modify permission for a given TreeNode (SQL Query)

Lakshan Perera asked on December 18, 2017 10:35

Need to list what roles have Modify permission for a given TreeNode. I need a SQL query. Thanks

Recent Answers


Juraj Ondrus answered on December 18, 2017 12:38

Hi,
You need to check the NodeACLID i9n the CMS_Tree table and then get the values from CMS_ACL and CMS_ACLItem tables. But it may not be that easy - the SQL query can get easily really complicated. Regrettably, there is no out of the box report or SQL query available for this.

1 votesVote for this answer Mark as a Correct answer

Lakshan Perera answered on December 19, 2017 06:31 (last edited on December 19, 2017 07:50)

Hi Juraj Ondrus, What are the Allowed and Denied columns in CMS_ACLItem table? There are no relationships.

I want to list all role that have Modify permission.

SELECT rle.*

FROM CMS_ACLItem aclItem

JOIN  [dbo].[CMS_Role] rle ON aclItem.RoleID = rle.RoleID

WHERE aclItem.ACLID = <ACLID>
0 votesVote for this answer Mark as a Correct answer

Juraj Ondrus answered on December 19, 2017 10:01

Hi,
Well, it may not be that easy since if the page level ACL permissions are not set, the module permissions come to the game. So, you may need to take these into account too.

So, for the content module permissions, these are the tables you need to use (arrows indicate the relationship direction): CMS_Resource <- CMS_Permission <- CMS_RolePermission -> CMS_Role.

And for the page level ACL permissions: CMS_Tree -> CMS_ACL <- CMS_ACLItem -> CMS_User/CMS_Role tables.

Configuration of permissions shared for all culture versions Multiple nodes can use same ACL – NodeACLID – share the configuration of permissions Only one is the owner – NodeIsACLOwner flag There is an inheritance of ACLs – ACLInheritedACLs – list of IDs of inherited configurations

Permission configuration for a user or role is stored as an integer value. Each bit represents one permission. So, if the value there is 32, the binary value is 0100000 (so, first 0 means ModifyPermissions permission, 1 - Browse tree, then goes Destroy, Delete, Create, Modify, Read). So, the 32 allowed value means the Browse tree permission is granted.
The same logic is applied to denial permissions for the ACLs. You also need to take into account the permissions inheritance - either restored inheritance from parent or the inheritance can be broken and other permissions set for the child items.

So, maybe it should be better to create API code and use the methods like IsAuthorizedPerDocument, IsAuthorizedPerTreeNode, IsAuthorizedPerClassName, IsAuthorizedPerResource, etc., to do the checks and logic.

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.