Re: Permissions to allow users to drop tables created by others

Posted in 2006

Topics: Stored Procedures & SPL, Server Administration, Security, Permissions & Auditing

Jonathan Leffler — Sat, 21 Oct 2006 04:06:26 GMT — source: Usenet: comp.databases.informix

bondsfis wrote: > We have been told by Sarbanes Oxley to tighten up our database permissions > (ie revoke DBA from public !) Well, that's a start - but leaving everyone with RESOURCE permissions isn't that much of an improvement. It certainly isn't desirable. > and I have granted resource to all users for > each individual database. I have also granted ALL on each table in each > database to public. My problem now is that the application is trying to > drop a table for one user, but the table was created by another user. Can > anyone tell me if there is any other way to get this to work other than > setting up each user as a DBA ? It depends on whether you can alter the application or not. Really and truly, the application should not be futzing around creating and dropping permanent tables all the time. The ordinary users should be able to run with CONNECT privilege and no more. Can you arrange for the application to invoke a stored procedure to drop and rebuild the table? If so, a DBA-privileged stored procedure should do the trick. If, as I suspect, you can't change the application or get it changed, then I think your application is intrinsically opposed to SOX compliance. Probably time to look at replacing it with another that is more orthodox in its design. Sorry, but requiring all users to have DBA privileges is tantamount to saying "security does not matter one jot". -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

↪ replying to Jonathan Leffler

bondsfis — Mon, 23 Oct 2006 10:06:51 -0400 — source: Usenet: comp.databases.informix

I'm afraid that the application can't be changed. It's a bit of archaic monster which still uses C-ISAM files (in the 21st century !). Replacing it is a bit of a non-starter also, as our company prides itself on how little it spends on IT. I've decided that the only way around this is to give everybody DBA privilege - at least the databases are now tightened up to only allow specified users to access them (the users cannot get to a command line and can only do things that the application will allow them to do). Hopefully this will satisfy the auditors !