This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Feb 21, 2012 4:06 PM by 919057 RSS

how to restrict toad or any other tool to access database using a username.

579729 Newbie
Currently Being Moderated
Hi
I have developed a front end tool. I have given to them username and password to access my front end tool
I want to restrict them to access database using toad or any other tools like jdbc etc. using the same username and password
Could you please tell me how to do this?

regards
  • 1. Re: how to restrict toad or any other tool to access database using a username.
    Anurag Tibrewal Guru
    Currently Being Moderated
    user576726 wrote:
    Hi
    I have developed a front end tool. I have given to them username and password to access my front end tool
    I want to restrict them to access database using toad or any other tools like jdbc etc. using the same username and password
    Could you please tell me how to do this?

    regards
    Hi,

    One way, have the logon trigger at the schema level that would check for client_info /client_identfier.
    If that matches to what so ever your tool is setting, allow him to login else raise an exception.

    Regards
    Anurag
  • 2. Re: how to restrict toad or any other tool to access database using a username.
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    Basically you cannot avoid people to connect onto the database with one or other tool.
    If you're using a trigger to check the program used, what refrein the user to rename the toad.exe by fake.exe and eventually workaround your "restriction" ?
    You should use proper security level on the user level and grant the users with proper roles, ip security, db vault...

    Nicolas.
  • 3. Re: how to restrict toad or any other tool to access database using a username.
    VijayaraghavanKrishnan Pro
    Currently Being Moderated
    Hi,

    This is an extract form [http://www.psoug.org/snippet/Block_TOAD_and_other_tools_516.htm]
    CREATE OR REPLACE TRIGGER block_tools_from_prod
      AFTER LOGON ON DATABASE
    DECLARE
      v_prog sys.v_$session.program%TYPE;
    BEGIN
      SELECT program INTO v_prog 
        FROM sys.v_$session
      WHERE  audsid = USERENV('SESSIONID')
        AND  audsid != 0  -- Don't Check SYS Connections
        AND  ROWNUM = 1;  -- Parallel processes will have the same AUDSID's
     
      IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
         UPPER(v_prog) LIKE '%SQLNAV%' OR     -- SQL Navigator
         UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
         UPPER(v_prog) LIKE '%BUSOBJ%' OR   -- Business Objects
         UPPER(v_prog) LIKE '%EXCEL%'       -- MS-Excel plug-in
      THEN
         RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
      END IF;
    END;
    check the below ask tom post it will be helpful
    [http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:561622956788]
    [http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9312070542748]

    Regards,
    Vijayaraghavan K

    Edited by: Vijayaraghavan Krishnan on Feb 4, 2010 8:11 PM
  • 4. Re: how to restrict toad or any other tool to access database using a username.
    John Hallas Newbie
    Currently Being Moderated
    A nice bit of PL/SQL but as has already been pointed out, that will only stop toad.exe coming in. Rename the executable to john.exe and hey presto.


    www.jhdba.wordpress.com
  • 5. Re: how to restrict toad or any other tool to access database using a username.
    VijayaraghavanKrishnan Pro
    Currently Being Moderated
    Hi,

    Thats why I mentioned the asktom posting too. His requiremnt is to stop toad.exe. If need to stop a set of programs then we need to get programs dynamically to block it.


    Regards,
    Vijayargahavan K
  • 6. Re: how to restrict toad or any other tool to access database using a username.
    ajallen Pro
    Currently Being Moderated
    As has been well pointed out, there are ways to attempt this restriction, but it can easily be worked around. So, please tell us why you (think you) need to block certain tools from accessing the database. Perhaps once we know that, we can suggest alternatives.
  • 7. Re: how to restrict toad or any other tool to access database using a username.
    579729 Newbie
    Currently Being Moderated
    Hi
    Thanks
    I have developed my tool using forms 10g. And this is being used by a lot of users having different oracle userid.
    Obviously I have to give them access to database objects like select,insert, update, delete, execute etc.

    If the user logs in using TOAD, he/she can tamper the data (because business logics are there in forms).
    He/She may insert data in a table without using the sequence (suppose primary key is based on a sequence). Later when some ohter user inserts data using the forms, it may throw primary key error.

    users (non skilled) may exucute a query or statement that may cause locks to some important database objects. That may degrade the performance.

    he/she may create unnecessary database object in the database.

    I don't want them to access database using any ohter connectivity like jdbc etc and many more......

    regards
  • 8. Re: how to restrict toad or any other tool to access database using a usern
    737129 Explorer
    Currently Being Moderated
    Remove the TOAD access from the user
  • 9. Re: how to restrict toad or any other tool to access database using a username.
    579729 Newbie
    Currently Being Moderated
    hi
    I have followed this approach, but eventhoug the user cannot log in but still an INACTIVE session exists.
    And when I added the following statement in the code, it didn't work.

    execute immediate 'ALTER SYSTEM KILL SESSION '||''''|| l_sid||','|| l_serrial||'''';

    it gives me the following error

    ORA-00027: cannot kill current session


    Could you please tell me how to kill that INACTIVE session?

    regards
  • 10. Re: how to restrict toad or any other tool to access database using a username.
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    It tells you only that you cannot kill your own session. Otherwise, kill an inactive session is same as kill an active session.

    Nicolas.
  • 11. Re: how to restrict toad or any other tool to access database using a username.
    579729 Newbie
    Currently Being Moderated
    Thanks
    Is there any way to kill this session in the same code?
    Because user is unable to log in but one INACTIVE session is getting created.

    Regards
  • 12. Re: how to restrict toad or any other tool to access database using a username.
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    If you want to "kill" your own session, just disconnect.
    But I'm sure I don't understand what you are saying.

    Nicolas.
  • 13. Re: how to restrict toad or any other tool to access database using a username.
    579729 Newbie
    Currently Being Moderated
    My aim is to restrict users to use TOAD or any other tools except from the software that I have provided to them (i developed it using forms 10g).
    For this I have created following code from sys user

    CREATE OR REPLACE TRIGGER block_other_tools
    AFTER LOGON ON DATABASE
    DECLARE
    l_prog sys.v_$session.program%TYPE;
    l_username VARCHAR2(1000);
    l_application VARCHAR2(1000);
    l_flag NUMBER:=0;
    l_sid NUMBER;
    l_serrial NUMBER;
    l_session_kill_string VARCHAR2(100);

    BEGIN
    SELECT program,USERNAME, SID, SERIAL# INTO l_prog ,l_username, l_sid, l_serrial
    FROM sys.v_$session
    WHERE audsid = USERENV('SESSIONID')
    AND audsid != 0 -- Don't Check SYS Connections
    AND ROWNUM = 1; -- Parallel processes will have the same AUDSID's

         l_prog:= UPPER(l_prog);
         l_username := UPPER(l_username );

         IF l_prog LIKE 'FRMWEB@%' THEN
              l_flag:=1;
         ELSE     
              BEGIN
                   SELECT OAU_APPLICATION_NAME INTO l_application FROM other_application_user WHERE OAU_USERNAME= l_username;
                   l_application:= UPPER(l_application);     
                   IF l_application = 'ALL' THEN
                   l_flag:=1;
                   ELSIF l_prog LIKE l_application||'%' THEN
                        l_flag:=1;
                   ELSE
                        l_flag:=0;
                   END IF;
              EXCEPTION
                        WHEN NO_DATA_FOUND THEN
                             l_flag:=0;
                        WHEN OTHERS THEN
                             l_flag:=0;
              END;
         END IF;

         IF l_flag = 0 THEN
         l_session_kill_string:='ALTER SYSTEM KILL SESSION '||''''|| l_sid||','|| l_serrial||'''';
              EXECUTE IMMEDIATE l_session_kill_string;
              RAISE_APPLICATION_ERROR(-20000, 'Other tools are not allowed.');
         
         END IF;
    EXCEPTION
         WHEN OTHERS THEN
         RAISE_APPLICATION_ERROR(-20000,SQLERRM );
    END;



    It's working fine but giving the following error when an user is trying to log in
    ORA-20000: ORA-00027: cannot kill current session

    I am trying to kill the session because eventhogh the use is unable to log in but an INACTIVE session exists there.

    Regards
  • 14. Re: how to restrict toad or any other tool to access database using a username.
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    user576726 wrote:
    It's working fine but giving the following error when an user is trying to log in
    As I said earlier, that will work until someone rename TOAD to your application name...
    ORA-20000: ORA-00027: cannot kill current session
    Again, as I said earlier, you cannot kill your own session, and this is what your trigger is attempting to : kill the session of the user which is trying to connect.
    So, your trigger is wrong by trying to kill the session, you should raise an error instead.

    And eventually, your trigger is useless.

    Nicolas.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points