Re: Protecting triggers?
John Hardin wrote: > I don't see any explicit REVOKE syntax for preventing users from > dropping a database trigger. > > How can you restrict DROP TRIGGER privileges? Is that perhaps an > undocumented side-effect of ALTER TABLE privileges? What should be the case, as with all objects, is that only the object creator or a DBA (or informix) can drop the trigger. Other RESOURCE privileged users should not be able to drop your trigger; CONNECT privileged users should not be able to drop (or create) any trigger, or any other durable database object (temp tables are allowed). A more subtle question is "who can create a trigger on a table". As before, CONNECT privileged users should not be able to do it - full stop. Equally, DBA privileged users (and informix) and the table owner should be create triggers on a table. The fuzzy area is RESOURCE privileged users - can they create triggers on someone else's table, and if so, which granted permission do they need to do so. Fortunately, the SQL Syntax manual tells us the answer (CREATE TRIGGER, on p2-259 in the 11.50 version): To create a trigger on a table or a view, you must own the table or view, or have DBA privilege. For the relationship between the privileges of the trigger owner and those of other users, see “Privileges to Execute Trigger Actions” on page 2-282. So, a user with RESOURCE privileges can only create triggers on tables they own. Users with DBA privileges are not constrained. Is that a problem? How many users have you given DBA privilege to? Did that include PUBLIC? If so, please think again - and remove DBA privilege from PUBLIC immediately. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0229 -- http://dbi.perl.org/ publictimestamp.org/ptb/PTB-7125 tiger 2009-09-12 15:00:04 3DA18AF1D555025EB4C368FAF0FFAA84FCAA981204EEC9B1
"Jonathan Leffler" <jleffler@earthlink.net> wrote: > John Hardin wrote: >> I don't see any explicit REVOKE syntax for preventing users from >> dropping a database trigger. >> >> How can you restrict DROP TRIGGER privileges? Is that perhaps an >> undocumented side-effect of ALTER TABLE privileges? > > What should be the case, as with all objects, is that only the object > creator or a DBA (or informix) can drop the trigger. Other RESOURCE > privileged users should not be able to drop your trigger; CONNECT > privileged users should not be able to drop (or create) any trigger, or > any other durable database object (temp tables are allowed). Okay, good. Thanks. I will try that out and verify. > A more subtle question is "who can create a trigger on a table". That's less of a concern for us - our problem domain is auditing access, and we want to prevent bypass of an auditing trigger. > So, a user with RESOURCE privileges can only create triggers on tables > they own. Users with DBA privileges are not constrained. > > Is that a problem? How many users have you given DBA privilege to? Did > that include PUBLIC? If so, please think again - and remove DBA > privilege from PUBLIC immediately. No, we stopped doing GRANT DBA TO PUBLIC ages ago. -- John Hardin KA7OHZ Senior Applications Developer, BI Specialist EPICOR Retail web: http://www.epicor.com voice: (425) 245-1800 fax: (425) 670-1810 email: <jhardin@epicor.com> 20818 44th Ave. W., Suite 270 Lynnwood, WA 98036 USA Worldwide Headquarters 18200 Von Karman, Suite 1000, Irvine CA 92612 USA ------------------------------------------------------------------------ The first time I saw a bagpipe, I thought the player was torturing an octopus. I was amazed they could scream so loudly. ------------------------------------------------------------------------
"Jonathan Leffler" <jleffler@earthlink.net> wrote: > John Hardin wrote: > >> How can you restrict DROP TRIGGER privileges? Is that perhaps an >> undocumented side-effect of ALTER TABLE privileges? > > What should be the case, as with all objects, is that only the object > creator or a DBA (or informix) can drop the trigger. That's indeed how it behaves, thanks. One more thing: how do you tell on-audit to audit disable and enable of triggers? I don't see an action code for SET TRIGGERS. Would you audit SET OBJECT MODE (STOM) ? -- John Hardin KA7OHZ Senior Applications Developer, BI Specialist EPICOR Retail web: http://www.epicor.com voice: (425) 245-1800 fax: (425) 670-1810 email: <jhardin@epicor.com> 20818 44th Ave. W., Suite 270 Lynnwood, WA 98036 USA Worldwide Headquarters 18200 Von Karman, Suite 1000, Irvine CA 92612 USA ------------------------------------------------------------------------ The first time I saw a bagpipe, I thought the player was torturing an octopus. I was amazed they could scream so loudly. ------------------------------------------------------------------------
John Hardin wrote: > "Jonathan Leffler" <jleffler@earthlink.net> wrote: >> John Hardin wrote: >> >>> How can you restrict DROP TRIGGER privileges? Is that perhaps an >>> undocumented side-effect of ALTER TABLE privileges? >> >> What should be the case, as with all objects, is that only the object >> creator or a DBA (or informix) can drop the trigger. > > That's indeed how it behaves, thanks. > > One more thing: how do you tell on-audit to audit disable and enable of > triggers? I don't see an action code for SET TRIGGERS. Would you audit > SET OBJECT MODE (STOM) ? > Yes: http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sec.doc/ids_au_104.htm?resultof=%22%61%75%64%69%74%22%20%22%6d%6e%65%6d%6f%6e%69%63%73%22%20%22%6d%6e%65%6d%6f%6e%22%20 Regards.