denrepo

OracleSecurity & users

Roles granted to each user, by how they log in

Every role granted to each account that isn't Oracle-maintained, grouped by authentication type: PASSWORD, GLOBAL (directory users, such as through OID, Active Directory or Entra ID) and EXTERNAL. Users with no roles show a blank role. Auditors often ask for the GLOBAL list on its own.

12c+

Not yet verified. How scripts are tested

ora-user-roles.sql 
1CLEAR COLUMNS2CLEAR BREAKS3SET LINESIZE 160 PAGESIZE 300 TRIMOUT ON TAB OFF4COLUMN authentication HEADING "AUTHENTICATION" FORMAT A145COLUMN username       FORMAT A306COLUMN account_status HEADING "STATUS" FORMAT A20 TRUNCATE7COLUMN granted_role   HEADING "ROLE" FORMAT A308COLUMN admin          HEADING "ADMIN" FORMAT A59COLUMN dflt           HEADING "DEFAULT" FORMAT A710COLUMN users          HEADING "USERS" FORMAT 99,99011 12SELECT authentication_type AS authentication, COUNT(*) AS users13FROM dba_users14WHERE oracle_maintained = 'N'15GROUP BY authentication_type16ORDER BY 1;17 18BREAK ON authentication SKIP 1 ON username ON account_status19 20SELECT u.authentication_type AS authentication,21       u.username,22       u.account_status,23       r.granted_role,24       r.admin_option AS admin,25       r.default_role AS dflt26FROM dba_users u27LEFT JOIN dba_role_privs r ON r.grantee = u.username28WHERE u.oracle_maintained = 'N'29ORDER BY u.authentication_type, u.username, r.granted_role;30 31CLEAR BREAKS

Save it as ora-user-roles.sql and run it with SQL> @ora-user-roles.

Open in denrepo

Part of these runbooks

More Oracle scripts: Security & users