This discussion is archived
13 Replies Latest reply: Jun 11, 2010 2:36 AM by Didier PORTEFAIX RSS

how to limit oracle database access from other applications

744119 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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