000029441 - Query to retrieve information about all record permission fields in RSA Archer 5.x

--PRE RSA ARCHER 5.5.2 
select M.module_name,FT.field_name, Fd.field_id, FDU.record_permission, FDu.inherited_permission,FDU.automatic,
FDU.default_creator_group, FDU.record_creator_default, FDU.record_creator_read,FDU.record_creator_delete, FDU.record_creator_update,
FDU.all_group_delete, FDU.all_group_read, FDU.all_group_update,
FDU.all_user_delete, FDU.all_user_read, FDU.all_user_update, FDU.auto_restrict_group,
FGL.group_id, FGL.cascade_flag as Groupcascade,FGL.record_update as grouprecupdate, FGL.record_delete as grouprecorddelete, FGL.show_users as groupshowusers
,FGL.default_flag as groupdefflag, FI.inherit_field_id,
FUL.user_id , FUL.record_delete as userdelete, FUL.record_update as userupdate,
G.group_name, U.user_username, FT11.field_name as InheritedFromFieldName, M1.module_name, FR.alias asRuleName
from tblIVFieldDef FD
join tblFieldTranslation FT ON FD .field_id= FT.field_id
join tblLevel L ON l.level_id = FD.level_id
join tblModule M ON M.module_id = L.module_id
join tblIVFieldDefUserGroup FDU ON FDu.field_id = FD.field_id
left outer join tblXIVFilteredGroupList FGL ON FGL.field_id = FD.field_id
left outer join tblXIVFilteredUserList FUL ON FUL.field_id = FD.field_id
left outer join tblGroup G ON G.group_id = FGl.group_id
left outer join tblUser U ON U.user_id = FUL.user_id
left outer join tblFieldInherit FI ON FI.field_id = FD.field_id
left outer join tblFieldTranslation FT11 ON FT11.field_id = FI.inherit_field_id
left join tblIVFieldDef FD1 ON FD1.field_id = FI.inherit_field_id
left join tblLevel L1 ON l1.level_id = FD1.level_id
left join tblModule M1 ON M1.module_id = L1.module_id
left outer join tblfieldRule FR ON FR.field_id = FD.field_id
where FD.field_type_id = 8
order by field_id
--RSA ARCHER 5.5.3 OR LATER
select MT.module_name,FT.field_name, Fd.field_id, FDU.record_permission, FDu.inherited_permission,FDU.automatic,
FDU.default_creator_group, FDU.record_creator_default, FDU.record_creator_read,FDU.record_creator_delete, FDU.record_creator_update,
FDU.all_group_delete, FDU.all_group_read, FDU.all_group_update,
FDU.all_user_delete, FDU.all_user_read, FDU.all_user_update, FDU.auto_restrict_group,
FGL.group_id, FGL.cascade_flag as Groupcascade,FGL.record_update as grouprecupdate, FGL.record_delete as grouprecorddelete, FGL.show_users as groupshowusers
,FGL.default_flag as groupdefflag, FI.inherit_field_id,
FUL.user_id , FUL.record_delete as userdelete, FUL.record_update as userupdate,
G.group_name, U.user_username, FT11.field_name as InheritedFromFieldName, MT1.module_name, FR.alias asRuleName
from tblIVFieldDef FD
join tblFieldTranslation FT ON FD .field_id= FT.field_id
join tblLevel L ON l.level_id = FD.level_id
join tblModule M ON M.module_id = L.module_id
join tblModuleTranslation MT on MT.module_id=M.module_id
join tblIVFieldDefUserGroup FDU ON FDu.field_id = FD.field_id
left outer join tblXIVFilteredGroupList FGL ON FGL.field_id = FD.field_id
left outer join tblXIVFilteredUserList FUL ON FUL.field_id = FD.field_id
left outer join tblGroup G ON G.group_id = FGl.group_id
left outer join tblUser U ON U.user_id = FUL.user_id
left outer join tblFieldInherit FI ON FI.field_id = FD.field_id
left outer join tblFieldTranslation FT11 ON FT11.field_id = FI.inherit_field_id
left join tblIVFieldDef FD1 ON FD1.field_id = FI.inherit_field_id
left join tblLevel L1 ON l1.level_id = FD1.level_id
left join tblModule M1 ON M1.module_id = L1.module_id
left join tblModuleTranslation MT1 on MT1.module_id=m1.module_id
left outer join tblfieldRule FR ON FR.field_id = FD.field_id
where FD.field_type_id = 8
order by field_id