Get all Security roles with related Privileges duties permissions and related paths of menuitem

This post will guid you to get all security roles with it related privileges duties permissions with related paths of menu item

Run this job to fill SysSecFlatDataTable, after job complete process you can open form from AOT SysSecObjectsInRole to confirm data population.

static void updateSecurityData(Args _args)

{

   sECURITYROLE secRoleTbl;

   SecuritySubRole secSubRoleTbl;

   SECURITYROLE secSubRoles;

   SecurityRoleTaskGrant secRoleTaskGrantTbl;

   SecurityTask secTaskTbl;

   SecurityTaskExplodedGraph secTaskExplodedGraphTbl;

   SecurityTask privilegesTbl;

   SecurityTaskEntryPoint secTaskEntryPointTbl;

   SecurableObject entryPointTbl;

   SysSecFlatDataTable dataTable;

   delete_from dataTable;

   while select * from secRoleTbl

       outer join * from secSubRoleTbl where (secSubRoleTbl.SecurityRole == secRoleTbl.RecId)

       outer join * from secSubRoles where (secSubRoleTbl.SecuritySubRole == secSubRoles.RecId)

       join   * from secRoleTaskGrantTbl where(secRoleTbl.RECID == secRoleTaskGrantTbl.SECURITYROLE || secSubRoles.RECID == secRoleTaskGrantTbl.SECURITYROLE)

       join * from secTaskTbl where (secRoleTaskGrantTbl.SECURITYTASK == secTaskTbl.RECID)

       join * from secTaskExplodedGraphTbl where (secTaskExplodedGraphTbl.SECURITYTASK == secTaskTbl.RECID)

       join * from privilegesTbl where (secTaskExplodedGraphTbl.SECURITYSUBTASK == privilegesTbl.RECID)

       join * from secTaskEntryPointTbl where (secTaskExplodedGraphTbl.SECURITYSUBTASK == secTaskEntryPointTbl.SECURITYTASK)

       join * from entryPointTbl where secTaskEntryPointTbl.ENTRYPOINT == entryPointTbl.RECID

       {

             ttsbegin;

           SysSecObjectsMiner::AddRelatedRolesRecToDataTable(dataTable, secRoleTbl.RecId, secRoleTbl.AOTNAME, secRoleTbl.NAME,

            secSubRoles.RecId, secSubRoles.AotName, secSubRoles.Name, secTaskTbl.RecId, secTaskTbl.AOTNAME, secTaskTbl.NAME, privilegesTbl.RecId, privilegesTbl.AOTNAME, privilegesTbl.NAME,

           entryPointTbl.RecId, entryPointTbl.NAME, entryPointTbl.TYPE, secTaskEntryPointTbl.PERMISSIONGROUP);

            ttscommit;

   }

}

And then run this job to truncate and run batch job to update Xref Tables

static void UpdateCrossRefBatch(Args _args)

{

    ;

    xRefUpdate::truncateXrefTables();

    xRefUpdateIL::updateAllXref(true, false, true);

    info("Done, cross reference update batch job created.");

}

And then create table with 2 field path string 900 and RefRecId recid to be filled by below jobs

static void updatePathData(Args _args)

{

    Custom_SecurityPaths Custom_SecurityPaths;

    SysSecFlatDataTable SysSecFlatDataTable;

    #TreeNodeSysNodeType

   #Properties

   #AOT

   TreeNode menuItemNode = TreeNode::findNode(@"\Menu Items\Display\CustTableListPage");

   TreeNode menuNode;

   xRefPaths xRefPaths;

   xRefReferences xRefReferences;

   TreeNode parentNode;

   Str path;

    while select SysSecFlatDataTable where SysSecFlatDataTable.EntryPointType==SecurableType::MenuItemDisplay && SysSecFlatDataTable.EntryPoint>''{

      menuItemNode = TreeNode::findNode(@"\Menu Items\Display\"+SysSecFlatDataTable.EntryPoint);

   xRefPaths = xRefPaths::find(menuItemNode.treeNodePath());

       while select xRefReferences

           where xRefReferences.referencePathRecId == xRefPaths.RecId

           && xRefReferences.Reference == XRefReference::Read

       {

           path = SysLabel::labelId2String(menuItemNode.AOTgetProperty(#PropertyLabel));

           menuNode = TreeNode::findNode(xRefPaths::findRecId(xRefReferences.xRefPathRecId).Path);

           if(menuNode && SysTreeNode::path2ApplObjectType(menuNode.treeNodePath()) == UtilElementType::Menu)

           {

               parentNode = menuNode.AOTparent();

               while(parentNode && parentNode.treeNodePath() != #MenusPath)

               {

                   path = SysLabel::labelId2String(parentNode.AOTgetProperty(#PropertyLabel))  + " > " + path;

                   parentNode = parentNode.AOTparent();

               }

            Custom_SecurityPaths.clear();

            Custom_SecurityPaths.Path=path;// info(path);

            Custom_SecurityPaths.RefRecid=SysSecFlatDataTable.RecId;

            Custom_SecurityPaths.doInsert();

           }

while select SysSecFlatDataTable where SysSecFlatDataTable.EntryPointType==SecurableType::MenuItemDisplay && SysSecFlatDataTable.EntryPoint>''{

      menuItemNode = TreeNode::findNode(@"\Menu Items\Action\"+SysSecFlatDataTable.EntryPoint);

   xRefPaths = xRefPaths::find(menuItemNode.treeNodePath());

       while select xRefReferences

           where xRefReferences.referencePathRecId == xRefPaths.RecId

           && xRefReferences.Reference == XRefReference::Read

       {

           path = SysLabel::labelId2String(menuItemNode.AOTgetProperty(#PropertyLabel));

           menuNode = TreeNode::findNode(xRefPaths::findRecId(xRefReferences.xRefPathRecId).Path);

           if(menuNode && SysTreeNode::path2ApplObjectType(menuNode.treeNodePath()) == UtilElementType::Menu)

           {

               parentNode = menuNode.AOTparent();

               while(parentNode && parentNode.treeNodePath() != #MenusPath)

               {

                   path = SysLabel::labelId2String(parentNode.AOTgetProperty(#PropertyLabel))  + " > " + path;

                   parentNode = parentNode.AOTparent();

               }

            Custom_SecurityPaths.clear();

            Custom_SecurityPaths.Path=path;// info(path);

            Custom_SecurityPaths.RefRecid=SysSecFlatDataTable.RecId;

            Custom_SecurityPaths.doInsert();

           }

while select SysSecFlatDataTable where SysSecFlatDataTable.EntryPointType==SecurableType::MenuItemDisplay && SysSecFlatDataTable.EntryPoint>''{

      menuItemNode = TreeNode::findNode(@"\Menu Items\Output\"+SysSecFlatDataTable.EntryPoint);

   xRefPaths = xRefPaths::find(menuItemNode.treeNodePath());

       while select xRefReferences

           where xRefReferences.referencePathRecId == xRefPaths.RecId

           && xRefReferences.Reference == XRefReference::Read

       {

           path = SysLabel::labelId2String(menuItemNode.AOTgetProperty(#PropertyLabel));

           menuNode = TreeNode::findNode(xRefPaths::findRecId(xRefReferences.xRefPathRecId).Path);

           if(menuNode && SysTreeNode::path2ApplObjectType(menuNode.treeNodePath()) == UtilElementType::Menu)

           {

               parentNode = menuNode.AOTparent();

               while(parentNode && parentNode.treeNodePath() != #MenusPath)

               {

                   path = SysLabel::labelId2String(parentNode.AOTgetProperty(#PropertyLabel))  + " > " + path;

                   parentNode = parentNode.AOTparent();

               }

            Custom_SecurityPaths.clear();

            Custom_SecurityPaths.Path=path;// info(path);

            Custom_SecurityPaths.RefRecid=SysSecFlatDataTable.RecId;

            Custom_SecurityPaths.doInsert();

           }

       }

}

}

Then outer join the paths table with SysSecFlatDataTable 1:n and you will get each role and entry point path with access