Ask the Expert

Restricting users from connecting through SQL*Plus/Toad

I want to restrict selected users from connection through SQL*Plus and Toad but they can connect using the Oracle Developer application. I have written a trigger on database startup and access history from SYS.V#Session. It doesn't work well. Can you help?

    Requires Free Membership to View

    Login

I was able to restrict users from using Toad by using a table, database trigger, a procedure and a job. The process works as follows:
  1. I created a table that looks like this: 
    SQL> desc LOGONAUDITTABLE
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------
 EVENT                                              VARCHAR2(10)
 TIMESTAMP                                     DATE
 SCHEMA                                           VARCHAR2(30)
 OSUSERID                                         VARCHAR2(30)
 MACHINENAME                              VARCHAR2(64)
 SID                                                      NUMBER
 SERIAL#                                            NUMBER
 PROGRAM                                         VARCHAR2(100)
  2. I created a database trigger. This trigger inserted sys.v$session info into my table. My criterion for inserting data: 1) the user cannot be "R11" 2) the program equaled "TOAD.exe" or 3) the module equaled "T.O.A.D." 
    CREATE OR REPLACE TRIGGER logonauditing
AFTER LOGON ON database
DECLARE
   machinename VARCHAR2(64);
   osuserid    VARCHAR2(30);
   sid NUMBER;
   serial# NUMBER;
   program VARCHAR2(100);
   module  varchar2(100);
   CURSOR c1 IS
     SELECT osuser,
         machine ,
   sid ,
   serial# ,
   program,
   module
  FROM   v$session
  WHERE  audsid = USERENV( 'sessionid' );
BEGIN
   OPEN c1;
   FETCH c1 INTO osuserid,machinename,sid,serial#,program, module ;
       if upper(program) = 'TOAD.EXE'
      or upper(module) = 'T.O.A.D.' then
       if user != 'R11' then
             INSERT INTO LOGONAUDITTABLE
                 VALUES ( 'LOGON', SYSDATE,USER,osuserid,
                                    machinename,sid,serial#,program);
    end if;
    end if;
   CLOSE c1;
END;
/
  3. I created a procedure that reads the LOGONAUDITTABLE and kills the sessions of all the users listed in the table. Then it deletes records from the table. This procedure looks like this: 
    CREATE OR REPLACE PROCEDURE KILLSESSION AS
   sid NUMBER;
   serial# NUMBER;
   timestamp DATE;
   CURSOR c1 IS
       SELECT sid ,
           serial#,
     timestamp
    FROM   LOGONAUDITTABLE;
    --WHERE INSTR(UPPER(program),UPPER('plus33w')) > 0;
BEGIN
   FOR i1 IN c1 LOOP
      execute IMMEDIATE 'alter system kill session 
       ' ||''''||i1.sid||','||i1.serial#||'''';
   DELETE LOGONAUDITTABLE
   WHERE sid = i1.sid
   AND serial#=i1.serial#
   AND timestamp = i1.timestamp;
   END LOOP;
   COMMIT;
END;
/
  4. Next I re-created a job that runs every 30 seconds. It executes the procedure KILLSESSION.

This was first published in July 2006

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top