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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: