Re: Security options at different SQL-Databases with ODBC -
From informix-list-owner@rmy.emory.edu Sun Oct 13 08:58:58 1996 Received: from rmy.rmy.emory.edu by hobbes.dbintellect.com via ESMTP (940816.SGI.8.6.9/930416.SGI.AUTO) for <settler@dbintellect.com> id IAA03034; Sun, 13 Oct 1996 08:58:57 -0600 Received: (from ilist@localhost) by rmy.rmy.emory.edu (8.7.1/8.7.1) id KAA10599 for settler@dbintellect.com; Sun, 13 Oct 1996 10:59:25 -0400 (EDT) From: Nils.Myklebust@idg.no (Nils Myklebust) Message-Id: <53qrcr$8dk@nntp.idgonline.no> Subject: Re: Security options at different SQL-Databases with ODBC - who is best? Date: Sun, 13 Oct 1996 12:43:58 GMT Reply-To: Nils.Myklebust@idg.no (Nils Myklebust) Organization: NM Data AS Sender: informix-list-owner@rmy.emory.edu To: informix-list@rmy.emory.edu X-Informix-List-To: settler@dbintellect.com X-Informix-List-Id: <news.29220> Micorsoft seems to believe that free access by everyone to all data in all databases is the best situation for everyone. There is *no* mechanism built into ODBC to protect any data from free manipulation by any user *if* that user has access to some application that can do such updates. If i write a Visual Basic application using the Microsoft recomended way of updating data via ODBC, there is no standard mechanism in ODBC or anywhere else that can stop that user from staring MS Access, connecting to that same database via the same ODBC interface, giving the same username and password used for that application, and then manipulate the data in any way that user wants. As far as I know all this is completely independent of which database is used. This is a bad situation indeed, as you write. The only company I know of that has done anything about this is OpenLink. Check out their whitepapers and manuals at http://www.openlinksw.com I have sent by direct mail a post I made to comp.databases.informix on the 19.07.96 about how we use this. It doesn't give you full security, but it helps. Microsoft and others should be pressured into doing something about this totaly unacceptable situation. Any help in doing this would be much appreciated. This problem is not only related to ODBC. If a user has an application using Informix I-Net or the equivalent "direct" drivers for other databases, there is nothing stoping that user from installing ODBC on his machine and start manipulating the data freely using the same login/password as for his application. volker.koenig@Duesseldorf.netsurf.de (Volker Koenig) wrote: :Hello out there! :As a database professional and journalist I am researching about security :mechanism in the world of SQL-Databases. :I do have experiences with DB2 on serveral platforms and found out that the :"plan-security" offered by DB2 is a very useful thing. In a few words for :those who are not familiar with DB2: Each program that is coded with Embedded :SQL consists of the loadable Module on the client and "packages" (respectively :a plan at DB2/MVS) in the server-database. The packages contain all :information about the SQL-statements. Packages are created from the bindfiles :with the bind-command. At bind-time the server can check whether or not the :user who is actually binding does have all privileges neccessary for all the :statements he wants to put into the plan. :Later at execution time the user can be made independent from having those :appropriate privileges himself, he just needs an EXECUTION-privilege on the :package/plan. :The abuse of packages by "foreign" programs is almost impossible since you :need a very highly skilled programmer for coding all that stuff manually, and :the the integrity between program and package is kept by a timestamp that :references to the precompile-time. :This is, of course, only possible for static SQL. Dynamic statements :(PREPARE/EXECUTE) can't be checked in this manner because the tables accessed :are not known at bind-time. Nevertheless each dynamic statement needs a :package/plan, too. The user needs EXECUTE-privileges for it _and_ the :access-privileges for the table he wants to access. So even query tools like :QMF or environments like SAS and ODBC drivers do have packages/plans inside :the database, but they are only "placeholders" for the statements generated at :execution time. :Unless you leave the mainframe you do not have a problem with this. A user can :update any table from his application without being able to do the same update :from the interactive query tool, because database changes are very unlikely :coded with dynamic SQL. Unless the user has been granted with SELECT privilege :explicitely he does not even have a chance to browse a table he must not :browse. :Now Client/Server is the breakthrough and lots of people are using lots of :ways to create PC-software (mostly Windows 3.1/95/NT) to access databases. :Those applications shall be portable between different SQL-databases, so they :use ODBC. :ODBC does not support static SQL in any way (except for a tool by NeonSoft I :dislike for several reasons). :On the one hand you have "grown" security policies on the mainframe that :essentially depend on the difference between static/dynamic SQL, on the other :hand there is the need to offer C/S-technologie yesterday. :I have heard from several firms which want to sell us their applications that :"only DB2 does have such problems with security". As for the standard SQL :security mechanism I know I must disagree with them because the plan security :DB2 offers is far beyond the standard. :Who ist right? Do "all the other SQL databases" have a security mechanism that :avoids my problem? Which mechanism does your favourite SQL database use to :avoid it? Or do your user have the possibility to access the application's :tables without using the application? :A remark: Certainly DB2 does support creating views with seperate privileges :and triggers and so on, and using hidden accounts on the database server to :access the data is possible, too, but you can spy out all "secret" ODBC-calls :using Software like ODBC-Agent by Leading Edge (this tool gives you even the :database password as plain ASCII). So it is dangerous to call such workarounds :"secure" as long as Lycos knows more than 1,400 links concerning "ODBC" and :"SPY" :Who can answer my question? :Regards, : Volker Koenig. :http://www.Duesseldorf.netsurf,de/~vkoenig/ Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org I concur with Nils - OpenLink offers a nice solution. Another way to go about it is to create roles and grant those roles update access to tables. Then, when an "authorized" application attempts to update tables, it must first execute a SET ROLE command to enable the role privileges. Since casual users will likely not issue the SET ROLE command via MS-Access or some other "unauthorized" application, they are limited to access granted specifically to their user