OracleSecurity & users

Failed logins in the last 24 hours

From the unified audit trail, which records failed logons by default through the ORA_LOGON_FAILURES policy. Return code 1017 is a wrong password; 28000 is a locked account.

Not yet verified. How scripts are tested

1CLEAR COLUMNS2SET LINESIZE 160 PAGESIZE 200 TRIMOUT ON TAB OFF3COLUMN at_time     HEADING "TIME"    FORMAT A194COLUMN dbusername  HEADING "DB USER" FORMAT A205COLUMN os_username HEADING "OS USER" FORMAT A206COLUMN userhost    HEADING "HOST"    FORMAT A30 TRUNCATE7COLUMN return_code HEADING "ORA-"    FORMAT 999998 9SELECT TO_CHAR(event_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS at_time,10       dbusername, os_username, userhost, return_code11FROM unified_audit_trail12WHERE action_name = 'LOGON'13  AND return_code <> 014  AND event_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY15ORDER BY event_timestamp DESC;

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

Open in denrepo

Helps with

• ORA-01017: invalid username/password; logon denied
• ORA-28000: the account is locked

Part of these runbooks

• Users can't connect

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.
• Who has DBA, SYSDBA and powerful system privilegesNon-Oracle accounts and roles holding the DBA role or high-risk ANY privileges, then everyone in the password file. Worth reviewing every audit cycle.
• 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…