Re: ODBC Read-Only Database Privilege
Jeff Snyder wrote: > > I would like to set up read-only database privileges for an ODBC client. > Is it possible to achieve > this through the ODBC configuration on the client? I am currently using > the Informix 3.30 32 bit > ODBC driver found in the SDK. Best to do this in the engine. REVOKE all priveleges from the client's user id on the server (ie in the engine) except SELECT privelege. Art S. Kagel
If this is practical, Art's suggestion is probably the most secure. This may not be practical if, for instance, you have a an existing application that you cannot change which needs R/W access to many objects in the database. If you open up your database for network access (which you must to use the Informix ODBC drivers), then there is no way to prevent the same users who use the application from connecting with full R/W privledges using ODBC. If you can change the application, then you can have it use roles to get the prviledges it needs and revoke all but select permissions to the users and/or public. As long as no one knows what role to set via ODBC, you're safe. The role name becomes a password of sorts for R/W access. If you can't change the application to utilize roles, then another option is to use an ODBC driver such as OpenLink's (www.openlinksw.com) multi-tier drivers which provide their own level of security. With OpenLink, for example, you can configure the Informix instance to not support network connections thus eliminating access via the Informix ODBC drivers. ODBC connections will then happen only through OpenLink which has it's own network communications layer and it's own additional security features which can, among other things, force read only access by user-ID and various other rules. In article <390F1E2E.427D527F@bloomberg.net>, kagel@bloomberg.net wrote: > Jeff Snyder wrote: > > > > I would like to set up read-only database privileges for an ODBC client. > > Is it possible to achieve > > this through the ODBC configuration on the client? I am currently using > > the Informix 3.30 32 bit > > ODBC driver found in the SDK. > > Best to do this in the engine. REVOKE all priveleges from the client's > user id on the server (ie in the engine) except SELECT privelege. > > Art S. Kagel > -- Irwin Goldstein Objective Software Systems, Inc. http://www.objectsoft.com Sent via Deja.com http://www.deja.com/ Before you buy.
irwin_goldstein@my-deja.com wrote: > > If you can't change the application to utilize roles, then another > option is to use an ODBC driver such as OpenLink's (www.openlinksw.com) I had looked at the latest ODBC drivers that came with the Informix SDK and found the them to work pretty well, contrary to reports about earlier versions. I liked the fact that the Intersolv driver presented only those tables to the client PC to which the user has any access. However, the need to different kinds of access mentioned in this thread, and a multi-platform environment, caused me to look at the OpenLink driver as well. Unfortunately, I have had some problems getting Excel 2000 to work smoothly with external queries via the OpenLink Multi-Tier version 3.2 driver. I'm going against SE 7.24.UC8 on Sun/Solaris 7. Specifically, the login prompt window doesn't display after I select the data source. However, if I pretend it's there and type accordingly, I can log in OK and do the query. The login works fine if I run MS Query directly. I didn't have any problems with Excel 97, or 98 or the Mac. Has anyone else seen this behavior? Thanks, Walt. PS: BTW, one of the people at Intersolv/Merant said that they would be offering their ODBC drivers in a three-tier architecture soon, either directly to the public or through 3rd parties. They had previously been an OEM-only provider. -- Walt Hultgren Manager, Information Technology Yerkes Research Center of Emory University Mailto:walt@rmy.emory.edu -- 404-727-0648
Hi Walt With regards this problem you are experiencing, are you sayingh that the Login Box does not appear when you are trying to run external queries or vice-versa. If it is the former, you can enable the Login Box by select the DSN in the ODBC administrator and unchecking the No-login Box option. HTH In article <390F5C65.2DD530E0@rmy.emory.edu>, Walt Hultgren <walt@rmy.emory.edu> wrote: > irwin_goldstein@my-deja.com wrote: > > > > If you can't change the application to utilize roles, then another > > option is to use an ODBC driver such as OpenLink's (www.openlinksw.com) > > I had looked at the latest ODBC drivers that came with the Informix SDK > and found the them to work pretty well, contrary to reports about > earlier versions. I liked the fact that the Intersolv driver presented > only those tables to the client PC to which the user has any access. > However, the need to different kinds of access mentioned in this thread, > and a multi-platform environment, caused me to look at the OpenLink > driver as well. > > Unfortunately, I have had some problems getting Excel 2000 to work > smoothly with external queries via the OpenLink Multi-Tier version 3.2 > driver. I'm going against SE 7.24.UC8 on Sun/Solaris 7. > > Specifically, the login prompt window doesn't display after I select the > data source. However, if I pretend it's there and type accordingly, I > can log in OK and do the query. The login works fine if I run MS Query > directly. I didn't have any problems with Excel 97, or 98 or the Mac. > > Has anyone else seen this behavior? > > Thanks, > > Walt. > > PS: BTW, one of the people at Intersolv/Merant said that they would be > offering their ODBC drivers in a three-tier architecture soon, either > directly to the public or through 3rd parties. They had previously been > an OEM-only provider. > > -- > Walt Hultgren > Manager, Information Technology > Yerkes Research Center of Emory University > Mailto:walt@rmy.emory.edu -- 404-727-0648 > Sent via Deja.com http://www.deja.com/ Before you buy.
Thanks for the information. Indeed, the existing application does not make it feasible to use Informix's table level privileges as the application relies upon PUBLIC having all privileges for over 150 tables. I did download the multi-tier driver from OpenLink and it appears to meet my needs. Jeff irwin_goldstein@my-deja.com wrote: > If this is practical, Art's suggestion is probably the most secure. > This may not be practical if, for instance, you have a an existing > application that you cannot change which needs R/W access to many > objects in the database. If you open up your database for network > access (which you must to use the Informix ODBC drivers), then there is > no way to prevent the same users who use the application from > connecting with full R/W privledges using ODBC. If you can change the > application, then you can have it use roles to get the prviledges it > needs and revoke all but select permissions to the users and/or > public. As long as no one knows what role to set via ODBC, you're > safe. The role name becomes a password of sorts for R/W access. > > If you can't change the application to utilize roles, then another > option is to use an ODBC driver such as OpenLink's (www.openlinksw.com) > multi-tier drivers which provide their own level of security. With > OpenLink, for example, you can configure the Informix instance to not > support network connections thus eliminating access via the Informix > ODBC drivers. ODBC connections will then happen only through OpenLink > which has it's own network communications layer and it's own additional > security features which can, among other things, force read only access > by user-ID and various other rules. > > In article <390F1E2E.427D527F@bloomberg.net>, > kagel@bloomberg.net wrote: > > Jeff Snyder wrote: > > > > > > I would like to set up read-only database privileges for an ODBC > client. > > > Is it possible to achieve > > > this through the ODBC configuration on the client? I am currently > using > > > the Informix 3.30 32 bit > > > ODBC driver found in the SDK. > > > > Best to do this in the engine. REVOKE all priveleges from the > client's > > user id on the server (ie in the engine) except SELECT privelege. > > > > Art S. Kagel > > > > -- > Irwin Goldstein > Objective Software Systems, Inc. > http://www.objectsoft.com > > Sent via Deja.com http://www.deja.com/ > Before you buy.
I must agree that the latest ODBC drivers from Informix seem to be much more stable and perform better than prior versions. As with the situation you have described however, I still find instances where I need the additional flexibility of the OpenLink architecture, especially with regards to security. With regards to the OpenLink/Excel 2000 problem, I can't say I've seen this specific problem before, but I have seen quite a bit of strangeness with OpenLink (and some other ODBC drivers as well) with Office 2000 applications. For the most part these problems were solved by downloading and installing the latest MDAC from Microsoft's web site. I've never used the OpenLink drivers to access SE, but I doubt that has anything to do with the problem you described. It sounds like a client side issue. Have you tried contacting OpenLink tech support about this? I've found them pretty helpful in the past. HTH, -- Irwin Goldstein Objective Software Systems, Inc. http://www.objectsoft.com In article <390F5C65.2DD530E0@rmy.emory.edu>, Walt Hultgren <walt@rmy.emory.edu> wrote: > irwin_goldstein@my-deja.com wrote: > > > > If you can't change the application to utilize roles, then another > > option is to use an ODBC driver such as OpenLink's (www.openlinksw.com) > > I had looked at the latest ODBC drivers that came with the Informix SDK > and found the them to work pretty well, contrary to reports about > earlier versions. I liked the fact that the Intersolv driver presented > only those tables to the client PC to which the user has any access. > However, the need to different kinds of access mentioned in this thread, > and a multi-platform environment, caused me to look at the OpenLink > driver as well. > > Unfortunately, I have had some problems getting Excel 2000 to work > smoothly with external queries via the OpenLink Multi-Tier version 3.2 > driver. I'm going against SE 7.24.UC8 on Sun/Solaris 7. > > Specifically, the login prompt window doesn't display after I select the > data source. However, if I pretend it's there and type accordingly, I > can log in OK and do the query. The login works fine if I run MS Query > directly. I didn't have any problems with Excel 97, or 98 or the Mac. > > Has anyone else seen this behavior? > > Thanks, > > Walt. > > PS: BTW, one of the people at Intersolv/Merant said that they would be > offering their ODBC drivers in a three-tier architecture soon, either > directly to the public or through 3rd parties. They had previously been > an OEM-only provider. > > -- > Walt Hultgren > Manager, Information Technology > Yerkes Research Center of Emory University > Mailto:walt@rmy.emory.edu -- 404-727-0648 > Sent via Deja.com http://www.deja.com/ Before you buy.