iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Oracle 11g New Features

Online Oracle Training

 

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!

 

Google
 
Web web site