PS Security - sql to obtain a list of navigation

Question

Is it possible with SQL Server to get a list of navigation for a given role?

1. Get all the permissions listed under that role
2. For each of the permissions, get the pages
3. For each of pages/menu name get the list of authorized components
4. For each of the list of components, get the PS Navigation

tried it with qry manager and it din't work

Answer 1

Yes this is possible to do with SQL but not in PSQUERY if you want a complete menu path

1. Get all the permissions listed under that role

Found in table: PSROLECLASS

2. For each of the permissions, get the pages

Found in table: PSAUTHITEM

3. For each of pages/menu name get the list of authorized components

Found in table: PSMENUITEM

4. For each of the list of components, get the PS Navigation

The content references are found in PSPRSMDEFN.

Fields to join on:

PORTAL_URI_SEG1 = MenuName

PORTAL_URI_SEG2 = Component

If you want the complete navigation path (eg: Root-> PeopleTools-> Security-> Permissions and Roles-> Permission Lists), that's a more complicated query. I ended up using a CTE which contained a recursive query on PSPRSMDEFN. Once I had that I joined the CTE to the above tables and was able to create something similar to what you want. I suggest adding the option (MAXRECURSION 20); hint to your recursive query to ensure you don't run into trouble while developing this