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
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
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
('Security Violation: '
|| ' accessed from '
|| ') at '
|| TO_CHAR(sysdate(),'MON-DD-YYYY HH24:MI:SS')
cur := DBMS_SQL.OPEN_CURSOR;
-- trick the oracle database to disconnect the user.
'ALTER SYSTEM KILL SESSION '''
|| '''', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
WHEN OTHERS THEN
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