Oracle Designer Report – SQL Script for Roles vs. Modules


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 Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

Comments are closed.