Auditing and Restricting User Connections

This tip was published on Oracle website in 2003. It looks like it is archived now by oracle but some other website has copied it to their own websites. I have now copied it to my blog.

Tip of the Week

Tip for Week of June 14, 2004

This tip comes from Ajwat Ul-Hasan, Database Administrator/System Analyst, The Carphonewarehouse in London, United Kingdom.



In Oracle9i, this script can restrict users logging into the database. In one table you provide information about which users are allowed to log into the database. You can modify this script to allow or block a user with respect to application, ip address, username, osuser, terminal, etc. It is a very easy and efficient way of implementing security in database.







create table valid_users (

valid_ora VARCHAR2(30),

valid_os varchar2(30)

);

and another table

CREATE TABLE USER_AUDIT

(

SID NUMBER NULL,

SERIAL# NUMBER NULL,

TIMESTAMP DATE NULL,

USERNAME VARCHAR2(30) NULL,

OSUSERID VARCHAR2(30) NULL,

MACHINENAME VARCHAR2(64) NULL,

PROGRAM VARCHAR2(48) NULL

)





And then create this trigger:







CREATE OR REPLACE TRIGGER test_audit

AFTER LOGON ON database

DECLARE

machinename VARCHAR2(64);

osuserid VARCHAR2(30);

ora_username VARCHAR2(30) DEFAULT NULL;

os_username VARCHAR2(30);

v_sid NUMBER;

v_serial NUMBER;

v_program VARCHAR2(48);

v_numuser NUMBER;

CURSOR c1 IS

SELECT sid, serial#, osuser, machine, program

FROM v$session

WHERE audsid = userenv('sessionid');

BEGIN

OPEN c1;

FETCH c1 INTO v_sid, v_serial, osuserid, machinename,

v_program;

CLOSE c1;

SELECT count(*)

INTO v_numuser

FROM valid_users

WHERE valid_ora = user;



IF v_numuser > 0 then

INSERT INTO user_audit

VALUES (v_sid, v_serial, sysdate,

user, osuserid, machinename,v_program);

ELSE

INSERT INTO user_audit

VALUES (v_sid, v_serial, ! sysdate,

user, osuserid, machinename,'IN ELSE');

raise_application_error(-20001,'You are not allowed to connect

to the database');

END IF;

END;

/





In the valid_user table you have to insert information about valid users, otherwise no user can log in to the database. You can generate information about user connection by using information in user_audit table.