Monday, November 9, 2009

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.

4 comments:

可愛 said...

成功多屬於那些很快做出決定,卻又不輕易變更的人。而失敗也經常屬於那些很難做出決定,卻又經常變更的人 ....................................................

Anonymous said...

Genial fill someone in on and this enter helped me alot in my college assignement. Say thank you you seeking your information.

雪糕 said...

Always read stuff that will make you look good if you die in the middle of it.............................................

太可怕 said...

弱者等待時機,強者製造時機........................................