Re: How to prohibit certain SQL operations ?

>From: kudrass@dvs1.informatik.th-darmstadt.de (Thomas Kudrass) >Subject: How to prohibit certain SQL operations ? >Date: 6 Jun 1994 18:00:42 GMT >X-Informix-List-Id: <news.7066> > >Hallo everywhere, > >I'm looking for a good way to prohibit certain operations, e.g. I don't >want that a table is manipulated by an UPDATE statement. But the triggers >(available since version 5.0.1) don't allow me to rollback a SQL >statement, because the transaction concept is only available in the >embedding SQL programming linke ESQL/C. When using the BEFORE clause of >the Trigger the triggering action (e.g. my bad update) has already >occured, this is not desirable. > >Any ideas from the Informix community ? I have a logged database called WTS, which contains the stored procedures wtsdba_del and wtsdba_upd which stop even a DBA like me from updating the tables in question. The messages I got looked like: SQL[23]: insert into pts_bug values (-1, 1, 1, 1, "4GL", "TST", "TST", > "johnl", 'C', 'johnl', current, 'johnl', current); SQL[24]: update pts_bug set environment_id = 2; SQL -746: You may not update table PTS_Bug ISAM -273: No UPDATE permission. SQL[25]: delete from pts_bug; SQL -746: You may not delete from table PTS_Bug ISAM -274: No DELETE permission. SQL[26]: Note that the UPDATE or DELETE is not successful because the triggered statement raises an exception. Error -746 is specifically for user-defined error messages. I used the ISAM error part to give some more information. This seems to meet your requirements. I assume it works under SE until someone demonstrates to the contrary. I tested it under 5.02 OnLine. I believe it will work in both logged and unlogged databases. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> =========================================================================== -- %W% %E% -- %Z%Informix R&D Work Tracking System (WTS) -- %Z%Create Procedure WTSDBA_DEL -- %Z%Author: JL CREATE PROCEDURE wtsdba_del(tab VARCHAR(18)) DEFINE msg VARCHAR(40); IF USER != 'wtsdba' THEN LET msg = "You may not delete from table " || tab; INSERT INTO FailedOpLog(TabName, Operation) VALUES(tab, 'D'); RAISE EXCEPTION -746, -274, msg; END IF END PROCEDURE DOCUMENT "Only WTSDBA may delete rows from most WTS tables.", "PROCEDURE wtsdba_del() enforces this constraint, even against DBAs"; =========================================================================== -- %W% %E% -- %Z%Informix R&D Work Tracking System (WTS) -- %Z%Create Procedure WTSDBA_DEL -- %Z%Author: JL -- %W% %E% -- %Z%Informix R&D Work Tracking System (WTS) -- %Z%Create table PTS_BUG -- %Z%Author: JL CREATE TABLE 'wtsdba'.PTS_Bug ( BugID INTEGER NOT NULL PRIMARY KEY CONSTRAINT 'wtsdba'.PK_PTS_Bug, Bug_Num INTEGER NOT NULL, Family SMALLINT NOT NULL, Environment_ID INTEGER NOT NULL, Product CHAR(8) NOT NULL, Assertion CHAR(3) NOT NULL, SubAssertion CHAR(3) NOT NULL, Owner CHAR(8) NOT NULL, PTS_Status CHAR(1) DEFAULT 'C' NOT NULL -- Is this Bug current or obsolete according to PTS? CHECK (PTS_Status IN ('C', 'O')) CONSTRAINT 'wtsdba'.C1_PTS_Bug, New_User CHAR(8) DEFAULT USER NOT NULL, New_Time DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND NOT NULL, Upd_User CHAR(8) DEFAULT USER NOT NULL, Upd_Time DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND NOT NULL, UNIQUE (Bug_Num, Family, Environment_ID) CONSTRAINT 'wtsdba'.AK_PTS_Bug ); REVOKE ALL ON 'wtsdba'.PTS_Bug FROM PUBLIC AS 'wtsdba'; GRANT SELECT ON 'wtsdba'.PTS_Bug TO PUBLIC AS 'wtsdba'; CREATE TRIGGER 'wtsdbs'.U_PTS_Bug UPDATE ON PTS_Bug BEFORE (EXECUTE PROCEDURE wtsdba_upd('PTS_Bug')); CREATE TRIGGER 'wtsdbs'.D_PTS_Bug DELETE ON PTS_Bug BEFORE (EXECUTE PROCEDURE wtsdba_del('PTS_Bug'));