RE: Security and the ODBC

Tim Kelly wrote: > Ok, quick one. I have a Informix 7.x server running with a database. = The > users connect to it via Informix-CLI client from within Win95 using a > complied program. The complied program handles the "business rules" of = the > database as far as insuring they have security etc. Simple example: > One table they are allowed to insert rows and delete etc. But, the > application checks a column called rsrc_id and doesn't allow them to = delete > or change a row if it isn't theirs (the rsrc_id is users name). It = works > fine. Remember this is just an example and I'm not wanting to know = about > Triggers, SPL etc. > The issue is this: The CLI client is an ODBC driver. The user can use > any ODBC application and point it to my Informix Server. Example being > MS-Access. Now MS-Access (along with others) can just do what ever it > wants to my data. In the above example the user is allowed to delete = from > the table and the application is ensuring the rsrc_id matches. So, is > there a way to tell Informix to accept connections based on an = application? > Any ideas other then writing a billion triggers, SPL etc etc? Tim: I don't use ODBC, but here's a wild thought. If you have a single application which performs the deletes, and any other = type of connection should not, you might be able to conceal the = functionality through the following: REVOKE INSERT, UPDATE, DELETE ON tab_x FROM PUBLIC;CREATE ROLE "tabx_mnt"; GRANT INSERT, UPDATE, DELETE ON tab_x TO "tabx_mnt";GRANT "tabx_mnt" TO PUBLIC; Then add to your compiled app the following: SET ROLE tabx_mnt; This will only allow updates to the table from within the context of the = ROLE. Users who come in via some other ODBC setup will not have the = appropriate privileges. If you set up the update privileges on all your = modifiable tables in this way, it would make maintenance a hell of a lot = simpler than SPL/Triggers etc. Generally, the fact that the SET ROLE statement must be explicitly used in = an application for that user to obtain the appropriate privileges is = considered a weakness of the functionality, but in your case it might just = be a strength! I guess it all boils down to how cunning your users are, and how much = knowledge about the DB they have, and how easy it is for them to get it. = If they are too smart for their own good, you might be forced to build = something more complicated/robust. Hope this helps, RET +--------------------------------------------------------------------------= --+ | Richard Thomas (DBA) richard_thomas@yes.optus.com.au +61 2 9342 = 7188 | +--------------------------------------------------------------------------= --+