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.

TRIGGER CODE:

 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

Popular posts from this blog

Oracle Database Client(12.1.0.2.0) Installation for Microsoft Windows(x64)

Image
Step1) Download Oracle Client 12C from below link http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-2297732.html Oracle Database Client (12.1.0.2.0) for Microsoft Windows (x64) winx64_12102_client.zip  (64-bit) (925,039,944 bytes) Oracle Database Client (12.1.0.2.0) for Microsoft Windows (32-bit) winnt_12102_client32.zip  (32-bit) (902,922,170 bytes)  chose the one based on your laptop Step2) Extract the downloaded file Oracle 12c 64 bit client has now been extracted. Double click ‘setup.exe’. This may test your patience, let it work! (Sometimes a minute or less can seem like an eternity). Don’t touch anything! Administrator: Enables applications to connect to an Oracle database on the local system or on a remote system. It provides tools that let y...
Read more

configuring goldengate

configuring goldengate Pre req:  Install Goldengate software on both the servers golden gate home: /u02/app/goldengate Update bash_profile on both Source and Target side [oracle@localhost ~]$ vi .bash_profile if [ -f ~/.bashrc ]; then         . ~/.bashrc fi # User specific environment and startup programs export ORACLE_SID=test export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 export GRID_HOME=/u01/app/oracle/product/12.1.0/grid export LD_LIBRARY_PATH=$ORACLE_HOME/lib export PATH=$ORACLE_HOME/bin:$PATH: export PATH=$GRID_HOME/bin:$PATH: export PATH=$ORACLE_HOME/OPatch:$PATH: PATH=$PATH:$HOME/bin export PATH export GGS_HOME=/u02/app/goldengate alias ggn='cd ${GGS_HOME}' alias ggsci='cd ${GGS_HOME}/ggsci' export LD_LIBRARY_PATH=$GGS_HOME:$ORACLE_HOME/lib:/lib:/usr/lib ".bash_profile" 25L, 684C                                    ...
Read more

Configuring oracle enterprise manager express 12c

1.TO configure EM express and connect to the database using EM database Express the Listener should be up an running. [oracle@pavanorcl101 ~]$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 17-OCT-2017 15:17:47 Copyright (c) 1991, 2014, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=pavanorcl101.mydomain.com)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date                17-OCT-2017 15:17:22 Uptime                    0 days 0 hr. 0 min. 25 sec Trace Level               off Security                ...
Read more