Re: ODBC & Client Access

This discussion has been up not to long ago. I now however have decided what we are going to do. Irwin Goldstein <irwin@objectsoft.com> wrote: :Ray Riley <weho-isd@ci.west-hollywood.ca.us> writes: :> Does anybody have an answer for ODBC client security? :> :> Once you open up ODBC to users, how do you keep them from modifying tables without the :> benefit of applications enforcing the database integrity? :> :> Ideally, I should be able to monitor who connects via ODBC and control access to the :> databases and tables without having to get involved with lots of messy grants and :> revokes. I've read on this Newsgroup somebody mentioning a product/technique called :> "Roles," but I haven't been able to find any reference to it elsewhere, but it is :> supposed to help manage grants etc. Has anyone heard of, or have any info on this? :<snip> :Check out OpenLink's ODBC drivers. They implement ODBC with a server-side request broker :that uses a security "rule book". You can enable access and various levels of read/write :privledges by user, application, client host name/IP address, etc. I think you can find OpenLink :at http://www.openlink.uk.co. I think they have a US Web site as well, but I'm not sure of :the URL. You can also find them on CompuServe (GO OPENLINK). Their main site is: www.openlinksw.com (US based). Ian Goddard <igoddard@netcomuk.co.uk> wrote: :At risk of repeating a point I made some time ago, I believe there's a :need to authenticate the application as well as the user. If you give :users access to the datbase and a set of tables with GRANT they can, as :things stand, access them with any program they get their hands on. :There seems to be little to prevent a user stitching together visual :basic or even a spreadsheet with ODBC and hitting the database with it. :Providing what they do doesn't violate any defined constraints I don't :see how you can lock them out. It seems to me that the ODBC mechanism :ought to incorporate a digital signature from the application and that :there ought then to be CRUD ermissions against application or :application/user combinations. The present situation, as far as I can :see, is a large security hole. This is exactly what OpenLink has allready done. They are using the task list from Windows that can be manipulated by cleaver users, so there are still holes, but it is prety good. Now they should of course talk to Micorosoft and make them put this into the standard (in ODBC 3.0) as a requirement. The problem right now is any user may install I-Net and an I-Net based ODBC driver on their PC and bypass the security OpenLink provides. (I am talking to people at Informix currently about this situation. May be they will fix it one day.) However one big issue is what kind of security you want and why. If you want real security that can keep hackers avay you probably have to look at DCE/Kerberos. I don't know these solutions in any detail, but they seem to be available from Informix and provide this kind of security. They also seem to be hard to use and probably expensive. Also if you have only one server and don't need TCP/IP based connectivity to it you can set up the OpenLink request broker to use shared memory access. In this case even if a user installs I-Net and/or any other ODBC driver then OpenLink on his PC he will gain no access to the database server. You control everything from the server which is presumably secure. This is a simple solution that works today. If you have NewEra programs running on PC's you will have to make them connect to the server via ODBC for the above to work. Currently this isn't possible for development, and hard for runtime. In ver. 3.0 it's supposed to become simpler. If it will work for development I don't know, but than you will have to install another non secured machine for that. Most other programs that use I-Net directly can also be set up to use ODBC (PowerBuilder, Delphi, Impromptu and others). Using the OpenLink drivers you will probably gain higher performance (or the same as I-Net) and shouldn't loose much, if anything in facilities. Now if you for some reason need TCP/IP based connection to your server (you may have a requirement for I-Net on PC's or have several database servers on different machines that need to communicate) you have a problem. As long as users are using OpenLink you can still set up very good security. However any user can now install any ODBC driver they can get their hands on without you even knowing. In this case the question of what kind of security you need arises. For us the main problem isn't hackers that are out to destroy, but users that do things they shouldn't and most often don't realy know that they are doing something bad. In this situation roles are usefull. Your applications running on PC's that need to have full access to tables use the set role command to gain this access. Default access (to public) is either select only or no access at all. "Non applications" (users running MS Access or similar tools) then automatically get this select access or no access at all if they aren't spesifically defined otherwise in the database. For a few very select users you *might* want to give them full direct access to tables (to do update/insert/delete directly via plain vanilla MS Access). Other users you give default of select only so they may use MS Access, Impromptu or other tools for reporting/analysis. Now you will say a clever user can use Visual Basic/VB for Applications or other tools to send a set role command to the engine and therby get full access to tables. First this is limited to users who have applications that do this. As far as I remember the roles system, other users may be locked out from setting such roles. So with these users you are left having to trust them that they don't try this. This is a problem, but we will accept it for now. This is by the way why we are talking to Informix to have them implement more security at this level. All in all the situation isn't as bleak as it seemed last time this issue where up. The next thing we will investigate is how these issues can be solved using Windows NT on the server. As far as I know OpenLink currently doesn't have a version of their request broker for Windows NT and Informix. As they have it for other engines, and the interest in the Informix community has been somewhat stired up lately for OpenLink :-) I trust they will have it very very soon - or what do you say Owen. Further comments and suggestions would be appreciated. There are as you can see, still some holes to be plugged, and demand for it must come from us users. Nils.Myklebust@ccmail.telemax.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company