LOGON success action is audited by unified audit policy ORA_LOGON_FAILURES after executing NOAUDIT & AUDIT command (Doc ID 2305054.1)

 


To BottomTo Bottom

In this Document

Symptoms
Changes
Cause
Solution


APPLIES TO:

Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.2.0.1 [Release 12.1 to 12.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.

SYMPTOMS

In unified_audit_trail, both LOGON success and failure actions are audited by unified audit policy ORA_LOGON_FAILURES
In following 2 audit records, DBUSERNAME:CCFSP logon successed, and DBUSERNAME:CCFSP111 logon failed.

SQL> select USERHOST,DBUSERNAME,AUDIT_TYPE,ACTION_NAME,CLIENT_PROGRAM_NAME,EVENT_TIMESTAMP,UNIFIED_AUDIT_POLICIES from unified_audit_trail;

USERHOST DBUSERNAME AUDIT_TYPE ACTION_NAME CLIENT_PROGRAM_NAME EVENT_TIMESTAMP
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------
UNIFIED_AUDIT_POLICIES
------------------------------
WORKGROUP\TACOM CCFSP Standard LOGON sjSPX_CmdMgr.exe 17-07-20 10:13:55.550000
ORA_LOGON_FAILURES

WORKGROUP\TACOM CCFSP111 Standard LOGON sjSPX_CmdMgr.exe 17-07-20 10:13:55.550000
ORA_LOGON_FAILURES

 

 

 

CHANGES

 Customer executed following statements to disable and enable audit policy ORA_LOGON_FAILURES.

noaudit policy ORA_LOGON_FAILURES;
audit policy ORA_LOGON_FAILURES;

 

CAUSE

The command of audit policy is lack of WHENEVER NOT SUCCESSFUL option, therefore audit policy of ORA_LOGON_FAILURES will audit both LOGON success and LOGON failure.
 

SOLUTION

In order to set ORA_LOGON_FAILURES to audit LOGON failure only, it is necessary to disable and enable ORA_LOGON_FAILURES with WHENEVER NOT SUCCESSFUL option.

SQL> conn / as sysdba
Connected.
SQL> noaudit policy ORA_LOGON_FAILURES;

Noaudit succeeded.

SQL> audit policy ORA_LOGON_FAILURES WHENEVER NOT SUCCESSFUL;

Audit succeeded.

 

Check the SUCCESS and FAILURE columns in AUDIT_UNIFIED_ENABLED_POLICIES:

SQL> col USER_NAME for a10
SQL> col POLICY_NAME for a20
SQL> col SUCCESS for a8
SQL> col FAILURE for a8
SQL> set lines 200
SQL> select * from AUDIT_UNIFIED_ENABLED_POLICIES;

USER_NAME POLICY_NAME ENABLED_ SUCCESS FAILURE
---------- -------------------- -------- -------- --------
ALL USERS ORA_SECURECONFIG BY YES YES
ALL USERS ORA_LOGON_FAILURES BY NO YES

 

Check the audit record in unified_audit_trail:

SQL> col DBUSERNAME for a10
SQL> col ACTION_NAME for a10
SQL> col UNIFIED_AUDIT_POLICIES for a20
SQL> col RETURN_CODE for 9999999
SQL> select DBUSERNAME,ACTION_NAME,UNIFIED_AUDIT_POLICIES,RETURN_CODE from unified_audit_trail where UNIFIED_AUDIT_POLICIES = 'ORA_LOGON_FAILURES';

DBUSERNAME ACTION_NAM UNIFIED_AUDIT_POLICI RETURN_CODE
---------- ---------- -------------------- -----------
NOUSER LOGON ORA_LOGON_FAILURES 1017 * <<<----LOGON failure action

 

Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more