Tuesday, February 25, 2014

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!"...
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 :)


1 comment:

Jason said...

This seems like a good idea at first glance. I will pass it on to the big brains and see what they think.