Access ODBC to Oracle Database - Read Only

Posted on 2009-04-17
Medium Priority
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
  • 3

Accepted Solution

TheVeee earned 2000 total points
ID: 24168212
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.

Expert Comment

ID: 24168251
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

ID: 24168346
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

Expert Comment

ID: 24170039
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

839 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