Re: Restricting ODBC access

I, Jacob Salomon, posted a request for solutions (RFS? ;-): > Can anyone come up with a way to prevent database access via ODBC? I > can't simply deny ODBC drivers to all PC users - there are other > databases to be accessed. The restriction I need should be settable > on a database by database basis. (Say that fast 10 times. ;-) Idealy, > it should be able to restrict data modifications via ODBC. Thanks to all who responded, both by e-mail and posting. For the benefit of the rest of the family, I am summarizing the answers I got. I believe all of them will help get my client going. "Dave Otto" <dotto@themoneystore.com> posted: |Easy if you are running 7.1+. Use ROLES. If there is no default role |defined, then the first the application must do is execute the |"set role..." statement. Unless the user has access to a SQL |command AND knows the appropriate role to set, s/he will be |locked out. As I recall, roles are a feature of 7.2+, but since this user is on the upgrade path (as opposed to the warpath ;-) this should not be a problem. Since the client's main concern is an inadvertent update by a user of Excel (or equivalent application), this alone might be adequate. I suppose this depends on if there is a way in Excel to issue commands like "set role". I kinda doubt it. Michael Segel <Mikey@NOSPAM.KingofMyDomain.MAPSON.Segel.com> posted an objection relating to the PC management issues. Valid objections I will raise with other clients but (I think) not a major concern to this client at this time. Irwin Goldstein <irwinNOSPAM@NOSPAMobjectsoft.comNOSPAM> posted a small manual on using OpenLink to diverting the socket connection from the database server to the server piece of OpenLink. This looks like a very viable solution to the problem if roles don't do the job. Allan Gould <allang@sco.com_no_spam> posted: |Our ODBC driver has a security component which allows you to restrict |access to your database via ODBC by table, user, SQL statement (i.e. |allow read-write/read-only access etc) in various combinations. Take |at look at SCO SQL-Retriever. | http://www.sco.com/vision/products/sqlretriever/ |for more information and a downloadable eval. Another possibility, although the issue of PC management raises its head. What's to enforce the use of this ODBC driver if a stubborn & clever user wants to use the ODBC driver he already had? Again, not a real objection in this case, since the concern here is over inadvertent updates. Eric e-mailed me the following: |Try OPENPATH (from Trilogy) ODBC driver or OPENLINK ODBC drivers both |offer access to the database based upon the user name and also allow |read or read/write capabilities. That's the second time I've seen OpenLink mentioned as a solution. I will look into OpenPath as well. Thanks all. I think with all this info to digest, my client will not bother me about it for another few months! ;-)) -- -- Jake (Pondering the color of an asphyxiated smurf) +------------------------------------------------------------+ | The expedient performance of a task with excessive concern | | regarding its duration-to-completion engenders a virtual | | certainty of diminished benefit therefrom. | | -- Benjamin Franklin (but he said it in 3 words) | +------------------------------------------------------------+