Tuesday, August 7, 2012

restrict USER any software other than SQLPlus to connect to Oracle database


/*restrict any software other than SQL*Plus to connect to Oracle database. */

/* Oracle Job Scheduler  DBMS_JOB */
create or replace procedure p_jobscheduler is
begin
/* Submit a job to DBMS_JOB  as below */
/* jobno is a  bind variable you need to declare before running this block*/
/* SYSDATE + 1/18000 makes the job run after every 5 seconds */
    DBMS_JOB.SUBMIT (:jobno,'P_SCHEDULE;', SYSDATE, 'SYSDATE+1/18000');
end;
/
/* Procedure should run in INTERNAL/SYS/SYSTEM in order to function*/
CREATE OR REPLACE PROCEDURE P_SCHEDULE IS
    CURSOR C_SESSION IS  SELECT SID,SERIAL#,USERNAME,MODULE
                FROM V$SESSION
                WHERE USERNAME IS NOT NULL
                AND     USERNAME NOT IN ('SYS','SYSTEM','LDBO');
/* Users SYS,SYSTEM,INTERNAL have been allowed to connect via any software */
/* Retrieve all the relevant columns from v$session*/
BEGIN
     FOR C_KS IN C_SESSION LOOP
/* Cursor For loop */          
          IF NOT C_KS.MODULE = 'SQL*Plus' THEN
            dbms_output.put_line(C_KS.MODULE);
/* You should be in Oracle 8i and above to make this statement work. */          
            EXECUTE IMMEDIATE ('ALTER SYSTEM KILL SESSION ''' || C_KS.SID || ',' || C_KS.SERIAL# ||
'''');
        END IF;
     END LOOP;
EXCEPTION
/* Oops..something went wrong  !! Have a look ... turn on your serveroutput */          
     WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('ERROR OCCURRED' || SQLERRM);
END;
/

No comments:

Post a Comment

Followers