Oracle Database Logon Trigger
CREATING SCHEMA and OBJECTS required to capture USER successful and failed logins:
Schema Creation:
create user DB_SEC identified by XXXXXX;
grant resource to db_sec;
grant unlimited tablespace to db_sec;
Table to Capture User's SUCCESSFUL login info:
create table DB_SEC.LAST_LOGIN_INFO (
DBUSER Varchar2(30),
OSUSER Varchar2(30),
MACHINE Varchar2(64),
LAST_LOGIN DATE);
Table to Capture User's FAILED login info:
Create table DB_SEC.FAILED_LOGIN_INFO (
DBUSER Varchar2(30),
OSUSER Varchar2(30),
MACHINE Varchar2(64),
FAILED_TIME DATE);
Whitelisting Table to allow User login:
Create table DB_SEC.USER_AUTH (
DBUSER Varchar2(30),
OSUSER Varchar2(30),
MACHINE Varchar2(64));
DB_SEC.USER_AUTH: This table holds the user's info as below and the trigger will authenticate against this table to allow the user to login.
SQL> select * from db_sec.user_auth;
DBUSER OSUSER MACHINE
------------------------------ ------------------------------ ------------------------------------
PAVAN ORACLE localhost.localdomain
PAVAN pavan Anjanis-MacBook-Pro.local
If the user is authenticated successfully then successful login info will be captured in DB_SEC.LAST_LOGIN_INFO and If the authentication fails then unsuccessful login info will be stored in DB_SEC.FAILED_LOGIN_INFO
NOTE: The below trigger allows the users with DBA Privilege to login without authentication against the DB_SEC.USER_AUTH table and also the login info will not be captured.
create or replace trigger GATE_KEEPER
after logon
on database
declare
V_DBUSER Varchar2(30);
V_OSUSER Varchar2(30);
V_MACHINE varchar2(64);
V_DATE DATE;
V_COUNT number :=0;
V_IS_DBA Varchar2(6);
begin
select
UPPER(SYS_CONTEXT('USERENV','SESSION_USER')),
UPPER(SYS_CONTEXT('USERENV','OS_USER')),
UPPER(SYS_CONTEXT('USERENV','HOST')),
UPPER(SYS_CONTEXT('USERENV','ISDBA')),
SYSDATE into V_DBUSER, V_OSUSER, V_MACHINE, V_IS_DBA,V_DATE from dual;
select count(0) into V_COUNT
from DB_SEC.USER_AUTH
where
UPPER(SYS_CONTEXT('USERENV','SESSION_USER'))= UPPER(DBUSER) and
UPPER(SYS_CONTEXT('USERENV','OS_USER')) = UPPER(OSUSER) and
UPPER(SYS_CONTEXT('USERENV','HOST')) = UPPER(MACHINE);
IF(V_IS_DBA='TRUE' or V_DBUSER='SYS'or V_DBUSER='SYSTEM'or V_DBUSER='SYSBACKUP') then
NULL;
ELSE
IF (V_COUNT = 0 and V_IS_DBA = 'FALSE') then
Insert into DB_SEC.FAILED_LOGIN_INFO values(V_DBUSER, V_OSUSER, V_MACHINE, V_DATE);
Commit;
RAISE_APPLICATION_ERROR( -20000, v_dbuser || ': you are NOT allowed to connect' ||' Please Contact Database administrator');
ELSE IF (V_COUNT >= 1 and V_IS_DBA = 'FALSE') then
Insert into DB_SEC.LAST_LOGIN_INFO values(V_DBUSER, V_OSUSER, V_MACHINE, V_DATE);
Commit;
END IF;
END IF;
END IF;
END;
/
Testing the Trigger:
Now let's try to Test by logging in with users PAVAN(Whitelisted) and RAVI(NOT-Whitelisted). So the trigger should allow PAVAN to login and do not allow RAVI to login.
Whitelist table's data:
SQL> select * from db_sec.user_auth;
DBUSER OSUSER MACHINE
------------------------------ ------------------------------ ------------------------------------
PAVAN ORACLE localhost.localdomain
PAVAN pavan Anjanis-MacBook-Pro.local
1. Login with user "PAVAN":
[oracle@localhost ~]$ sqlplus
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 11 16:17:18 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter user-name: pavan
Enter password:
Last Successful login time: Wed Jun 10 2020 22:43:15 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
Since login was successful, it should be captured in the table DB_SEC.LAST_LOGIN_INFO
SQL> select * from db_sec.last_login_info order by 4 desc;
DBUSER OSUSER MACHINE LAST_LOGIN
------------------------------ ------------------------------ ---------------------------------------------------------------- -------------------
PAVAN ORACLE LOCALHOST.LOCALDOMAIN 2020/06/11 16:17:21
2. Login with user "RAVI":
[oracle@localhost ~]$ sqlplus
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 11 16:28:48 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter user-name: ravi
Enter password:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: RAVI: you are NOT allowed to connect Please Contact Database
administrator
ORA-06512: at line 37
Since login was unsuccessful, it should be captured in the table DB_SEC.FAILED_LOGIN_INFO
SQL> select * from db_sec.failed_login_info order by 4 desc;
DBUSER OSUSER MACHINE FAILED_TIME
------------------------------ ------------------------------ ---------------------------------------------------------------- -------------------
RAVI ORACLE LOCALHOST.LOCALDOMAIN 2020/06/11 16:28:53
Comments
Post a Comment