Access ODBC to Oracle Database - Read Only

Posted on 2009-04-17
Last Modified: 2013-12-18
Hi Experts,
I have access application which needs to connect to an Oracle database using ODBC. What is the best and most secure method of connecting to the database with 'Read Only' rights? The application will run on multiple computers if that makes any difference. The reason I need Read Only access is to satisfy J-sox requirements.

Thanks in advance
Question by:petelai100
    LVL 5

    Accepted Solution

    ODBC is just a way to connect, not meant for securing databases.  Security giving only read access will be done on the oracle side by the user id.   Many ways to do this, you just need to find what your DBA allows.  You can create a generic userid with read only access to the tables or views needed, or you can create a group, then have someone maintain the group, then have the group granted access rights for read only to the view or table again via oracle.

    Rule of thumb is to keep the security and business rules of application on central place and away from the presentation layer at all costs when possible.  This always allow a new front end be changed to the persistence layer (database) without having to reinvent code to handle business and security in the presentation layer.

    ODBC is just a way of communicating to the presentation layer to the database.  It should never care about security.
    LVL 5

    Expert Comment

    Additionally if you decide not to use odbc and go the jndi route for a web application, the only needed knowledge is the userid and password, the security is still the same via oracle security.  As long as the user is valid, Oracle "knows" what table the user can read or maintain without regards how the user connected via odbc, jdni or anything else....

    Author Comment

    Hi the application will also be using an SQL database. Would it be better to have the SQL database connect to the oracle server as opposed to multiple computers? Can it be done?

    Thanks again
    LVL 5

    Expert Comment

    Hmm not following you on that one?  Applications connect to each other, not databases.  So whats your definition of a oracle server?  Our you talking the Oracle Database, or our you talking about the Oracle container AKA Oracle web server?  

    So if your asking can a SQL Server Database talk directly to a Oracle database, the answer would be no.  Databases can only talk to each other when there the same type... aka clustering.  To my knowledge no manufacture lets you cluster two different types of databases by different vendors.

    If your asking if the Oracle container (web server) can talk to SQL Server Database it would think it would be possible but would take alot of customization.  And if the later is true the Oracle Container attaching to the SQL Server Database, why would you want to spend the large amount of money for licensing fees when the Apache server could do this for free?

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now