Re: Increase Security

>From: cherylk@spamis.jcdc.doleta.gov >Date: Tue, 24 Oct 1995 16:39:36 -0500 (CDT) >X-Informix-List-Id: <list.7766> > >The best approach to me: "GRANT CONNECT to PUBLIC" and then GRANT SELECT >on table-xyz to PUBLIC for all tables. This will allow anyone to read data. >Only the users who need to update data need INSERT or UPDATE. Those who >need to create tables need RESOURCE to the data base vs CONNECT. Those who >are DBA should be the only one's with DBA authority. > >On 24 Oct 1995, Brian O'Leary wrote: >} Our database is Informix sql 3.2 running on SCO 3.2.2 >} >} The database and all tables are currently set to "GRANT DBA to PUBLIC" >} and has been fine when adding new users especially as they are >} restricted by menu access to 4gl programs. I am currently opening up the >} systems to make use of mail and shell programs for all users, but I am >} concerned that the databases could be left open to meddling and >} corruption. >} >} What is the best way to open the system, keep the flexibility for new >} user additions but maintain security for the database. I agree with the basic idea of not granting anything other than SELECT permission on tables by default. However, unless the database is a MODE ANSI database, all the tables are created with 'GRANT ALL ON TableName TO PUBLIC' anyway, so the 'GRANT SELECT ON TableName TO PUBLIC' does not prevent modifications to the table unless you do 'REVOKE ALL ON TableName FROM PUBLIC' first. This is what I do; it is also what dbexport does. I would never grant DBA permission to public on any database that contains anything remotely important in the way of data. I have only ever done that on Stores databases and scratch or test databases. I also agree with the basic philosophy of not granting anything other than CONNECT permission to those who don't need it, but I usually don't grant CONNECT privilege to PUBLIC either; only specified users get privileges on the database. This is adminstrator-intensive if the user population is dynamic. I generally use a (complex) system of tables to describe the privileges needed to use certain programs on the database, and these are granted automatically when a DBA grants some new user privileges to use a particular program. Again, more work, but more security. The fundamental problem with any security system in SQL-based systems is that if UserA needs UPDATE privilege on TableB when using ProgramC, it is nearly impossible to prevent that user from updating TableB via DB-Access or ISQL as well. DBA-privileged stored procedures almost work -- but what happens if the user crashes the program which arranged for the privileges to be granted? Answer -- the privileges stay granted. The only possible exception might be if the database has transactions and the privileges are granted within an uncomitted transaction, but then do the privileges take effect for some other process; I doubt it. I don't have a solution for this problem. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>