Mir Sayeed Hassan – Oracle Blog

How to create the trigger for failed & success login attempt in Oracle Database

Note: The below given trigger is created to attempt the success and failed login attempt of the all the user in Oracle database., Data will be recorded in the table as well as at the OS Level (directory loc).

Verify the audit config in DB

SQL> show parameter audit_trail

NAME            TYPE     VALUE
-------------------------------
audit_trail     string     DB

create tablespace for dedicated to audit user (Optional)

SQL> create tablespace USER_AUDIT_TAB datafile size 10m autoextend on maxsize unlimited;
Tablespace created.

Create a user and assign the default tablespace

SQL> create user audit_user identified by auditu1 default tablespace user_audit_tab quota unlimited on user_audit_tab temporary tablespace temp account unlock profile default;
User created.

SQL> grant create session,create trigger to audit_user;
Grant succeeded.

SQL> grant ADMINISTER DATABASE TRIGGER to audit_user;
Grant succeeded.

Login to the User & verify

SQL> connect audit_user/auditu1;
Connected.

Create a directory at OS Level

[oracle@test-db u01]$ mkdir AUDITLOGS

[oracle@test-db u01]$ cd AUDITLOGS

[oracle@test-db AUDITLOGS]$ pwd
/u01/AUDITLOGS

Create a directory at DB Level

[oracle@test-db AUDITLOGS]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 18 07:29:59 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create directory AUDITLOGS as '/u01/AUDITLOGS/';
Directory created.

SQL> grant read, write on directory AUDITLOGS to sys;
Grant succeeded.

Create a table to audit

SQL> create table audit_logon_succ_fail_trg(username varchar2(20),userhost varchar2(128),log_date timestamp,action varchar2(40));
Table Created.

Create a trigger to Audit logon user in Database

CREATE OR REPLACE TRIGGER AUDIT_LOGON_USER_SESSION
AFTER LOGON ON DATABASE WHEN (USER NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP','DBADMIN','MGMT_VIEW'))
declare
out_file utl_file.file_type;
filestr varchar2(4000);
BEGIN
insert into audit_logon_succ_fail_trg(username, userhost,log_date, action) values(user,sys_context('USERENV','HOST'),sysdate, 'logging on');
-- Write audit to file
out_file := utl_file.fopen(LOCATION =>'AUDITLOGS', FILENAME => 'AUDIT_SUCCESS_FAILED_LOGIN.aud', OPEN_MODE => 'A');
filestr := 'DATE=' || to_char(sysdate,'YYYY/MM/DD-HH24:MI:SS') || '||' || 'DB_USER=' || sys_context('USERENV','SESSION_USER') || '||' || 'HOST=' || sys_context('USERENV','HOST') || 'ACTION=LOGINING ON';
utl_file.put_line(FILE => out_file, BUFFER => filestr , AUTOFLUSH => TRUE);
UTL_FILE.FCLOSE(FILE => out_file);
END;
/

Create a trigger to audit failed login attempt

create or replace TRIGGER AUDIT_LOGIN_FAILED_TRIGGER AFTER SERVERERROR ON DATABASE
DECLARE
out_file utl_file.file_type;
filestr varchar2(4000);
BEGIN
-- ORA-1017: invalid username/password; logon denied
IF (IS_SERVERERROR(1017)) THEN
insert into audit_logon_succ_fail_trg(username, userhost,log_date, action) values(sys_context('USERENV','AUTHENTICATED_IDENTITY'),sys_context('USERENV','HOST'),sysdate, 'failed');
-- Write audit to file
out_file := utl_file.fopen(LOCATION =>'AUDITLOGS', FILENAME => 'AUDIT_SUCCESS_FAILED_LOGIN.aud', OPEN_MODE => 'A');
filestr := 'DATE=' || to_char(sysdate,'YYYY/MM/DD-HH24:MI:SS') || '||' || 'DB_USER=' || sys_context('USERENV','AUTHENTICATED_IDENTITY') || '||' || 'HOST=' || sys_context('USERENV','HOST') || 'ACTION=FAILED';
utl_file.put_line(FILE => out_file, BUFFER => filestr , AUTOFLUSH => TRUE);
UTL_FILE.FCLOSE(FILE => out_file);
END IF;
END;
/

Describe table

SQL> desc audit_logon_succ_fail_trg;
Name        Null?           Type
-----------------------------------------
USERNAME                   VARCHAR2(20)
USERHOST                   VARCHAR2(128)
LOG_DATE                   TIMESTAMP(6)
ACTION                     VARCHAR2(40)

Do some test & verify

SQL> connect hassan/hassan
Connected.

SQL> connect hassan/asjklflkag
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

Verify at DB Level

SQL> conn / as sysdba
Connected.

SQL> set linesize 300
SQL> select * from audit_logon_succ_fail_trg;

USERNAME      USERHOST           LOG_DATE                         ACTION 
-------------------- --------------------------------------------------------
AUDIT_USER     test-db        18-JAN-23 08.51.52.000000 AM          failed 
HASSAN         test-db        18-JAN-23 08.42.24.000000 AM         logging on 
HASSAN         test-db        18-JAN-23 08.50.14.000000 AM          failed 
HASSAN         test-db        18-JAN-23 09.47.56.000000 AM         logging on

Verify at OS Level

[oracle@test-db AUDITLOGS]$ cat AUDIT_SUCCESS_FAILED_LOGIN.aud

DATE=2023/01/18-08:51:14  ||  DB_USER=HASSAN      ||  HOST=test-db ACTION=FAILED
DATE=2023/01/18-08:42:24  ||  DB_USER=HASSAN      ||  HOST=test-db ACTION=LOGINING ON
DATE=2023/01/18-08.50.14  ||  DB_USER=AUDIT_USER  ||  HOST=test-db ACTION=FAILED
DATE=2023/01/18-09.47.56  ||  DB_USER=HASSAN      ||  HOST=test-db ACTION=LOGINING ON

=====Hence tested & verified in our test env=====