Service Desk Practitioners Forum
cancel

Anyone have a SQL Query for Forms etc used by Roles?

SOLVED
Go to solution
Highlighted
Ken Briscoe
Acclaimed Contributor.

Anyone have a SQL Query for Forms etc used by Roles?

Hi, does anyone have a SQL query that extracts a list of forms, views, templates and actions used for each entity in each role?
I have the query that's appeared on the forum to display entity access details, but need an automated way of listing all the other components used by each role
Thanks.
And long live OVSD 4.5!
My email is kenilian@bigpond.com.au
10 REPLIES
George M. Meneg
Acclaimed Contributor.

Re: Anyone have a SQL Query for Forms etc used by Roles?

Hello Ken,

I don't think that this can be done easily with a sql query because roles are hierarchical objects and Hierarchical queries are pretty hard stuff if are not supported directly by the db.
menes fhtagn
Ken Briscoe
Acclaimed Contributor.

Re: Anyone have a SQL Query for Forms etc used by Roles?

Thanks George - although in my case I have no included roles, so I was hoping there was a way to get to the detail for each entity in each role. Oh well..will just have to manually work thru each entity/role.
My email is kenilian@bigpond.com.au
Vasily Kamenev
Acclaimed Contributor.
Solution

Re: Anyone have a SQL Query for Forms etc used by Roles?

Hi
Look at attachment.
The easy way to do this complex requests - read the views from sd db.

Vasily
Nasser Kolathum
Super Contributor.

Re: Anyone have a SQL Query for Forms etc used by Roles?

Ken and all,
I am attaching another good sql script, will get you the complete information from DB. Hope this is what you are looking for.

Best regards,
Nasser
George M. Meneg
Acclaimed Contributor.

Re: Anyone have a SQL Query for Forms etc used by Roles?

Ok, if there is not included role then this can help you

-- Roles Access

select rol.rol_description as 'Role', ent.ent_name as 'Entity', 'Action' as 'Type', acl.acl_text as 'Name'
from rep_action_access aac inner join rep_actions_locale acl on acl.acl_act_oid=aac.aac_act_oid
inner join rep_roles rol on aac.aac_rol_oid=rol.rol_oid
inner join rep_actions act on aac.aac_act_oid=act.act_oid
inner join ifc_entities ent on act.act_ent_oid=ent.ent_oid
where acl.acl_lng_oid=1033

union

select rol.rol_description as 'Role', ent.ent_name as 'Entity', 'Form' as 'Type', flo.flo_text as 'Name'
from rep_form_access fac inner join rep_forms_locale flo on fac.fac_foi_oid=flo.flo_foi_oid
inner join rep_roles rol on fac.fac_rol_oid=rol.rol_oid
inner join rep_forminfos foi on fac.fac_foi_oid=foi.foi_oid
inner join ifc_entities ent on ent.ent_oid=foi.foi_ent_oid
where flo.flo_lng_oid=1033

union

select rol.rol_description as 'Role', ent.ent_name as 'Entity', 'View' as 'Type', vtl.vtl_text as 'Name'
from rep_view_access vac inner join rep_view_title_locale vtl on vac.vac_viw_oid=vtl.vtl_vti_oid
inner join rep_roles rol on vac.vac_rol_oid=rol.rol_oid
inner join rep_view_title_infos vti on vtl.vtl_vti_oid=vti.vti_oid
inner join ifc_entities ent on vti.vti_ent_oid=ent.ent_oid
where vtl.vtl_lng_oid=1033


order by 1,4,2,3

This doesn't include templates but it's pretty easy to add them using the same logic.
Join tables rep_templates, rep_template_access and ifc_entities.
menes fhtagn
George M. Meneg
Acclaimed Contributor.

Re: Anyone have a SQL Query for Forms etc used by Roles?

Hello Nasser,

This is pretty interesting script but I'm afraid it won't work on MSSQL.
menes fhtagn
Ken Briscoe
Acclaimed Contributor.

Re: Anyone have a SQL Query for Forms etc used by Roles?

Thanks George and Vasily - that's a big help and looks like what I need.
Nasser - your script is awesome and would be a huge help when we go into different clients. But does George's comment mean it's Oracle only? An MSSQL version would be fantastic...I know an expert MSSQL DBA so maybe I can get it converted. (My SQL is just above novice level!).
Thanks......Ken.
My email is kenilian@bigpond.com.au
George M. Meneg
Acclaimed Contributor.

Re: Anyone have a SQL Query for Forms etc used by Roles?

Hello Ken,

If you had problems with templates this is the rest of the query. Just add another "union" before that part and move the "order 1,4,3,2" after that part.

select rol.rol_description as 'Role', ent.ent_name as 'Entity', 'Template' as 'Type', tem.tem_name as 'Name'
from rep_template_access tac inner join rep_roles rol on tac.tac_rol_oid=rol.rol_oid
inner join rep_templates tem on tem.tem_oid=tac.tac_tem_oid
inner join ifc_entities ent on ent.ent_oid=tem.tem_ent_oid

Don't assign more than 3 points to this answer because this should be included in my previous post but I forgot it and I was a lazy bum to wrote it and gave you instructions instead.
menes fhtagn
Ken Briscoe
Acclaimed Contributor.

Re: Anyone have a SQL Query for Forms etc used by Roles?

Come on George - how could anyone call you lazy when you supply such great answers! This will save me heaps of time - much better than manually going through a lot of rules. Thanks. (5 pts is the least I ca do!).
My email is kenilian@bigpond.com.au
Ken Briscoe
Acclaimed Contributor.

Re: Anyone have a SQL Query for Forms etc used by Roles?

Come on George - how could anyone call you lazy when you supply such great answers! This will save me heaps of time - much better than manually going through a lot of rules. Thanks. (5 pts is the least I can do!).
My email is kenilian@bigpond.com.au