RE: Security and the ODBC

Food for thought, the performance would be an issue in my case. It would be possible to create a database per user that gets populated via a C-Deamon running on the Unix box. Again the idea of a second process handling the request has merit. Instead of unloading to a file, insert into a database (or a table in the same database). The key here is to have a second process populate the data to an area the user can access, from an area that only Informix can access. Here's another idea. Is there a way to talk to Snet without the ODBC? If this is the case, I could talk directly to Snet and not even load the ODBC driver at all? Matter of fact, prior to going to VB we used C++ and talked to I-Net directly without even using an ODBC any ideas on this (time to go back and RTFM some more). -----Original Message----- From: Scott Black Sent: Tuesday, September 09, 1997 11:30 AM To: Tim Kelly Subject: RE: Security and the ODBC I'm not sure there is a way to do what you want using only Informix permissions. We are up against a similar (though not as complex) issue. We are worried about someone with an ODBC gaining access to whatever 'public' has access to. We therefore have to revoke 'public' permissions and give them explicitly to each user. The only problem being that performance goes to hell quick (68 extents for syscolauth, and 68 for systabauth). It occurs to me that you need a second buffer to process the select requests. If a user is at the appropriate terminal, and he has the appropriate authority, etc. Then send a request to another user that has been given the select permission to do the select for this user. Therefore no-one has the permission other then [informix] and can't get at the information unless they go through the appropriate terminal/program. You could for instance (through cron maybe) have the privileged user monitoring for a request for data, do the select, unload it to a file, and have the actual user load from the file into a temp table. I'm sure you can think of other (faster) ways to go about this. I understand this is slower, and convoluted. If there are actually 'thousands' of business rules this may not be practical, but if there truly is no other way... Just thinking out loud. > -----Original Message----- > From: Tim Kelly [SMTP:Tkelly@svhs.org] > Sent: Tuesday, September 09, 1997 7:39 AM > To: 'informix-list@rmy.emory.edu' > Subject: FW: Security and the ODBC > > > > Let me take another stab at this. I'm not suggesting you're totally > wrong, but you cannot create a View on every relationship that you > want. > It isn't that way. I have a very complex database schema with 56 > tables > and some of them with over 25 million rows. Views are inefficient and > CANNOT always be used. The point of the original question was not to > be > taken literally as the only example. Just think of it in several > different situations. If you give a user an ODBC connection (using > the > Informix CLI) there is no way to ensure they are coming from your > application and your application only. That's the question. > > Yes, views do restrict certain things. But, you cannot do > certain things like inserts, deletes, and etc on certain types of > views. > So in those cases views are not usable. Also, this is even more > complex > as I'm running 4 instances of online and 12 databases. The > application > dynamically builds a select statement across multiple instances and > multiple databases. The database names and tables change dynamically > and are not always the same. There is no way a view would even work. > > The only thing I've seen as a possible solution is using a ROLE. > But, again building dynamic database selects seems not to work. For > example, if you switch ROLE in database A and now try to select from > database A, B, and C or just C; you are not known to those foreign > database as your new ROLE. > > I stand corrected on the use of the TERM security. Yes, I > agree building security into the application was a miss use of the > term. > Security is NOT what I'm trying to enforce in the literal since. I > was > speaking figuratively. I need to restrict access based on business > rules. In my business, and company, we call that security. Like for > example: We have a business rule that says if you are logged on to a > certain workstations you CANNOT print or fax. This would be if you > left > your office and logged on from another PC at one of our remote > locations. Yes, this is a business rule. But, as far as my manger is > concerned this is security. > > Actually, the application is a Medical Records Imaging database > and there are thousands of business rules. Without these business > rules > (a.k.a. security) a user could gain access to confidential patient > information. The rules and security can be enforced by the > application. > But, again they can circumvent them if they use another ODBC client > application on that workstation. You cannot use the authorization of > the > database engine to restrict information based on a business rule. > > Another example: If you have HIV Test results available in your > database and a user is allowed to view those only if they are logged > in > form terminal #55 using an application called HIV-View which is in a > restricted and secured area (so you can use physical security to > prevent > someone from taking that information). How do you code a view to only > allow access to the information in this case? The user can go down > the > street to another workstation that has CLI loaded (but doesn't have > HIV-View) and use Crystal Reports to access that view and printout all > the information. > > > > > > > -----Original Message----- > From: PETER.LANCASHIRE.PL1@bayer.co.uk > [SMTP:PETER.LANCASHIRE.PL1@bayer.co.uk] > Sent: Tuesday, September 09, 1997 5:34 AM > To: Tim Kelly > Subject: RE: Security and the ODBC > > Tim, > > I don't think I did miss the point. Excuse me if I have this > wrong but ODBC > goes via the database engine. The engine gives access to the > tables according > to the user authorisation identifier (in SQL-speak). ODBC > demands a user name > and password if it is set up correctly. So, any database access > privileges will > be enforced by the engine. Therefore your privileges and views > will work. > > If this did not work I suspect nobody would be using ODBC > because it would > render all database security useless. > > It would be possible to design your application so it gave the > username, not > the users. This is a glaring security hole and should be fixed > (like passwords > programmed into function