000031350 - Archer 5.x - SQL script to find out the CRUD permission applied to all access roles

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000031350
Applies ToArcher 5.X
IssueSQL script for CRUD permission applied to all access roles
Resolution-- pages and roles with CRUD permissions. The create_date and created by would show who has created this permission where update_date and updated by would point out the time it is updated and the user who has updated it -- 
Select P.task_name,P.task_desc, 
R.xcomp_access_role_name, R.xcomp_access_role_desc, 
Pr.xpagesaccessroles_write, PR.xpagesaccessroles_delete, 
Pr.create_date, (Select user_username from tbluser where user_id=Pr.create_login)AS 'created by', 
Pr.update_date, (Select user_username from tbluser where user_id=PR.update_login) AS 'updated by' 
from tblXPagesRoles PR 
join tblPage P on PR.task_id=P.task_id 
Join tblXCompaniesAccessRoles R on R.xcomp_access_role_id=PR.access_role_id 
-- Roles associated with the groups-- 
Select R.xcomp_access_role_name, G.group_name from tblXGroupsRoles GR 
join tblXCompaniesAccessRoles R on GR.xcomp_access_role_id=R.xcomp_access_role_id 
join tblGroup G on G.group_id=GR.group_id 
-- Roles associated with the users-- 
Select R.xcomp_access_role_name, U.user_username from tblXUsersRoles UR 
join tblXCompaniesAccessRoles R on UR.xcomp_access_role_id=R.xcomp_access_role_id 
join tblUser U on U.user_id=UR.user_id 
-- Users associated with the groups-- 
Select G.group_name, U.user_username from tblXGroupsUsers GU 
join tblGroup G on G.group_id=GU.group_id 
join tblUser U on U.user_id=GU.user_id