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 ------------------------------ -----------------------