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.
