on Jun 14, 2016Article Content
| Article Number | 000031350 |
| Applies To | Archer 5.X |
| Issue | SQL 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_create,Pr.xpagesaccessroles_read, 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 |