abaddltribypasslog (ap_dbl)

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


Table description

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:

 

  • Start a transaction, create a temp table #abaperls with the column objname (of type sysname), and insert the name of the object to modify into this table. Note that the temp table must be created inside the transaction.

  • CREATE commands are always permitted. (Because if the object is missing, it cannot be wrong to create it.)

  • For a development database (i.e. a database not marked TEST or PROD), ALTER TABLE is always permitted.

 

The less proper ways:

  • Disable the preventing trigger (but the action will still be logged, as long as the logging trigger is enabled). Note that when you connect with AbaPerls, AbaPerls will always enable the triggers if they are present.

  • Perform an action which does not fire DDL triggers in SQL Server. They are not very many, but among them are ENABLE and DISABLE TRIGGER (which obviously has quite serious repercussions). In SQL 2005, this also applies to sp_rename, sp_bindrule and sp_bindefault. Such an action will obviously not be logged.

  • Dress up exactly like AbaPerls. AbaPerls own actions are never logged, and there is no way to prevent a malicious user from pretending to be AbaPerls.


Column list


Column


Domain

Data
Type

NOT
NULL

F
K


Comment

dblid

 

int

X

 

Surrogate key.

objname

 

sysname

X

 

Object affected by the action.

action

 

nvarchar(40)

X

 

E.g. ALTER TABLE.

actiontime

 

datetime

X

 

When it occurred

username

 

sysname

X

 

Who did it.

hostname

 

sysname

X

 

The alleged host for the action.

client_net_address

 

varchar(48)

X

 

The net address for the client.

appname

 

appname

X

 

From which applicaiton.

ddltri1_disabled

 

bit

 

 

How the DDL trigger was bypassed.

command

 

nvarchar(MAX)

X

 

The full command text.


Keys

Name

Columns

Options

PK

pk_ap_dbl

dblid clustered

X


objname

The object affected by the action. Taken from the ObjectName field of the eventdata() function.


action

The type of command issued. Taken from the filed EventType of the eventdata() function.


hostname

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.


client_net_address

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.


appname

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.


ddltri1_disabled

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.

 


command

The full command text, taken from TSQLCommand/CommandText of the eventdata() function.