Oracle 11g: Security Tips
Assuming that you have an application and you want that only the server
running your application access to your database. I
use this trigger to prevent all other access from other servers to my
database.
The following trigger will kill all the sessions if their ip addresses
are not ip address of that server (For example: 133.33.333.33) then will
write all the users' information such as user's environment, hostname,
ip address and date-time to a table called ck_security_table.
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') <> '133.33.333.33'
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;
/
Good Luck!
|