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 keys). Designing business logic into applications is fine. Designing security is not for the reasons you give. The view and privilege statements are: create view myview select * from mytable where rsrc_id = USER; revoke all on mytable from public, user1, user2, etc; grant select, delete, update on myview to user1, user2, etc; See page 10-22 in Informix Guide to SQL, Tutorial version 7.1. Peter To: Peter Lancashire@BAYER-UK-NOTES cc: "DDA.RFC-822=informix-list@rmy.emory.edu/P=BAYER/A=DBP/C=DE"@X400 From: Tkelly@svhs.org @ INTERNET @ BAYERNOTES Date: 09-09-97 04:44:00 CET Subject: RE: Security and the ODBC You missed the point. If the ODBC allows access to the database they get access to all tables and all views. Once you leave your application, there is NOTHING stopping them from using any ODBC compliant application to access your database and totally bypass your view. You can switch roles and do a few tricky things like that, but again this is some what limited. If you install the 32-CLI on the client PC and they have a passwd to access your database they can access it with Crystal Reports to MS-Access or whatever they want. That's the issue, and the view you suggest does nothing. *-----Original Message----- *From:*Peter Lancashire [SMTP:Peter.Lancashire.PL1@bayer.co.uk] *Sent:*Monday, September 08, 1997 5:51 AM *To:*informix-list@rmy.emory.edu *Subject:*Re: Security and the ODBC *Tim Kelly wrote: *> *> Ok, quick one. I have a Informix 7.x server running with a database. The *> users connect to it via Informix-CLI client from within Win95 using a *> complied program. The complied program handles the "business rules" of the *> database as far as insuring they have security etc. Simple example: *> *> One table they are allowed to insert rows and delete etc. But, the *> application checks a column called rsrc_id and doesn't allow them to delete *> or change a row if it isn't theirs (the rsrc_id is users name). It works *> fine. Remember this is just an example and I'm not wanting to know about *> Triggers, SPL etc. *> *> The issue is this: The CLI client is an ODBC driver. The user can use *> any ODBC application and point it to my Informix Server. Example being *> MS-Access. Now MS-Access (along with others) can just do what ever it *> wants to my data. In the above example the user is allowed to delete from *> the table and the application is ensuring the rsrc_id matches. So, is *> there a way to tell Informix to accept connections based on an application? *> Any ideas other then writing a billion triggers, SPL etc etc? *Try creating a view which includes all the columns of the base table and *selects on the USER name and the user's name stored in the table. This *view then contains only rows the user "owns". Grant only select to the *base table and insert and delete to the view. This is textbook stuf