Drole (drole.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script lists all database roles and indicates whether they require a password for activation. Essential for understanding role-based security implementation and identifying password-protected roles.

The Script

col role format a30
col password_required format a30


select role
      ,password_required
  from dba_roles
 where role like nvl(upper('&role'),'%')
order by role
;

Parameters

The script prompts for:

• &Role - name pattern to filter (use % for all)

Usage

-- Basic usage
@drole.sql

-- When prompted, enter:
-- role: Role name pattern to filter (use % for all)

Sample Output

ROLE                           PASSWORD_REQUIRED
------------------------------ ------------------------------
ADM_PARALLEL_EXECUTE_TASK      NO
APEX_ADMINISTRATOR_ROLE        NO
CONNECT                        NO
DATAPUMP_EXP_FULL_DATABASE     NO
DATAPUMP_IMP_FULL_DATABASE     NO
DBA                            NO
EXP_FULL_DATABASE              NO
HR_ADMIN                       NO
HR_READ_ONLY                   NO
IMP_FULL_DATABASE              NO
RESOURCE                       NO
SECURE_APP_ROLE                YES
SELECT_CATALOG_ROLE            NO
SYSBACKUP                      NO
SYSDBA                         NO
SYSDG                          NO
SYSKM                          NO
SYSOPER                        NO
SYSRAC                         NO