Explain the security implications of triggers for SQL Server databases

Trigger Permissions and Ownership

CREATE TRIGGER permissions are granted by default to the table owner that defines the trigger, members of the sysadmin fixed server role, and members of the db_owner and db_ddladmin fixed database roles, and are not transferable.

The environment you need

This article requires an environment that has acquired one of the above SQL Server permissions, in order to leave a hidden backdoor and not be discovered by the administrator. Even if found is also encrypted (can be cracked, but some administrators do not understand, and will not notice, the relevant information under Google).

Triggers are stored procedures that are executed automatically when a table is inserted (insert), updated (update), or deleted (delete). The most common use for performing sensitive data operations is to make historical records.

This article takes the dynamic Network forum Dvbbs As an example, we have got the db_owner privilege (note: Not to say Dvbbs itself has a loophole). Because only db_owner permission, so the reader wants to carry out "xp_cmdshell", no longer the scope of this article, I believe that after reading this article, as long as there is System permissions, as a system of the back door is also simple. Think first about what we do and what we care about when we use the database.

Why use triggers as backdoor

The administrator will first execute the SQL file, and then import the contents of the MDB, usually use the backup at most, restore under. Usually no one looks at the contents of the trigger, either to see that the trigger can use the command "exec sp_helptrigger ' dv_admin", or to select "Manage Triggers" in Enterprise Manager. Because the net does not use the trigger, also does not mention the trigger, so the network administrator will not go to see. So what we write in it is relatively safe.

Ideas

Triggers are primarily used to make history, and of course you can change the administrator's password and add a user's history. Put it in a place that an administrator usually doesn't notice, and the average user can see it.

Dynamic network Password has MD5, the encryption of the operation of the ASP program in the server to complete, and so on the database to get the data has been added to the secret. But the network at the same time the password in the clear way into the Dv_log table, it gives us convenience. Just get the contents of the L_content field in the Dv_log table, and then determine if the administrator is performing sensitive operations, the back door mentality is formed.

Use process-code resolution

Code snippets: Creating triggers.

create trigger dv_admin_history
on Dv_log
with encryption
for insertas
as

Triggers need to be built on the Dv_log table, where the plaintext password is placed. We do not know how long the administrator password settings, can only be a password inside the contents of the field to remove all. The trigger is best encrypted, and after encryption, the administrator does not know what is here, even when they see it. Executes a trigger when insert (join) data.

The value taken out should be placed in a place that a normal user can see, so that the password can be seen as long as the normal user has permission. In a dynamic network database, field administrators who are the largest and can hold data are usually seen. So you have to find a place where an administrator won't look, and other users won't notice.