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;
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;
/
Or
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.
Login as sys and create
ck_security procedure:
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 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)');