Oracle Designer Report – SQL Script for Roles vs. Modules

Lucas Jellema
0 0
Read Time:1 Minute, 20 Second

On my current project, we had a request from the customer to produce an overview of all the (Database) Roles in the Application with a list of Modules, Screens and Reports, that are assigned to those Roles. Since I could not find this overview in shipped Repository Reports, and I know I did not implement it in the Repository Object Browser, I quickly wrote the following SQL Query, that you can execute as any Designer (Repository) User:

exec jr_context.set_workarea('AGS_WA');

select case rn
       when 1
       then role
       end role
, module||' - '||top_title module
from ( select module
         , mde.top_title
         , role
         , row_number() over (partition by
                                    order by
                                   ) rn
         from ci_role_module_accesses rms
         , ci_roles rle
         , ci_general_modules mde
         where = rms.role_reference
         and rms.general_module_reference =

The result looks something like:

ROLE                           MODULE
------------------------------ ------------------------------------------------------------
AGS$ALG                        AGS0000F - PKS Startup Scherm
                               AGS2910R - Overzicht catalogi op postniveau
                               AGS2912R - Overzicht catalogi op elementniveau
                               AGS2920R - Overzicht variabelen.
                               AGS2930R - Overzicht oppervlaktetypes
AGS$BHR                        AGS00010F - Onderhouden statische Parameters
                               AGS1100F - Onderhouden Stamgegevens autorisatie
                               AGS1200F - Onderhouden gebruikersgroepen en privileges
                               AGS1201L - Privileges
                               AGS1300F - Onderhouden gebruikers en gebruikersgroepen
                               AGS1410R - Overzicht rechten per gebruikersgroep
                               AGS1420R - Overzicht gebruikers
                               AGS1430R - Overzicht gebruikersgroepen
                               AGS1440R - Overzicht privileges.
                               AGS2500F - Onderhouden subsidieregelingen.
                               AGS2550F - Onderhouden variantnummers
                               AGS2600F - Onderhouden variabelen.
                               AGS2700F - Onderhouden oppervlaktetypes
                               AGS2800F - Onderhouden eenheden
                               AGS2900F - Onderhouden gebieden
                               AGS3501L - Overzicht gebruikers
AGS$BWGBHR                     AGS5400F - Wijzigen eigenaar machtiging
AGS$BWGOND                     AGS5100F - Onderhouden machtigingen
                               AGS5200F - Verwijderen machtigingen
                               AGS5300F - Onderhouden gebruikerstoegang machtigingen
                               AGS5800F - Geforceerde statuswijziging machtiging
AGS$BWGRPN                     AGS5100Q - Raadplegen machtigingen

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
0 %
0 %
0 %
0 %
0 %
0 %
Next Post

Oracle Designer - Keyword Expansion upon Check Out

A little while ago I wrote the article Oracle Designer Check In – synchronize P_VERSION argument on this weblog. In it, I discuss the desirability of having Oracle Designer apply the version label of a Module that it receives during Check In to the Module Argument p_revision. This parameter is […]
%d bloggers like this: