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.

Published: 20 Jul 2006

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?

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:

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)

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;
/

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;
/

Next I re-created a job that runs every 30 seconds. It executes the procedure KILLSESSION.

Dig Deeper on Using Oracle PL-SQL

SearchDataManagement

Dig Deeper on Using Oracle PL-SQL

How to concatenate rows into a single CLOB in PL/SQL

ORA-01422 error when procedure returns more than one row

CURRENT OF with a RETURNING clause

Sending spool file as mail attachment

Start the conversation

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

Please create a username to comment.