Q

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 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:
  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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close