Add roles to documents via SQL

James Harrison asked on September 15, 2015 00:37

Hello, Our client has a large amount of documents that they would like to add security roles to . Instead of adding them one by one, is there a SQL command we can use or an API call, to add roles to documents dynamically?

We know the Document ID and the Role ID for each that we would like to add.

Thanks for your help

James

Recent Answers

Maarten van den Hooven answered on September 15, 2015 09:00 (last edited on September 18, 2015 16:20)

Hi James, I for sure would use the Kentico API because messing in the DB with nodes can be very complicated and mess up your site.

You need to use the SetRolePermissions Method, but an very important note is that it changes the permission for the node where the document inherits the security from. This can be the root for example and that is not what you want. So before you set role permissions first you need to break the inheritance.

Summarized these are the steps you should do:

Loop through your document ID's
Get the TreeNode by document ID (DocumentHelper.GetDocument(documentId, TreeProvider))
Break the inherintance of the document (AclInfoProvider.BreakInherintance(node, bool copyParentPermissions))
To be sure we delete normally also the permissions (see example code below)
Get the role info (RoleInfoProvider.GetRoleInfo(RoleCodeName, SiteContext.CurrentSiteName))

Set the role permissions with the AclItemInfoProvider.SetRolePermissions Method ( Eaxmple code in Kentico V7 )

// Get ID of ACL used on API Example document
int nodeACLID = ValidationHelper.GetInteger(node.GetValue("NodeACLID"), 0);

if (nodeACLID > 0)
{
    // Delete all ACL items
    AclInfoProvider.ClearPermissions(node.NodeID, nodeACLID);
    AclInfoProvider.DeleteAclInfo(nodeACLID);
}

If this answer helped you, please vote for my answer :-)

0 votesVote for this answer Mark as a Correct answer

Joshua Adams answered on September 15, 2015 19:20

You can take that a step further as well and actually build a small module or page that you can select the users/roles and the pages and set the permissions using the api. You could build a page with uniselectors that allow you to control selecting your users or roles that you want added to the selected page(could be another uniselector set to the nodes or documents, then attach them through the api. I would just make sure that they page is behind security checks, so no one can access the page or use it maliciously.

This would also allow you to use the page in the future as well, and not have to find the query or code that you ran once.

0 votesVote for this answer Mark as a Correct answer

Maarten van den Hooven answered on September 23, 2015 07:54

Hi James, Did you succeeded with assigning permissions to your documents?

0 votesVote for this answer Mark as a Correct answer

Lakshan Perera answered on December 18, 2017 12:21 (last edited on December 18, 2017 12:22)

Hi, can describe the table structure for tree node permissions? Need to prepare a sql query to get what roles have modify permission for a given tree node.

0 votesVote for this answer Mark as a Correct answer