Name |
abaddltribypasslog |
Code |
ap_dbl |
Creation Date |
31 juli 2013 09:44:44 |
Modification Date |
31 juli 2013 10:41:42 |
Comment |
Logs commands which were bypassed the DDL triggers |
AbaPerls has two DDL triggers, the preventing DDL trigger and the logging DDL trigger.
The preventing DDL trigger prevents casual users from altering objects outside of AbaPerls if they were loaded by AbaPerls originally. (New objects are always a fair game.) It is possible to bypass the trigger, and sometimes this may be required in order to deploy an urgent fix into production. In this case the logging DDL trigger logs the action to this table.
Beware that the logging scheme is mainly aimed to catch the casual user. A user who is dedicated to the task will always be able to bypass both triggers. Here are some different ways to bypass the triggers:
The proper ways:
The less proper ways:
|
|
|
Data |
NOT |
F |
|
dblid |
|
int |
X |
|
Surrogate key. |
|
sysname |
X |
|
Object affected by the action. |
|
|
nvarchar(40) |
X |
|
E.g. ALTER TABLE. |
|
actiontime |
|
datetime |
X |
|
When it occurred |
username |
|
sysname |
X |
|
Who did it. |
|
sysname |
X |
|
The alleged host for the action. |
|
|
varchar(48) |
X |
|
The net address for the client. |
|
|
appname |
X |
|
From which applicaiton. |
|
|
bit |
|
|
How the DDL trigger was bypassed. |
|
|
nvarchar(MAX) |
X |
|
The full command text. |
Name |
Columns |
Options |
PK |
pk_ap_dbl |
dblid | clustered |
X |
The object affected by the action. Taken from the ObjectName field of the eventdata() function. |
The type of command issued. Taken from the filed EventType of the eventdata() function. |
The host name as reported by the host_name() function. Note that this value is taken from the connection string and the user can set it to whatever he likes. |
The IP address (or similar) for the network connection. This is taken from sys.dm_exec_connections, and in difference to host_name(), this values is much more difficult to manipulate for the caller. |
The applicaiton name taken from app_name(). Like host_name() this value is set in the connection string and the user may thus set whatever he likes. |
This column indiciates how the DDL trigger that prevents update was bypassed. There are three possible values:
NULL - The DDL trigger is missing entirely. (But the logging DDL trigger is still there. Obviously.) 0 - The preventing DDL trigger was bypassed in the proper way. 1 - The preventing DDL trigger was disabled at the time.
|
The full command text, taken from TSQLCommand/CommandText of the eventdata() function. |
|
|
|