DDL triggers in Azure SQL Database
This article discusses database DDL triggers for auditing Data Definition Language (DDL) on Azure SQL Database.
For securing your database infrastructure, a database audit is vital. Azure SQL Database is a SQL Server PaaS database solution. It has a built-in server and database audit that you can enable and adjust to meet your needs. DDL triggers, on the other hand, are critical in the audit. Assume you have a mission-critical Azure production database for which you want to log all CREATE, ALTER, and DROP table statements. In another situation, you don’t want any user who isn’t a member of the db owner role to DROP the table. In this scenario, SQL Server DDL triggers are critical in preventing unauthorized database access. Can use these triggers to complete the following tasks.
- Prevent specific database schema changes.
- Should audit changes to the database schema.
DDL triggers operate on DDL events. These events correspond to the CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS T-SQL statements.
It is difficult to respond to suspicious actions in a conventional database without an auditing tool in a controlled and secure database environment. As a DBA, you must respond to management and find solutions to the following questions:
- Who threw the table away?
- When will the table be lowered?
EVENTDATA() built-in functions
The EVENTDATA() function in the DDL triggers captures the details for a DDL trigger in an XML format. The following data is included in the XML:
- Event timestamp SPID of the connection in which the performed query triggers the DDL trigger
- Information about the event
In XML, it returns the following information:
| XML column | Details |
| EventType | It gives the type of event that caused the DDL trigger. For example, CREATE, ALTER, DROP. |
| PostTime | Event timestamp |
| SPID | Session ID |
| ServerName | SQL Server instance name |
| LoginName | It is the login details for the SPID |
| UserName | |
| DatabaseName | It is the database name in which the DDL trigger is fired. |
| ObjectName | Object name such as table name, schema name |
| ObjectType | Type of the object such as Table, View, Stored procedure. |
| TSQLCommand | The Eventdata captures the TSQL command fired the DDL trigger |
| SetOptions | SET options used in the connection |
| CommandText | It gives the create, alter or drop command. |
Let’s construct a table in the Azure SQL Database with the following script to capture the event data using a DDL trigger.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
CREATE TABLE dbo.CaptureDDLEvents
(
EventDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
EventType NVARCHAR(100),
EventDDL NVARCHAR(MAX),
EventXML XML,
DatabaseName NVARCHAR(255),
SchemaName NVARCHAR(255),
ObjectName NVARCHAR(255),
HostName VARCHAR(64),
IPAddress VARCHAR(48),
ProgramName NVARCHAR(255),
LoginName NVARCHAR(255)
);
|
The following script provides a DDL trigger for CREATE TABLE, ALTER TABLE, and DROP TABLE statements on the Azure SQL Database. The trigger inserts data into the [CaptureDDLEvents] table using the EVENTDATA() function.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
CREATE TRIGGER AzureSQL_CAD_Trigger
ON DATABASE
FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@EventData XML = EVENTDATA();
DECLARE @ip varchar(48) = CONVERT(varchar(48),
CONNECTIONPROPERTY(‘client_net_address’));
INSERT Azuredemodatabase.dbo.CaptureDDLEvents
(
EventType,
EventDDL,
EventXML,
DatabaseName,
SchemaName,
ObjectName,
HostName,
IPAddress,
ProgramName,
LoginName
)
SELECT
@EventData.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘NVARCHAR(100)’),
@EventData.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘NVARCHAR(MAX)’),
@EventData,
DB_NAME(),
@EventData.value(‘(/EVENT_INSTANCE/SchemaName)[1]’, ‘NVARCHAR(255)’),
@EventData.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘NVARCHAR(255)’),
HOST_NAME(),
@ip,
PROGRAM_NAME(),
SUSER_SNAME();
END
GO
|
Let’s construct a new table in the Azure SQL Database to test the DDL trigger. The DDL trigger should be fired and insert the captured data into the [CaptureDDLEvents] table.
Then look for the entry in the [CaptureDDLEvents] table to receive the collected data in XML format.
The [EventXML] column has a hyperlink that leads to more information.
Let’s take a look at the information we’ve gathered.
| XML column | Captured data | Description |
| EventType | CREATE_TABLE | For CREATE TABLE statement, it gives the CREATE_TABLE event. |
| PostTime | 2021-03-05T11:32:43.897 | It is the event timestamp |
| SPID | 58 | It is the SPID under which the CREATE TABLE statement was executed. |
| ServerName | Azuredemosqldemo | It is my Azure SQL Database logical server name. |
| LoginName | Sqladmin | It is the SQL login name through which we authenticated to Azure SQL Database. |
| DatabaseName | Azuredemodatabase | It is the Azure SQL Database name. |
| SchemaName | Dbo | It is the database schema name. |
| ObjectName | MyDemoTable | It is the SQL table name that we created using the CREATE TABLE statement. |
| ObjectType | TABLE | The object type refers to which object is affected, for example, table, procedure. |
| TSQLCommand | T-SQL script | The TSQL command refers to the SQL script along with the SET options. This column helps track the script that the user used for object creation, removal or modification. |
DDL triggers have been set up for both CREATE and DROP table statements. As a result, let’s run a DROP TABLE transaction and record the information. As we can see, the user sqladmin deleted the table [MyDemoTable] from the [AzureDemoDatabase] Azure SQL Database.
As we saw previously, DDL triggers can assist in auditing database activity events and determining the answers to the questions – Who did it? When did it take place? Which script was run?
But what if we wish to limit the operations we can perform? For instance, let’s say we don’t want anyone to drop the table. We specify a message for the user and rollback the transaction in this DDL trigger.
|
1
2
3
4
5
6
|
CREATE TRIGGER DDL_DoNotDropSQLTable
ON DATABASE
FOR DROP_TABLE
AS
PRINT ‘Dropping a table is not allowed in the [AzureDemoDatabase] Azure SQL database’
ROLLBACK
|
Try dropping an existing table to test the DDL trigger functionality. As described in the trigger body, you receive an error notice. The user has the db owner permission to drop the table, but the DDL trigger protected you.
If we wish to drop the table in this situation, we must first disable the DDL trigger, then drop the table and enable it again.
|
1
2
3
4
5
6
7
8
|
DISABLE TRIGGER DDL_DoNotDropSQLTable
ON DATABASE
GO
DROP TABLE student
GO
ENABLE TRIGGER PreventDropTable
ON DATABASE
GO
|
Multiple users connect to your database in a standard database setting. In the example above, no one is allowed to delete the table from the Azure database. However, in an ideal situation, we would not want to restrict someone who has the db owner access. We create a DDL trigger in the script below that checks if the user belongs to the db owner permission group. It prints a notification and rolls back the transaction if the user does not belong to the db owner group. A trade for the user with the db owner permission, on the other hand, skips the IF block and drops the table successfully.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
CREATE TRIGGER DDL_DoNotDropDBTable
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
IF IS_MEMBER (‘db_owner’) = 0
BEGIN
PRINT ‘Dropping a table is not allowed in the [AzureDemoDatabase] Azure SQL database’
ROLLBACK TRANSACTION;
END
END
|
To test our DDL trigger logic, let’s create a SQL login in the Azure SQL Database and give it the db datareader, db datawriter, and db ddladmin rights. The db ddladmin permission group allows users to create, delete, and modify items in the SQL Database.
|
1
2
3
4
5
6
7
8
9
|
CREATE LOGIN appdataread
WITH PASSWORD = ‘P@ssw0rd5’
GO
CREATE USER appdataread FROM LOGIN appdataread;
ALTER ROLE db_datareader ADD MEMBER appdataread;
ALTER ROLE db_datawriter ADD MEMBER appdataread;
ALTER ROLE db_ddladmin ADD MEMBER appdataread;
|
Use [appdataread] login credentials to connect to the Azure SQL DB.
As shown below, in the choice, type the name of the Azure SQL DB.
Drop any existing tables once you’ve been joined. It won’t let us drop it and instead displays a message within the DDL trigger body. Db owner permission isn’t granted to this user. As a result, despite having db ddladmin permission, he cannot drop the table.
Now, try dropping the table with the user who has db owner permission.
|
1
2
3
4
5
6
7
8
9
10
11
|
SELECT USER_NAME() AS UserName
GO
DECLARE @i int
set @i= IS_MEMBER (‘db_owner’)
SELECT
CASE
when @i=1 then ‘Yes’
else ‘No’
end as DBOwner
go
DROP TABLE dbo.t2
|
In the Azure SQL Database, the user has the db owner’s permission. As a result, the DDL trigger does not roll back the transaction, and the table is successfully dropped.
Conclusion
This post looked at Azure SQL Database DDL triggers for auditing and restricting users from doing particular operations like DROP TABLE. It’s good to set up a database-level audit for a crucial production database to protect it from unauthorized access.
Enteros
About Enteros
IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.
The views expressed on this blog are those of the author and do not necessarily reflect the opinions of Enteros Inc. This blog may contain links to the content of third-party sites. By providing such links, Enteros Inc. does not adopt, guarantee, approve, or endorse the information, views, or products available on such sites.
Are you interested in writing for Enteros’ Blog? Please send us a pitch!
RELATED POSTS
Revolutionizing Healthcare IT: Leveraging Enteros, FinOps, and DevOps Tools for Superior Database Software Management
- 21 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Optimizing Real Estate Operations with Enteros: Harnessing Azure Resource Groups and Advanced Database Software
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Revolutionizing Real Estate: Enhancing Database Performance and Cost Efficiency with Enteros and Cloud FinOps
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Enteros in Education: Leveraging AIOps for Advanced Anomaly Management and Optimized Learning Environments
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…