• Redgate Monitor
• Security and compliance

Monitoring Changes in Permissions, Users, Roles and Logins

Compliance means keeping a close grip on any changes to the permissions and access control of a database. Sadly, the law has had to acknowledge, from bitter experience, that it is not just external intruders who want to do this, but it could also be attempts at fraud or data theft from within the organisation. Permission changes are certainly one of the things that security experts advise you look out for; you need the equivalent of CCTV trained on your servers.

If you have default trace running, then you should already have an impressive audit, with a record of what changes to permission, users, logins or roles were made, when and by whom.

After the alert

The most important requirement is to get an alert at the point these changes start happening. We’ll set that up shortly in SQL Monitor, using a custom metric that queries the default trace.

When you receive an alert, you can then examine the details of the access and permission changes at your leisure, so you can understand more about the attack. Fortunately, the default trace can go back weeks, depending on the workload, and on the extent of telemetry and monitoring that you have running.

There is a lot of data in the trace, and it is easy to get a bit overwhelmed with it, so we’re aiming to distil it into a simple report of when the change was made, who changed what, and details of the affected security objects. Figure 1 shows the result of our forthcoming labours. I generated the report after creating and deleting a few logins, roles and users.

Figure 1

The action column that you see is just something I’ve added to assist the viewer; it’s a summary of useful data from other relevant columns.

Collecting the data

In previous articles on monitoring SQL Server for database drift, configuration changes and ‘suspicious’ errors, I’ve mined the data I need from Extended Events. Here, I’ve resorted to using the default trace.

Microsoft say:

‘This feature (the default trace) is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Extended Events instead.’

Unfortunately, I can find no equivalent way, using Extended Events, of detecting changes in permissions that covers all the range of SQL Server versions in production use. You will get a lot more data from SQL Audit if you have it, but the reason that I like using the default trace is that its presence in SQL Server goes back in the mists of time, and so you are likely to be able to use it. It is still there and smiling in SQL Server 2017, and begging to be used. As a bonus, towards the end of the article, I’ll even show you how to use the default trace to detect unauthorized changes to database objects (tables, view procedures and so on), as we achieved with Extended Events previously, but now shown alongside permission changes. You can see both types event in one single ‘narrative’, or time-based, sequence.

Listing 1 shows how to create the SeeAccessControlChanges function that will collect all the data we need from the default trace. I’ve reduced the data to be scanned to just the period you select, and I’ve just chosen to collect only the records that involve a login, user or role. This filtering is almost disarmingly simple, since each of these provide a value in the TargetLoginName column, whereas for everything else it is NULL.

The only downside is that it takes a half-second or more to get the information, because you are basically reading it from a file. However, in my experience, Extended Event ring buffers are generally slower to read.

Listing 1

Now, when we want to drill into the details of any changes made, we can simply use Listing 2, to execute the function, and it produces a report like the one in Figure 1.

Listing 2

In this example, I’ve just selected all changes that happened in the past day, but you can search a long way back with the default trace, or just a few seconds if you need that.

This approach is, however, more like an audit, a way of assessing the damage. What you really want to know is what is happening right now, because you would rather nip an attack or uncontrolled change in the bud, before the perpetrator has got to the data. You want a baseline from which you can judge that activity is unusual. What you need is a monitoring tool.

Creating the custom metric in SQL Monitor

We next set up a custom trace for SQL Monitor that will alert us as soon as a change occurs. SQL Monitor wants a SQL batch that returns a single integer, and we will choose to return the number of changes to logins, users or roles that happened in a ten-minute interval. As scanning the default trace takes time, we don’t want to be calling it too often. Once every five minutes seems about right to me.

Listing 3

If you get false positives, you can easily fine-tune the WHERE filter in the SQL Expression. You might, for example, get permission changes as part of a legitimate routine within a user database, a third-party tool or perhaps a scheduled task; and these must be filtered out once you are clear about the circumstances in which the event occurs.

If you don’t have SQL Monitor, you can run this on a schedule in SQL Server Agent, and set it to alert you if there are any changes in the permission system involving either logins, roles or users.

The disadvantage of relying on SQL Agent is that, to comply with legislation such as SOX, the monitoring system should be outside the control of what you are monitoring. An attacker who gains sysadmin privileges can disable a SQL Agent job. Also, you want to be able to check the whole farm for any signs of intrusion or uncontrolled changes.

Figure 2 shows us adding a new custom metric to our SQL Monitor installation, which uses Listing 3 to collect the data.

Figure 2

I’m collecting the data every 5 minutes, from the master database, on all my servers

Figure 3

Finally, we add the alert.

Figure 4

Detecting the changes

We can now run various tests and see if some simple changes to logins, roles and users are detected. First, we will make some simple changes. We’ll add a random time interval between DCL commands to simulate real usage a bit closer, as if the commands are being keyed in.

Listing 4

In the Analysis section of SQL Monitor, I’ve created a tile that plots the values for our new Permission/Security changes metric, along with those for the three custom metrics that I’ve written about in previous articles (referenced above).

Having created the tile, run Listing 4 to make sure it detects these untoward database security changes.

Figure 5

You should also find that an alert was fired when it happened.

Figure 6

Now, of course, you should retrieve the detailed report that tells you who made what changes and when, as described earlier.

Using the default trace to relate database drift to permission changes

Sometimes, you want to use the default trace to detect object changes alongside security changes. The following SeeDatabaseObjectChanges function collects this data form the default trace, similarly to the one we used earlier.

Listing 5

And this is how you might want to execute this.

Listing 6

This shows us which logins changed which objects, in which databases. Depending on what you were doing on the server at the time, the reports looks something like this:

Figure 7

This is fine, but we can do even better now. We can mix information from this TVF and the previous one, SeeAccessControlChanges, to see both object changes and permission changes. This gives us the fuller story of what the intruder is doing.

Of course, in this case, the ‘attack’ makes no narrative sense, because I’m simply reusing existing test scripts! The narrative from the real intruder will make more sense.

Figure 8

And this is the code that worked this magic.

Listing 7

Conclusion

The combination of a good monitoring system and the default trace can tell you when something untoward is happening with a server and will provide a baseline of the activity you are monitoring. The default trace is antiquated, but that means that you can manage a whole estate of servers and use the same forensic tools on all of them. Be aware, though, that it is flagged for removal, to be replaced by equivalent functionality with Extended Events. I suspect I’ll rewrite this article then!

The great thing about using the default trace is that your subsequent queries, once SQL Monitor has alerted you, are relatively easy. If your default trace is getting filled rapidly, you may need to search more than the current file. This will make it slower but requires only a slight alteration to the code.

If you haven’t many servers to look after, you can simply use the query to determine how many permission changes had happened in the past ten minutes and fire a SQL Server alert when something is detected. However, you lose the baseline (zero, you’d hope in this case, but don’t count on it!) and you cannot aggregate servers into groups.

Worse, you lose the advantage that SQL Monitor is a remote process that is hard for intruders to turn off, even if they manage to gain godlike powers for their SQL Server user, and even if they try, it fires an alert! I like it most for seeing those spikes in the tile graphs, and an at-a-glance view of the sort of activity that is going on. Graphs can make it easy to assimilate a variety of information and integrate it. They are, in other words, brain-friendly.

Tools in this post

Redgate Monitor

Real-time SQL Server and PostgreSQL performance monitoring, with alerts and diagnostics

Find out more

SQL Toolbelt

The industry-standard tools for SQL Server development and deployment.

Find out more