Product articles SQL Monitor SQL Server Security Monitoring
Monitoring Changes in Permissions,…

18 October 2018

4 Comments

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

18 October 2018

4 Comments

Monitoring Changes in Permissions, Users, Roles and Logins

Phil Factor uses the default trace and a SQL Monitor custom metric to alert you to unauthorized changes in security membership or permissions in any of your monitored databases.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

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.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter
  • Max Shapiro

    Is this code only for SQL 2017? The FUNCTION dbo.SeeAccessControlChanges does not seem to compile in SQL 2012

  • Max Shapiro

    Is this code only for SQL 2017? The FUNCTION dbo.SeeAccessControlChanges does not seem to compile in SQL 2012

  • Phil Factor

    @@disqus_nuQC4lr6Ll:disqus There is no good reason I can think of why it shouldn’t me changed to run on 2012. It uses the DateTime2 datatype for the parameters, which can be changed for DateTime. Unfortunately I no longer have access to a 2012 version for testing. Can you tell me the error and I’ll investigate.

  • Michael Spurlock

    The error I get is:
    “Msg 8116, Level 16, State 1, Procedure SeeAccessControlChanges, Line 37 [Batch Start Line 3]
    Argument data type datetime is invalid for argument 1 of switchoffset function.”
    I tried changing some types around, but got the same error.

  • Phil Factor

    @michael_spurlock:disqus @disqus_nuQC4lr6Ll:disqus Try CONVERT(datetime2, SWITCHOFFSET(CONVERT(datetimeoffset, StartTime), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS datetime_local,
    –try this code. It is supposed to work in 2008 but I can’t test it easily

You may also like

  • Community event

    SQLSaturday Cape Town

    SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. Admittance to this event is free, all costs are covered by donations and sponsorships. Please register soon as seating is limited, and let friends and colleagues know about the event. Redgate are excited to be one of the

  • Webinar

    How DevOps can help you stay ahead of the competition

    In this webinar, Microsoft MVP Steve Jones welcomes DevOps expert Bob Walker, Solution Architect at Octopus Deploy, to discuss how taking a DevOps approach paves the way for staying ahead in business.

  • Article

    Picking over the Bones of a SQL Injection Attack

    The best way to learn how to protect your databases from SQL Injection is to to see it in action and confront its consequences. This article tells the story of an attack on a vulnerable SQL Server REST interface, explaining how the attack unfolds, the mistakes that made it possible, and SQL Monitor's role as the 'canary in the mine'.

  • Article

    Avoid running out of Disk Space ever again using SQL Monitor

    SQL Monitor not only collects all the disk and database growth tracking data you need, automatically, but also analyses trends in this data to predict accurately when either a disk volume will run out of free space, or a database file will need to grow.

  • Forums

    SQL Monitor Forum

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