iSelfSchooling - I have an specific user that I want to run from an specific server. How can I check its session and if it is opening some other session so I kill its session.

I have an specific user that I want to run from an specific server only. How can I check its session and if it is opening some other session from different machine so I can stop its session?

Do the following:

Using login trigger:

CREATE OR REPLACE TRIGGER ck_security_trigger

AFTER LOGON ON DATABASE

DECLARE

cur integer;

rc integer;

v_1 number;

v_2 number;

BEGIN

IF sys_context('USERENV','SESSION_USER') = 'LISTEST' THEN

IF sys_context('USERENV','IP_ADDRESS') <> '156.33.200.91'

OR sys_context('USERENV','IP_ADDRESS') IS NULL THEN

v_1 := '';

v_2 := '';

INSERT INTO ck_security_table

VALUES

('Security Violation: '

|| sys_context('USERENV','SESSION_USER')

|| ' accessed from '

|| sys_context('USERENV','HOST')

|| '('

|| sys_context('USERENV','IP_ADDRESS')

|| ') at '

|| TO_CHAR(sysdate(),'MON-DD-YYYY HH24:MI:SS')

);

COMMIT;

cur := DBMS_SQL.OPEN_CURSOR;

-- trick the oracle database to disconnect the user.

DBMS_SQL.PARSE(cur,

'ALTER SYSTEM KILL SESSION '''

|| v_1

|| ','

|| v_2

|| '''', DBMS_SQL.NATIVE);

rc := DBMS_SQL.EXECUTE(cur);

DBMS_SQL.CLOSE_CURSOR(cur);

END IF;

EXCEPTION

WHEN OTHERS THEN

RAISE_APPLICATION_ERROR (

num=> -20106,

msg=> '*** Security violation ****

ORA-20107: *** Security violation was occurred.

ORA-20108: *** Security Violation: Your attempt was recorded.

ORA-20109: *** Security Violation: Please contact your system administrator.');

END;

Using PL/SQL

This procedure check the user if it is not from specific target server, it will kill the session and then write a violation message to the ck_security_table table.

First create a table to store violation event and procedure to check its username and

terminal (for example: assuming the uername is 'LISTEST' and its terminal name is

'JK1157'). If you see it on the different terminal just kill it and report a violation

to ck_security_table.

CREATE TABLE ck_security_table

(violation_msg varchar2(500));

CREATE OR REPLACE PROCEDURE ck_security AS

cur integer;

rc integer;

v_sid number;

v_serial number;

v_username varchar2(100);

v_machine varchar2(100);

CURSOR c_user_security IS

SELECT sid, serial#, username, terminal, machine, status

FROM v$session;

BEGIN

FOR this IN c_user_security LOOP

IF this.username = 'LISTEST' THEN

IF this.terminal = 'JK1157' THEN

null;

ELSIF this.status <> 'KILLED' THEN

v_sid := this.sid;

v_serial := this.serial#;

cur := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(cur,

'ALTER SYSTEM KILL SESSION '''

|| v_sid

|| ','

|| v_serial

|| '''', DBMS_SQL.NATIVE);

rc := DBMS_SQL.EXECUTE(cur);

DBMS_SQL.CLOSE_CURSOR(cur);

INSERT INTO ck_security_table

VALUES

('Security Violation: '

|| this.username

|| ' accessed from '

|| this.machine

|| '('

|| this.terminal

|| ') on '

|| TO_CHAR(sysdate(),'MON-DD-YYYY HH24:MI:SS'));

END IF;

END LOOP;

END ck_security;

Once you compile the procedure successfully, create a job and submit is every two

second. That will do the job.

-- Define “v_jobno” as a binding variable:

VARIABLE v_jobno NUMBER

-- Submit the ck_security procedure for every two second.

EXECUTE dbms_job.submit (:v_jobno, 'ck_security;', sysdate, 'sysdate + (2/86400)');

-- Print the job number

PRINT v_jobno

-- Run the job.

-- Make sure that the JOB_QUEUE_PROCESSES parameter is not zero. The zero value means

stop all job queues.

EXECUTE dbms_job.run (:v_jobno);

-- Remove the job.

EXECUTE dbms_job.remove (:v_jobno);

-- To change the inteval

dbms_job.chyange(:v_jobno, NULL, NULL, 'SYSDATE + (1/86400)');