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 last published in July 2006

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close