User Logon Validation Trigger

User Logon Validation Trigger This tip comes from Babu Rangasamy, a Sr. Oracle Database Consultant, in Harrisburg, PA. This trigger verifies whether a user logging into the database is (pre)authorized to connect to the database using the approved client machine, client software, etc. -- connect as sys/***** CREATE TABLE authorize_users_tab( username VARCHAR2(30), osuser VARCHAR2(30), program VARCHAR2(64), machine VARCHAR2(64) ); ALTER TABLE authorize_users_tab ADD CONSTRAINT user_prog_mach_uk UNIQUE (username,osuser,program,machine); -----

Insert appropiate values into this table, so only the entered users will be permitted to connect to that database, query V$SESSION for correct info, data entered into the table are case sensitive.

insert into authorize_users_tab values ('SCOTT','DOMAIN\OSUSER','SQLPLUSW.EXE','DOMAIN\MACHINE'); insert into authorize_users_tab values ('TESTUSER','DOMAIN\OSUSER','SQLPLUSW.EXE','DOMAIN\MACHINE'); commit; CREATE OR REPLACE TRIGGER authorize_users_trig AFTER LOGON ON DATABASE ------------

Description: This trigger helps database administrators to control users logging into the database using various client tools and machines. List of USERNAME, OSUSER, PROGRAM and MACHINE entered in table AUTHORIZE_USERS_TAB will only be permitted to login into the database. Ref Oracle Bug No.2628258 and Doc No.2628258.8 in Metalink for using RTRIM(machine,CHR(0)) in the below code. Caveat: When a user tries to connect via SQL*Plus and if the trigger refuses connection, info about that session is shown as INACTIVE in V$SESSION, unless that user closes the SQL*Plus window/session.

DECLARE p_count NUMBER(1); BEGIN SELECT COUNT(*) INTO p_count FROM sys.authorize_users_tab WHERE UPPER(username||osuser||program||RTRIM(machine,CHR(0))) IN ( SELECT UPPER(username||osuser||program||RTRIM(machine,CHR(0))) FROM v$session ! WHERE au dsid = USERENV('sessionid') AND logon_time = ( SELECT MAX(logon_time) FROM v$session WHERE audsid = USERENV('sessionid')));

IF p_count = 0 THEN RAISE_APPLICATION_ERROR(-20001, '---> Sorry, Access Denied <---'); END IF; END ; /