13 Replies Latest reply: Jun 11, 2010 4:36 AM by Didier PORTEFAIX RSS

    how to limit oracle database access from other applications

    744119
      Hie,

      I am using Oracle 10g Database with oracle forms 6i, 10g and ASP website as a front end. I want to secure my database from other application like TOAD, SQL IDE. I go through v$session view but most of time it has several null fields in case of different application, so i cant stop other application. I want that my all users can connect only from forms, report and web.

      Kindly tell me how is that possible.

      Regards
        • 1. Re: how to limit oracle database access from other applications
          Zeeshan BaiG
          i copied from http://www.psoug.org/snippet/Block_TOAD_and_other_tools_516.htm
          CONNECT / AS SYSDBA;
           
          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;
          /
          SHOW ERRORS
          plz mark it helpful / correct
          • 2. Re: how to limit oracle database access from other applications
            744119
            Sory, it didn't work. Actually some application didn't come up in 'PROGRAM' attribute in V_$SESSION table. and we have NULL values like report runtime, TOAD 9.0 version.
            • 3. Re: how to limit oracle database access from other applications
              spilgrim
              One way is to grant access to the application via a role with a password. Your Forms application knows this password and will then be able to enable the role. Any other method the user uses to connect they won't know the role password and then won't have access to the schema.

              Steve
              • 4. Re: how to limit oracle database access from other applications
                744119
                I know we can stop accessing database from this way but this is very unreliable in my office. I want to do from procedure or function through some authentic values.
                • 5. Re: how to limit oracle database access from other applications
                  frm40735
                  could you not have NVL (program, module) in the solution given??

                  What are you getting when you run this code when you connect to the database using TOAD or PL/SQL Developer?
                  SELECT username,
                          program,
                          module,
                          terminal
                  FROM   v$session
                  WHERE  audsid = userenv('SESSIONID') 
                  • 6. Re: how to limit oracle database access from other applications
                    Marwim
                    Hello 8017230,
                    I know we can stop accessing database from this way but this is very unreliable in my office.
                    Why is it unreliable? Are these passwords flying around for everyone to see? If your developers/DBAs are unreliable you will not be able to protect your application.
                    I want to do from procedure or function through some authentic values.
                    The information you get from v$session are provided by the program that connects. So they can be changed easily (see [Ask Tom|http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:561622956788#3203632394743] or [Ask Tom|http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9312070542748])

                    Regards
                    Marcus
                    • 7. Re: how to limit oracle database access from other applications
                      744119
                      USERNAME.........PROGRAM...................MODULE................TERMINAL
                      ----------------------------------------------------------------------------------------------------------------------
                      FAHAD...............sqlplusw.exe...............SQL*Plus...............ITD-FAHAD
                      FAHAD...........................................................................ITD-FAHAD
                      FAHAD...........................................................................ITD-FAHAD
                      FAHAD...............................................TOAD 9.0.0.160.....ITD-FAHAD

                      Middle tow sessions are reports sessions. And some other applications gives null values also.

                      Edited by: user8017230 on Jan 14, 2010 10:42 PM
                      • 8. Re: how to limit oracle database access from other applications
                        744119
                        Why is it unreliable? Are these passwords flying around for everyone to see? If your developers/DBAs are unreliable you will not be able to protect your application.

                        Dear Marwim,

                        Forms firsltly develop their own session with form user id and pass then we have connect user so that he/she can see their respective data. I only want that no one even developer can connect through sql editor or toad from their onw user id and pass

                        The information you get from v$session are provided by the program that connects. So they can be changed easily (see Ask Tom or Ask Tom)

                        Yes you are rite, but I will takecare of that.
                        • 9. Re: how to limit oracle database access from other applications
                          Mark Reichman
                          If you cannot trust your DBAs then you have bigger problems. Databases are built upon that trust.

                          The role with a password is a good approach. The password is buried in the form. No one but you and the DBAs know the password. The only reason the DBAs know the password is becuase they most likely created the role for you.

                          Restricing access to data via grants and roles is a common approach...

                          All of our applications use the role with a password method and this does prohibit others from doing DML, (sel, upd, del, ins) to the applications schema/account from their oracle account no matter what tool they are using. The only way they can manipulate the data is to log in to the form app.

                          Works great.

                          Using the logon proc in the above post looks interesting, but then you would have to list every possible program that can connect to the database and do DML. You will never accomplish that. Notice how they forogt to add sqlplus to the list. :)

                          Edited by: Mark Reichman on Jan 15, 2010 10:24 AM

                          Edited by: Mark Reichman on Jan 15, 2010 10:26 AM
                          • 10. Re: how to limit oracle database access from other applications
                            744119
                            I know I cant block all the applications but through that procedure I can only allow those applications which is legal in my company. I am surprised that oracle session cant reconganized some of oracles product like oracle report.
                            • 11. Re: how to limit oracle database access from other applications
                              744119
                              okie.. I have a better solution but my solution is based upon the WRAPPING (Oracle Feature). I constructed my solution upon the assumption that oracle provides me that Wraps files cant unwrap. Unfortunately unwrap utilities are easily available and through that you can unwrap all the wrap procedures.

                              Guys, if any 1 have better solution to protect code then please tell me.
                              • 12. Re: how to limit oracle database access from other applications
                                Andreas Weiden
                                The question is, what exactly to you want to "hide". Using wrapped code only hides the "implementation" of procedures, functionf or packages, but nothing else. If you want to hide that information, follow the already suggested method with a password-protected role which is granted to your users, but not set as default role. In your forms-start-module, enable that role using dbms_session.
                                • 13. Re: how to limit oracle database access from other applications
                                  Didier PORTEFAIX
                                  Hi,

                                  i tried this snippet, but the only thing that it does is writing in the alertxxx.log and in udump trace file, while SQL Developer still connects to the database :

                                  -----
                                  [oracle@] tail alert_dbxxx.log
                                  ...
                                  Errors in file /oracle/applications/admin/dbxxx/udump/dbxxx_ora_32587.trc:
                                  ORA-00604: une erreur s'est produite au niveau SQL r�cursif 1
                                  ORA-20000: Les outils de developpement sont interdits sur les bases de production.
                                  ORA-06512: � ligne 16
                                  [oracle@] cat dbxxx_ora_32587.trc
                                  *** SERVICE NAME:(dbxxx) 2010-06-11 11:21:44.535
                                  *** SESSION ID:(471.8490) 2010-06-11 11:21:44.535
                                  Skipped error 604 during the execution of BLOCK_TOOLS_FROM_PROD
                                  *** 2010-06-11 11:21:44.540
                                  ksedmp: internal or fatal error
                                  ORA-00604: une erreur s'est produite au niveau SQL r�cursif 1
                                  ORA-20000: Les outils de developpement sont interdits sur les bases de production.
                                  ORA-06512: � ligne 16