Re: Increase Security

That last par from Jonathan is the whole problem with Informix DB security (do you hear me Informix?). Hopefully the ROLES permission in Informix 7.1 address this. I will be finding out in a few months. I also want to mention the dbschema with -p "all" or "user_id" with the will also create a schema of the tables in the database or table requested with the permission statements. Check out dbschema fully by doing a dbschema -- on your system. It will save a lot of unneeded DBA typing. On Tue, 24 Oct 1995, Jonathan Leffler wrote: } >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> }