OracleSecurity & users

Who has DBA, SYSDBA and powerful system privileges

Non-Oracle accounts and roles holding the DBA role or high-risk ANY privileges, then everyone in the password file. Worth reviewing every audit cycle.

12c+

Not yet verified. How scripts are tested

ora-powerful.sql

1CLEAR COLUMNS2SET LINESIZE 140 PAGESIZE 200 TRIMOUT ON TAB OFF3COLUMN grantee      FORMAT A254COLUMN privilege    FORMAT A255COLUMN kind         FORMAT A126COLUMN admin_option HEADING "ADMIN" FORMAT A57COLUMN username     FORMAT A258COLUMN sysdba       FORMAT A69COLUMN sysoper      FORMAT A710COLUMN sysbackup    FORMAT A911COLUMN sysdg        FORMAT A512COLUMN syskm        FORMAT A513 14SELECT grantee, granted_role AS privilege, 'ROLE' AS kind, admin_option15FROM dba_role_privs16WHERE granted_role = 'DBA'17  AND grantee NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y')18UNION ALL19SELECT grantee, privilege, 'SYSTEM PRIV', admin_option20FROM dba_sys_privs21WHERE privilege IN ('ALTER SYSTEM', 'ALTER USER', 'BECOME USER', 'GRANT ANY PRIVILEGE',22                    'GRANT ANY ROLE', 'DROP ANY TABLE', 'SELECT ANY TABLE',23                    'CREATE ANY PROCEDURE', 'EXECUTE ANY PROCEDURE')24  AND grantee NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y')25  AND grantee NOT IN (SELECT role FROM dba_roles WHERE oracle_maintained = 'Y')26ORDER BY 1, 2;27 28SELECT username, sysdba, sysoper, sysbackup, sysdg, syskm29FROM v$pwfile_users30ORDER BY username;

CLEAR COLUMNS
SET LINESIZE 140 PAGESIZE 200 TRIMOUT ON TAB OFF
COLUMN grantee      FORMAT A25
COLUMN privilege    FORMAT A25
COLUMN kind         FORMAT A12
COLUMN admin_option HEADING "ADMIN" FORMAT A5
COLUMN username     FORMAT A25
COLUMN sysdba       FORMAT A6
COLUMN sysoper      FORMAT A7
COLUMN sysbackup    FORMAT A9
COLUMN sysdg        FORMAT A5
COLUMN syskm        FORMAT A5

SELECT grantee, granted_role AS privilege, 'ROLE' AS kind, admin_option
FROM dba_role_privs
WHERE granted_role = 'DBA'
  AND grantee NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y')
UNION ALL
SELECT grantee, privilege, 'SYSTEM PRIV', admin_option
FROM dba_sys_privs
WHERE privilege IN ('ALTER SYSTEM', 'ALTER USER', 'BECOME USER', 'GRANT ANY PRIVILEGE',
                    'GRANT ANY ROLE', 'DROP ANY TABLE', 'SELECT ANY TABLE',
                    'CREATE ANY PROCEDURE', 'EXECUTE ANY PROCEDURE')
  AND grantee NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y')
  AND grantee NOT IN (SELECT role FROM dba_roles WHERE oracle_maintained = 'Y')
ORDER BY 1, 2;

SELECT username, sysdba, sysoper, sysbackup, sysdg, syskm
FROM v$pwfile_users
ORDER BY username;

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

Open in denrepo

Helps with

ORA-01031: insufficient privileges

More Oracle scripts: Security & users

Accounts locked, expired or expiring soonApplication accounts that aren't OPEN, or whose passwords expire in the next 14 days. Catch service accounts before they break an application.
CMU step 1: prepare Active DirectoryDone once by an AD administrator before any database work: create the service account the database binds with, install Oracle's password filter on…
CMU step 2: export the AD root certificateThe database talks to AD over LDAPS, so its wallet must trust the certificate authority that issued the domain controllers' certificates. Export that…
CMU step 3: create the walletBuilds the auto-login wallet the database reads at login: the service account's user name, DN and password, plus the AD root certificate. With PDBs,…
CMU step 4: create dsi.oraTells the database which domain controllers to use. Put it in the same folder as the wallet from step 3. Use fully qualified host names, and list at…
CMU step 5: point each PDB at its wallet (CMU_WALLET)Creates a directory object for the wallet folder from step 3 and sets the CMU_WALLET database property in the PDB, so CMU reads that PDB's wallet and…