Overview
What is a malicious trigger? What can it do? How can you protect yourself and your database server?
Background
I've been reading the Microsoft Press MCITP training book for the Microsoft 70-441 exam: Designing Database Solutions by Using Microsoft SQL Server 2005 and I came across an interesting comment about trigger security. I'm describing it here, because it is quite easy for a malicious database developer to use against an unsuspecting DBA.
You can find the book here:
http://www.amazon.com/MCITP-Self-Paced-Training-Exam-70-441/dp/0735623422/ref=sr_1_1?ie=UTF8&s=books&qid=1199445450&sr=1-1
ISBN-13: 978-0-7356-2342-2
ISBN-10: 0-7356-2342-2
Context and issue
In SQL Server, triggers execute using the user context of the person that initiated the statement (DML or DDL). This user may not be the user that created the trigger and herein lies the danger.
Triggers can include most SQL commands including GRANT statements and database configuration statements, such as creating a login, elevating server-wide permissions and installing custom .NET assemblies.
Are you worried yet?!
Exploit
A malicious database developer creates a trigger on a table he/she owns. He/she adds malicious code into the body of the trigger which he/she normally could not execute.
Next, the developer requests assistance from the DBA, stating trouble performing a particular DML or DDL statement.
The unsuspecting DBA tries the statement using his/her DBA account, if necessary fixing the syntactic mistakes in the developer's suggestion.
The trigger is executed using the DBA's user context and succeeds to change permissions or in performing other malicious activities.
Workaround
There are several workarounds:
- Don't give your developers full DBO rights. Instead, give them specific permissions. Require that an authorised DBA creates any necessary triggers
- Don't execute statements against a non-system database using a privileged account. Test using an account with the same permissions as the user, or the actual user's account
- Review the existence of triggers using the sys.triggers and sys.server_triggers catalogue views. Check the code using the sys.sql_modules catalogue view
Versions
Metadata