Click on the banner to learn about and purchase my database training on Azure

SQL Server - Implementing access control and audit logs when using the CLR (C #)

Views: 716 views
Reading Time: 4 minutes

Hello everybody!
How are you ?

In this post I would like to demonstrate how to implement access control and audit logs in using CLR (C #). This has proven to be very useful during auditing processes or when there is a problem due to misuse of the CLR, especially where the SQL Server service user is domain admin or has a high privilege level and can perform a number of actions and you end up not doing so. taking control of who you use and what each person is doing with the CLR, especially when you have record handling routines, files, etc.

In order to maintain control over the use of this tool, I will demonstrate in this post how you can implement access control of all CLR usage so that you can identify who performed each routine and even the parameters used in each call.

Creating the CLR Audit Logs

To create this control, first of all we need to create the table in the database that will keep the logs for auditing the use of SP's and CLR functions.

The structure I will use in this post is this:

Record method source codeLogExecution

Using the code below, you can generate log information and write to the database.

Using the auditing method in your CLR

Once you have created the static method writesLogExecution In a class of your SQL CLR project, you should use this method in all of your Stored Procedures and functions you want to audit.

I will demonstrate some examples.

Implementing audit logging in Stored Procedures:

Implementing the audit log in roles:
To implement the log write feature, we will need to include 2 special parameters in the declaration of the SqlFunction object, which is DataAccess and SystemDataAccess.

By default, functions in the CLR do not have access to perform database queries. To make this possible, we will need to set the read mode for these functions so that they can query and execute commands in the database, as I did in the example below.

As you may have noticed, in the method call writesLogExecution I need to inform the name of the parameter and its values ​​in each call. This is especially laborious, especially if you have a lot of SP's and functions, but unfortunately, I couldn't find any alternative for that, not even using Reflection. Therefore, if your Stored Procedure has 10 parameters, you will need to inform them one by one, and their respective values ​​in the method call. writesLogExecution in C #.

It is worth remembering that, unlike Transact-SQL functions, where it is not possible to execute commands and make any external changes to the function, such as performing a Procedure, changing table data, etc., in CLR functions it is possible to perform any kind of action, including file operations, data manipulation, etc., which makes them especially powerful and dangerous if misused.

Viewing audit logs

Now that I have implemented the audit log in all my CLR routines, let's run some objects and look at the generated execution log.

CLR Command Examples

Log Preview

That's it folks!
I hope you enjoyed this tip.

I know you thought it was really cool how I manipulated the files very easily, intuitively and quickly right? If you liked it and want to know a little more about how to perform file operations using CLR, see more in the post. SQL Server - How to list, read, write, copy, delete and move files with CLR (C #).