Make It Easy To Check User's Accessibility To PS Queries

In PeopleSoft, checking whether a user has access to a PS Query is sometime a very frustrating job, particularly when the query has references to many base records which are added into multiple access groups in different query trees, and these query trees and access groups are granted to different permission lists which are owned by different roles.

Unfortunately, I am the guy who have always been asked 'How come I am not able to view/edit query ... blah blah...' and I have spent too much time and efforts on this. Yesterday I eventually decided something must be done to pull me out of this repetitive and monotonous job, so I wrote the PL/SQL script chk_query_access.sql.

This script asks for 3 parameters:

1) PS Query Name: wildcards (%, _) are accepted. Escape character '\' is allowed too. For example, you can type in full query name N_Q006_SR_LOA, or you can also type in a query name pattern N\_Q00_\_% to check accessibility for queries N_Q001 to N_Q009.

2) User ID: is case-sensitive

3) Verbose Level: ranges from 0 - 3

• Level 0: only shows query grant status. This is the default level.

SQL> @d:\SQL\chk_query_access.sql
SQL> SET ECHO OFF
Query Name (wilecard accepted): N_Q006_SR_LOA
User ID: PSTEST
Verbose Level:
0 - Show query grant status (default)
1 - Show query/record grant status
2 - Show query/record grant status and grant paths
3 - Show query/record grant status and all paths
Your Choice(0,1,2,3):
old   5:                v_qryname_pattern PSQRYDEFN.QRYNAME%TYPE        := trim('&prompt_qryname');
new   5:                v_qryname_pattern PSQRYDEFN.QRYNAME%TYPE        := trim('N_Q006_SR_LOA');
old   6:                v_oprid PSOPRDEFN.OPRID%TYPE                    := trim('&prompt_oprid');
new   6:                v_oprid PSOPRDEFN.OPRID%TYPE                    := trim('PSTEST');
old   7:                v_verbose_lvl INTEGER                                   := &prompt_verbose;
new   7:                v_verbose_lvl INTEGER                                   :=          0;
===========================================================
=== Checking User(PSTEST)'s access to query 'N_Q006_SR_LOA'
===
===
===  Query 'N_Q006_SR_LOA' granted to 'PSTEST'
===

PL/SQL procedure successfully completed.

• Level 1: shows query grant status and record grant status. This is useful when you want to know what record is not granted if the query is not accessible.

...
...
...
===========================================================
=== Checking User(PSTEST)'s access to query 'N_Q006_SR_LOA'
===
-----------------------------------------------------------
>>> Record 1: N_STNT_PERS_VW
>>> Record granted
-----------------------------------------------------------
>>> Record 2: N_STNT_SUMAC_VW
>>> Record not granted
-----------------------------------------------------------
>>> Record 3: N_LOA
>>> Record granted
===
===  Query 'N_Q006_SR_LOA' not granted to 'PSTEST'
===
...

• Level 2: show query grant status, record grant status, and grant path. At this level the script also shows the whole grant path, eg Record - Query Tree/Access Group - Permission List - Role - User Profile.

...
...
...
===========================================================
=== Checking User(PSTEST)'s access to query 'N_Q006_SR_LOA'
===
-----------------------------------------------------------
>>> Record 1: N_STNT_PERS_VW
[Y] N_QUERY_TREE_RPT.N_RPTQAG_MOD_RANK -> N_R030_SR_MODULE_RANKING -> N_EXAM_QRY_EU -> PSTEST
[Y] N_QUERY_TREE_RPT.N_RPTQAG_BOE_ATTACH -> N_R031_SR_BOE_ATTACHMENTS -> N_EXAM_QRY_EU -> PSTEST
>>> Record granted
-----------------------------------------------------------
>>> Record 2: N_STNT_SUMAC_VW
>>> Record not granted
-----------------------------------------------------------
>>> Record 3: N_LOA
[Y] N_QUERY_TREE_DEN.N_DNQAG_LOA -> N_PROG_PLAN_ADMIN_QRY_EU -> N_PROG_PLAN_ADMIN_QRY_EU -> PSTEST
[Y] N_QUERY_TREE_DEN.N_DNQAG_LOA -> N_PROG_PLAN_ADMIN_QRY_IT -> N_PROG_PLAN_ADMIN_QRY_IT -> PSTEST
[Y] N_QUERY_TREE_RPT.N_RPTQAG_BOE_ATTACH -> N_R031_SR_BOE_ATTACHMENTS -> N_EXAM_QRY_EU -> PSTEST
>>> Record granted
===
===  Query 'N_Q006_SR_LOA' not granted to 'PSTEST'
===
...

• Level 3: the most detailed verbose, especially useful when a query is not accessible and you need to find out at what position the granting is not done.
  
  

...
...
...
===========================================================
=== Checking User(PSTEST)'s access to query 'N_Q006_SR_LOA'
===
-----------------------------------------------------------
>>> Record 1: N_STNT_PERS_VW
[Y] N_QUERY_TREE_RPT.N_RPTQAG_MOD_RANK -> N_R030_SR_MODULE_RANKING -> N_EXAM_QRY_EU -> PSTEST
[Y] N_QUERY_TREE_RPT.N_RPTQAG_BOE_ATTACH -> N_R031_SR_BOE_ATTACHMENTS -> N_EXAM_QRY_EU -> PSTEST
>>> Record granted
-----------------------------------------------------------
>>> Record 2: N_STNT_SUMAC_VW
[N] N_QUERY_TREE_RPT.N_RPTQAG_MOD_RANK -> N_R030_SR_MODULE_RANKING -> N_EXAM_QRY_EU
[N] N_QUERY_TREE_RPT.N_ROGQAG_ENRL_STATS2 -> N_R042_SR_ENROL_STATS2
>>> Record granted
-----------------------------------------------------------
>>> Record 3: N_LOA
[Y] N_QUERY_TREE_DEN.N_DNQAG_LOA -> N_PROG_PLAN_ADMIN_QRY_EU -> N_PROG_PLAN_ADMIN_QRY_EU -> PSTEST
[Y] N_QUERY_TREE_DEN.N_DNQAG_LOA -> N_PROG_PLAN_ADMIN_QRY_IT -> N_PROG_PLAN_ADMIN_QRY_IT -> PSTEST
[Y] N_QUERY_TREE_RPT.N_RPTQAG_BOE_ATTACH -> N_R031_SR_BOE_ATTACHMENTS -> N_EXAM_QRY_EU -> PSTEST
>>> Record granted
===
===  Query 'N_Q006_SR_LOA' granted to 'PSTEST'
===

Updates:

• 18-Nov-2009: Added check for defnition security.
• 24-Nov-2009: Added support for command line arguments. Added access group cascading check.