1 2 Previous Next 20 Replies Latest reply: Feb 21, 2012 6:06 PM by user7052551 RSS

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

    579729
      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
          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
            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.
              Vijayaraghavan Krishnan
              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
                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.
                  Vijayaraghavan Krishnan
                  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
                    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
                      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
                        Remove the TOAD access from the user
                        • 9. Re: how to restrict toad or any other tool to access database using a username.
                          579729
                          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
                            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
                              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
                                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
                                  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
                                    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