Re: Database Security with ODBC users...

In article <3ehh9d$c0n@emory.mathcs.emory.edu>, cwakins@leia.alloys.rmc.com (Clem Akins) writes... >> I have an order entry application that has been written with the Fourgen >> Case Tools and Informix 5.01 SE on a RS/6000. I would like to use a >> third party product for simple ad-hoc reporting like MS Access using >> ODBC and INET. Is there a way to protect the database from any >> unfortunate updates while using MS Access? I will be using the same >> user-id for the order entry application as well as MS Access. I think Clem refers to a different problem, and one I have faced several times when dealing with client-server applications that use ODBC. It exists with all database systems. The problem is not one of _security_, i.e. of making sure a user can not update things he/she is not supposed to see or update. This can be guaranteed by using specific accounts on the server, and by granting table and other database privileges to those accounts. The problem is one of _correctness_ . Taking the order entry example, a typical order entry application will do all sorts of checks before doing the updates (like checking stock levels, customer credit, etc) before doing the updates. Then the updates can be complex. Foe instance, if stock goes below a certain threshold, insert a row into a 'reorder' table. All those checks and updates are done by the _application_ code. They essentially implement the business rules defined by the company. In order for the application and the users to do their work, the users must be given the correct privileges: reading customers, insert order headers, inserte order lines, update parts, inserts reorder, etc. What now prevents the user (maliciously or not) from directly interacting with the database, outside the application, for example using MSaccess or any other PC data manipulation tool ? Locking the pc environment might be a solution - like removing the query tools, only leaving the application code (I think this is possible with MSaccess). But then, what prevents the user from installing other tools ? Or building other applications ? Onne solution is for the client application and the database server to identify and recognize themselves. Database privileges would then only be granted to the combination (userid, applicationid). The user on its own would only heve minimal (or no) database privileges. Unfortunately, this does not exist today in ODBC. To implement this kind of mechanism, you need to build true client/server applications, based on RPC, messaging, or other solutions. Other than that, the solutions are today to build as many business rules as possible in the database, using constraints, triggers and stored procedures with definer's rights: all the user can do is call a stored procedure. The proicedure has the rights to do the updates. The user him/herslef has no rights at all - except the one to invoke the procedure. -- Albert Godfrind Oracle Rdb Engineering Oracle Corporation INET: godfrind@heron.enet.dec.com DEC European Technical Center Albert.Godfrind@vbo.mts.dec.com BP 027 Voice: [+33] 92.95.51.63 F-06901 Sophia-Antipolis, France FAX: [+33] 92.95.50.50 Expressed views are my own, not the one of my employer.