Product Suggestion: CREATE EVENT TYPE DDLStatement

From time to time questions arise about auditing DDL statements like CREATE TABLE and GRANT, and the answers are frequently the same:

• SQL Anywhere doesn't support triggers on DDL statements, and
  

• you can use SQL Anywhere's built-in audit feature.
  

A common knee-jerk response to Answer Number Two is "Go [redact] yourself!"...

• Auditing puts data in the transaction logs, and
  

• logs are hard to analyze on a timely basis, and besides,
  

• transaction logs are separate files not subject to the natural ACID durability protections offered by any product that calls itself a "database"... transaction logs can be (and often are) deleted at any time.
  

Answer Number One, "SQL Anywhere doesn't do DDL triggers" is actually a red herring, as in "a seemingly plausible, though ultimately irrelevant, diversionary tactic". Sure, SQL Server does DDL triggers but "a DDL trigger and the statement that fires it are run within the same transaction" and that's not what we want here.

In fact, if you're serious about auditing DDL statements you want to audit them whether they succeed or fail... like a security camera that watches the loading dock 24-by-7 whether or not Walt and Jesse are successful stealing that barrel of methylamine.

What We Need

What we need is a CREATE EVENT TYPE DDLStatement that works asynchronously on its own connection, and is fired when a DDL statement has finished executing.

With a corresponding EVENT_PARAMETER() call (or two), of course :)

Plus (maybe) a WHERE EVENT_CONDITION() predicate... or two... or three...

CREATE EVENT e TYPE DDLStatement [ WHERE EVENT_CONDITION ( 'Type' ) = 't' -- very nice to have | WHERE EVENT_CONDITION ( 'Type' ) IN ( 't' [, 't'] ... -- nice to have | WHERE EVENT_CONDITION ( 'StatementText' ) LIKE 'string' ] -- would be way cool HANDLER BEGIN ... ... EVENT_PARAMETER ( 'StatementText' ) ... -- required... pointless without this ... ... EVENT_PARAMETER ( 'SQLCODE' ) ... -- very nice to have ... END;

A restricted list of 't' values might be provided; e.g., 'grant' for all forms of the GRANT statement, and 'create table' for all forms of DDL that create rows in ISYSTAB including views.

The EVENT_PARAMETER and EVENT_CONDITION ( 'StatementText' ) calls would return whatever the server processed, not necessarily what the client sent... this is an audit tool, not a documentation or debugging tool (although it would be invaluable for both purposes :)

---