Re: Increase Security

>Date: Tue, 14 Nov 1995 13:56:41 -0800 >From: dasidwel@us.oracle.com (David Sidwell) > >(A copy of this message has also been posted to the following newsgroups: >comp.databases.informix) > >In article <46k6fg$571@cssun.mathcs.emory.edu>, johnl@informix.com >(Jonathan Leffler) wrote: > >> >From: cherylk@spamis.jcdc.doleta.gov >> >Date: Tue, 24 Oct 1995 20:00:31 -0500 (CDT) >> >> >On Tue, 24 Oct 1995, Jonathan Leffler wrote: >> >> [...] >> >> 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. >> >> >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. >> >> You missed my point -- what I said applies to any (ANY) SQL-based system >> which uses just SQL privileges. Roles do not affect this. If I can do SET >> ROLE using a program which I am supposed to be able to use, I can also do >> it using the interactive facility of my choice. And this, too, applies to >> any (ANY) SQL-based system in the absence of non-SQL controls. > >You missed his - or someone else's point. This is in danger of getting silly, but I'm not sure which point I've missed. I recognise that the standard SQL based system of privileges means that if an Authorisation Identifier (AuthID) X is allowed to DELETE rows in TableA, then whenever AuthID X is the relevant AuthID, the program which is at work can delete rows from TableA, regardless of whether that program is the one which was intended to do the work or not. For better or worse, Informix treats the Unix login name as the AuthID, so any Informix program run by AuthID X can therefore delete rows from TableA; one of the 'any Informix program's is ISQL, so if X can delete rows from TableA using an application AppB, then X can also delete rows from TableA using ISQL. I don't think the SQL standard says anything about how AuthIDs are associated with a particular program run by a particular user because there is little or no commonality between the user concepts on Unix, IBM mainframe systems, DOS, Windows, etc. >Also, what you say does not necessarily hold true for ANY SQL-based >system. (Unless ANY-SQL is a trademark of Informix ?). ANY-SQL is not a trademark of Informix, to the best of my knowledge. Clearly, I should have been utterly pedantic and said something about any SQL-based system which does not have non-standard SQL extensions to the GRANT/REVOKE mechanisms to control access to the database. >The way database roles and privileges work in Oracle - crashing a program >which has raised a password protected role does not affect any other >session's privileges. Yes the privileges stay granted - to the role ! >The role is not raised in any session unless it is explicitly raised with >the correct role password. SQL-92 does not mention passwords in the privileges sections, so Oracle is not using standard SQL privileges when handling things via 'password protected roles'. That said, I am using as an authority a book which doesn't mention ROLE, so either SET ROLE is a non-standard extension, or is not part of SQL-92, or is missing from 'Understanding the new SQL: a complete guide' by J Melton & A R Simon (ISBN 0-55860-245-3), and I don't know which of these alternatives applies. >I am not an advocate of securing access based on client access path, >although with Oracle7's role functionality this is possible. The problem >comes in keeping the role password secret - and yet still allowing the >application to know or obtain it. There are, however, techniques for >achieving this. David, Please could you enlighten me about these techniques for enforcing the access controls based on which application is doing the access (which is how I translate 'based on client access path')? Also, please would you explain why you do not think it is a good idea to base access controls on the client access path? Fortunately, in one of my postings in this sequence, I noted: >> Unless someone else knows better, of course. It appears that someone does know better:-) Thanks, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>