RE:Database Security

From: Nils.Myklebust@idg.no (Nils Myklebust) Message-Id: <542uit$g64@nntp.idgonline.no> Subject: Re: Security options at different SQL-Databases with ODBC - who is best ? Date: Wed, 16 Oct 1996 14:27:35 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.29325> Status: R guest wrote: You might want to tell us who you are. In the meantime I'll take your argument at face value. They are wrong and bad. I thought I explained the issues well enough below, and it has also been discussed enough not that long ago. However the issue is: 1. I develop a client application that can do updates to the server database via sql. (Not stored procedures.) It runs on a client computer (MS Windows in our case, but it could be another OS. Even Unix.) Nils.Myklebust@idg.no (Nils Myklebust) wrote: ...snip >2. The user has a login name and password used to run the application. >This login name is defined to the database and to be able to run the >application this user must have the necessary rights so the >application can do the updates to the database. Usually update, insert >and delete to one or more tables and/or columns in the server >database. > >3. The user has (or installs) ODBC on his PC. Starts MS Access. >Selects the menu option to connect to one of the tables the >application in 1 above updates. He is asked for a loginname and >password. He supplies the same as the one he gives when running the >application. Voila - he gets the table displayed in a grid on his >screen. > >4. The user can now go to any column/row, type anyting and the table >is autmagically updated in the serve database. ...snip This is certainly true if you grant update privileges specifically to that user. However, using version 7.1 feature enhancements, you can prevent this. First, revoke the update privileges from the user and grant him or her only select privileges. These become their default privileges. Next, create a role (CREATE ROLE DATA_ADMIN) and grant update privileges to the role (GRANT UPDATE ON tbl TO DATA_ADMIN). Finally, modify the "custom" application to issue a SET ROLE DATA_ADMIN after connecting to the database using the loginname. Now the user has update privileges, but these go away once the connection is broken. You can keep the name of the role secret so that the user cannot invoke update privileges through a pass-through SET ROLE command. ------------------------------------------------------------------- Jeff Craig | dbINTELLECT Technologies, Inc. Senior Consultant | Golden, CO | (303) 275-6954 ext. 8008 | email: settler@dbintellect.com | fax: (303) 275-2134 -------------------------------------------------------------------