Comparative Analysis of Auditing using Classic SQL and CLR Methods with Accent on Performance Influence Admir Šehidić, Larisa Dedović
Jasmin Azemović
Faculty of Information Technology University “Džemal Bijedić” of Mostar Mostar, Bosnia and Herzegovina {admir, larisa}@edu.fit.ba
Faculty of Information Technology University “Džemal Bijedić” of Mostar Mostar, Bosnia and Herzegovina
[email protected]
Abstract—One of the primary concern of database administrator is monitoring and maintaining security and data consistency. Controlling a large number of transactions is demandable job and it presents a real challenge for administrators. This paper will explain some possibilities of control and data logging within the e-learning database used on Faculty of information technology. The main goal is to track of insert, update and delete operations within database (DML Data Manipulation Language), and to store all changes in safe location (log data). If unexpected changes occurs, the administrator will be able to access the log and verify data consistency. Auditing is implemented by using Microsoft SQL Server database management system, with focus on the implementation of classic SQL (Structured Query Language) and CLR (Common Language Runtime) based triggers. As a result of this work, a comparison of already mentioned techniques is presented and optimal solutions are given. Index Terms—Auditing, monitoring, triggers, sql, clr
I. INTRODUCTION Database and database management systems became skeleton of modern business. They can be found in different areas such as management, commerce, medicine, education and many more. Everyday business activities result in storing large quantities of data. They need to be organized and stored for the purpose of control and quick overview. During this process it is of high importance to secure data consistency and continuity, which is a challenge for administrators. Data manipulation is delicate because malicious data usage can jeopardize entire business. Because of that the mechanism of protection needs to be secured. Control of data management has to be done regularly, as well. For the purpose of demonstration, e-learning database of Faculty of information technology, will be used. Data about students, their achievements, their payments for school fees, are all stored in this database. Only employees with specific roles will be able to access and change this data. It is necessary to control the activities of employees which are dealing with sensitive data. The aim of administrator is to maintain logs in safe location, and to access and find unexpected data
inconsistency. This paper will present various techniques of auditing, with a special accent to the system performance. A. Auditing Data protection is not only a good practice, it is a necessary thing for every company. Nowadays, the level of threats is extremely high. Those threats come from inside or outside, from malicious users to unsatisfied employees. The administrators have the obligation to keep the security, privacy and consistency of database. Given the environment of the system and user profiles, different requirements were established. Primarily, it is necessary to take care of the access control, user permissions and activities. Auditing is the monitoring and recording of selected user database actions. It can be based on individual actions, such as the type of SQL statement executed, or on combinations of factors that can include user name, application, time, and so on. The main goal is to answer the question - who did something, what, and when. In this paper, primary goal is to monitor DML statements (insert, update and delete operations). Large amount of students’ data developed during their studies, need to be consistent and safe. In order to meet the requirements it is necessary to analyse various possibilities and auditing techniques regarding already mentioned database. When it comes to the realization of the goal, the most basic limitation is the database management system. Since Microsoft SQL Server DBMS engine is used in the production, requirements have to be adapted to environment. SQL Server, on the level of database, offers various possibilities for auditing implementation, which are based on different types of triggers. B. Types of SQL triggers Triggers are database objects. Basically, these are special type of stored procedures that are automatically fired/executed when a DDL (Data Definition Language) or DML command statement related with the trigger is executed. Triggers are used to assess/evaluate data before or after data modification using DDL and DML statements. These are also used to preserve data integrity, to control server operations, to audit a server and to implement business logic or business rule. In SQL Server can be created four types of triggers:
DDL Triggers: Auditing CREATE, ALTER, DROP and several system defined stored procedures which perform DDL operations, DML Triggers: Auditing INSERT, UPDATE and DELETE operations, as well on stored procedure that executes DML like operations, CLR Triggers: Auditing triggers implemented in .NET languages supported by SQL Server 2008 and above, Logon Triggers: Fire when SQL Server logon event occurs. They can be used to track successful authentication and control audit server sessions. There is a wide range of cases where triggers can be used, and they are especially useful for enforcing business policies [1]. To understand and implement the following auditing, it is necessary to explain DML and CLR types of triggers. 1) DML Triggers DML triggers are used to evaluate data after data manipulation using DML statements. We have two types of DML triggers: AFTER and INSTEAD OF triggers. AFTER Trigger. This trigger fires after SQL Server successfully completes action that fired it. For example, if you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the AFTER Trigger [2]. INSTEAD OF Trigger. This trigger fires before SQL Server starts the execution of the action that fired it. This is much more different from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete. For example, if you insert record/row in a table then the trigger associated with the insert event on this table will fire before the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will fire the INSTEAD OF Trigger [2]. 2) CLR Triggers The Common Language Runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. The CLR provides various functions and services required for program execution, including just-in-time compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security [3]. With the CLR hosted in SQL Server (called CLR integration), you can author stored procedures, triggers, userdefined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios [3].
CLR triggers are special type of triggers which are based on the CLR in .NET framework. CLR integration of triggers has been introduced with SQL Server 2008 and allows for triggers to be coded in one of .NET languages like C#, Visual Basic and F#. We coded the objects (like trigger) in the CLR that have heavy computations or need references to objects outside the SQL Server. We can write code for both DDL and DML triggers, AFTER or INSTEAD OF [1]. C. Organizing auditing triggers Basic request for auditing implementation on e-learning database system is monitoring and logging the execution of DML statements. When user executes insert/update/delete operation upon certain table, it is necessary to edit log history. Log history can be stored in table of the same or different database with limited privileges, text file or somewhere else. Auditing implementation will be discussed in details by storing changes in log table of the same database. It is necessary to create AFTER triggers, in order to follow and store changes after DML commands are executed. Trigger task is to store the changes into the log table. There are more tables containing sensitive data within already mentioned database, which need to have ensure auditing policy. It is not recommended to create log for each table in the database. Regarding this fact, changes that occure upon all tables with sensitive data will be stored in the same log table. For the purpose of easy organization, support and control, only one trigger will be created for all tables. When writing triggers, decision you must make is whether to use traditional SQL or CLR based triggers. Hereafter, possibilities of creating different types of triggers will be examined. Implementation details including TSQL and CLR triggers, will also be described. II. AUDITING IMPLEMENTATION E-learning system in its background has a complex database with a large number of user defined objects. System has been used for more than 10 years. Database contains more than 100 tables, more than 20 views, more than 500 stored procedures and approximately 10 GB of data. However, the database has certain tables with data sensitive to changes (passed exams, grades, professors, ECTS credits, etc.) which will be used for auditing implementation. Only employees with specific roles have right to access and change this data. The structure of log table (called Audit) is given in the following block of code. CREATE TABLE Audit( AuditID bigint identity(1,1) not null, TableName varchar(50) not null, RowID bigint not null, Operation varchar(10) not null, OccurredAt datetime not null, PerformedBy varchar(50) not null, FieldName varchar(50) null, OldValue nvarchar(MAX) null, NewValue nvarchar(MAX) null )
This table will store details about executed DML statements, such as: table that has been affected with executed operations, identifier of changed row, operation type, time and user who has performed the operation (insert/update/delete). In case of update operations, some additional columns will be filled: field name (column where changes occurred), old and new value (column field value pre and post changes). After the history table is created, next step of auditing implementation is selecting a table which will be monitored (with sensitive data). AFTER types of triggers for monitoring and logging execution insert/update/delete operations will be created for each table. A. Auditing DML statements using DML triggers DML trigger statements use two special tables: the deleted table and the inserted tables. SQL Server automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions. You cannot directly modify the data in the tables or perform data definition language (DDL) operations on the tables. The deleted table stores copies of the affected rows during delete and update operations. During the execution of a delete or update operations, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common. The inserted table stores copies of the affected rows during insert and update operations. During an insert or update transaction, new rows are added to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table. An update transaction is similar to a delete operation followed by an insert operation: the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table. When trigger conditions is set, the inserted and deleted tables should be used appropriately for the action that fired the trigger. Although referencing the deleted table when testing an insert or the inserted table when testing a delete does not cause any errors, these trigger test tables do not contain any rows in these cases [4]. Triggers written in T-SQL have the capability of determining which columns from the firing view or table have been updated. In order to complete set up request, which is creating one trigger for all three operations, it is necessary to write a complex TSQL that combines dynamic SQL and usage of cursor for creating loop (transfer data between temporary and log table). This is because of the update operation and the way of storing log data (one log table). If the user changes data, it is necessary to check which columns have been changed by using dynamic SQL to temporary tables (inserted and deleted). For every changed column one row needs to be inserted in Audit table by using cursor (loop), as well as the details of change data: field name, old and new value.
B. Auditing DML statements using CLR triggers Triggers written in a CLR language differ from other CLR integration objects in several significant ways. CLR triggers can: Reference data in the inserted and deleted tables, Determine which columns have been modified as a result of an update operation, Access information about database objects affected by the execution of DDL statements. The CLR trigger is definitely powerful and has a lot of promise. Execution time does suffer at high numbers of altered records though, if you want your trigger to be truly dynamic. One of the advantages of CLR triggers is that you can manage the compiling, deploying and testing all from within Visual Studio development environment [5]. In our case trigger has been written using .NET C# programming language. The trigger uses DataTables to store and manipulate both the altered and audit data, and uses DataAdapters to quickly fill them or flush them back to the database. It also uses SqlCommandBuilder to automatically generate the insert statements for the Audit tables, based on the select statements in the DataAdapters. Getting the inserted and deleted tables, name of the affected table and system user, is one of the most expensive items in the execution of the trigger [6]. After preparing the essential data, it is necessary to test a performed operation. Depending on the operation type, changes have to be logged into Audit table. It is also necessary to check changed columns (using foreach loop) and log the details: field name, old and new value. Managed database objects, such as stored procedures or triggers, are compiled and then deployed in units called assemblies. Managed DLL assemblies must be registered in SQL Server before the functionality the assembly provides can be used. To register an assembly in a SQL Server database, CREATE ASSEMBLY statement was used. Database users or user roles are given permissions to create, and thereby own, assemblies in the database. In order to create assemblies, the database user or role should have the CREATE ASSEMBLY permission [7]. And, finally, before executing the trigger, CLR must be enabled. III. RESULTS AND DISCUSSION After one of already mentioned triggers has been implemented upon key tables, all changes that appeared upon data will be stored in Audit table. Administrator's task is to protect Audit table from user’s access, as well as monitoring all changes. In case of deliberate or accidental change, administrator has the ability to compare data between tables, operating (table in which the changes occurred) and Audit table. No matter what type of trigger you chosen (TSQL or CLR), the result will be the same i.e. changes will be stored in Audit table.
Figure 1. Audit table with log data Figure 1. Selected row points that update operation is done upon the table that contains data about student success. Domain user FIT\admir changed the student’s grade. If we want to see data in details we could do it by using column value Rowld and join to operating table, with the same Id value. The following data could be: students’ name and surname, subject, professor and his subject, date of exam etc. In order to implement auditing details and achieve a goal it is essential to explore how do different types of triggers affect system performance, and choose optimal solution. It is expected that CLR triggers have better effect because they deal with compiled code that needs to be confirmed as well. Performance influence will be measured by considering following parameters: CPU time, duration, reads and writes. CPU time: Amount of CPU time (in milliseconds) used by the event, Duration: Amount of time (in milliseconds) taken by the event, Reads: Number of logical disk reads performed by the server on behalf of the event, Writes: Number of physical disk writes performed by the server on behalf of the event. Measurement was performed by using SQL Server Profiler Tool. It is an additional component of SQL Server installation. Regarding the purpose of database and clear differences between gained results, measurements that were completing by using 100 rows upon insert/update/delete operations. Based on 5 measurements that have been carried out, average value was computed for each operation. The results of measurements are presented in the following diagrams.
Figure 2. CPU Time comparation
Figure 3. Duration comparation
Figure 4. Reads comparation
Figure 5. Writes comparation Looking at the diagrams considerable deviations between parameters can be noticed. The most significant deviations appeared during the execution of update operations. Regarding the complexity of implementation, using loops, cursor and dynamic SQL, this was expected. Three, out of four parameters show that CLR based triggers are much more effective i.e. they use less system resources. Minor deviations appeared while recording data on disc, whether it comes to insert, update or delete operation. TSQL triggers require less time (CPU and Duration time) while insert and delete operations are being done. Deviations that appear while reading and recording data on disc are not significant. Deviations between TSQL and CLR based triggers are not same for all types of DML statements. Before the implementation it was necessary to make a detailed analysis. The results of analysis were following: Both types of triggers
have their advantages and disadvantages. If we use positive (+) and negative (-) symbols to mark advantages and disadvantages TSQL and CLR triggers, we would get the following results.
Parameters
TSQL
CLR
I
U
D
I
U
D
CPU Time
+
-
+
-
+
-
Duration
+
-
+
-
+
-
Reads
-
-
-
+
+
+
Writes
-
-
+
+
+
-
Table 1. TSQL and CLR auditing comparation (I - Insert, U – Update, D – Delete) It is noticeable from the table that CLR based triggers have minor advantage (from the aspect of all parameters). However, deviations are minimal (+2). If only DML statements were observed, main advantage of CLR triggers would be shown through complete effectiveness during which update operation is executed. On the other hand, from the aspect of performance time, TSQL triggers proved to be more effective when it comes to completing insert and delete operations. Based on reading and writing data on a disc, CLR has disregardable advantage with regard to TSQL triggers. Taking into account conducted analysis during the auditing implementation on e–learning database level the most effective solution is the combination of described triggers. As for monitoring insert and delete operations upon sensitive tables, it is essential to create AFTER INSERT, DELETE trigger (explained within the chapter implementation). Due to the significant improvement of system performance, update operation needs to be created by using CLR based trigger. There by, system performance, on the whole, will be optimized, while auditing will be implemented in desired manner. IV. CONCLUSION AND FUTURE WORK Database auditing is one of the administrator’s most important responsibility, especially when it comes to sensitive data. This is why it is essential to ensure data safety and integrity by choosing the best auditing policy. Todays’ database management systems offer a wide range of possibilities for auditing implementation at the server and database level.
This paper described a various database auditing implementations of e-learning system. Different types of DML triggers were used and detailed analysis was conducted from the point of system performances. The results showed that all of these triggers have their ups and downs and that the optimal solution for this particular system would be to use the combination of presented approaches. The question remains: Are triggers the best solution for auditing implementation? SQL Server has different kinds of auditing implementation. Besides triggers, there is an embedded SQL Server Auditing as well as the SQL Server Trace, Transaction Log and other additional tools. Depending on requirements and the level of security needed, appropriate solution would be chosen. The next phase of this research would include some other auditing approaches taking into account more demands on sever, database, performance, security and profitability. Special attention would also be given to logged data protection using encryption and hashing techniques. REFERENCES
[1] Shailendra Chauhan, Different Types of SQL Server Triggers, http://www.dotnet-tricks.com/Tutorial/sqlserver/OJ97170312Different-Types-of-SQL-Server-Triggers.html, downloaded: March 03th 2014. [2] Shailendra Chauhan, After Trigger, Instead of Trigger Example, http://www.dotnet-tricks.com/Tutorial/sqlserver/OPUH170312After-Trigger,-Instead-of-Trigger-Example.html, downloaded: March 03th 2014. [3] MSDN Microsoft, Introduction to SQL Server CLR Integration, http://msdn.microsoft.com/enus/library/ms254498(v=vs.110).aspx, downloaded: March 05th 2014. [4] Technet Microsoft, Use the inserted and deleted Tables, http://technet.microsoft.com/en-us/library/ms191300.aspx, downloaded: March 09th 2014. [5] Technet Microsoft, CLR triggers, http://technet.microsoft.com/en-us/library/ms131093.aspx, downloaded: March 09th 2014. [6] David Catriel, Detailed SQL Server data audit through a .NET (CLR) trigger, http://www.codeproject.com/Articles/293406/SQL-Server-dataaudit-with-a-NET-trigger, downloaded: March 05th 2014. [7] Technet Microsoft, Creating an Assembly, http://technet.microsoft.com/en-us/library/ms345106.aspx, downloaded: March 10th 2014.
