18 October 2018

4 Comments

18 October 2018

4 Comments

Monitoring Changes in Permissions, Users, Roles and Logins

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.

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.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    Custom Metrics for Detecting Problems with Ad-hoc Queries

    Whatever development methodology you use, you must check on the quality of the code before releasing a version of a database. This isn’t just a general check for ‘technical debt’ or code smells; you must also make sure that queries are not hogging resources on the server. A common crime is the unnecessary overuse of

  • SQL Saturday

    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

  • Article

    SQL Monitor Quick Tip: Using the SQL static code analysis performance rules

    Static code analysis parses the source code, checking the syntax for compliance with a built-in set of rules. These rules are designed to encourage good coding practices and, applied during development and testing work, help you minimize the number of ‘code smells’ that creep into your application and database builds. For SQL, these ‘smells’ could

  • Article

    VMware monitoring in SQL Monitor

    Over the years, running SQL Server inside a virtual machine has changed from being a bit of a strange and risky idea to being commonplace and offering a range of benefits: more effective distribution of load, more flexible provisioning and allowing additional tiers of redundancy to be put in place. Though these factors are compelling,

  • University

    Take the SQL Monitor course

    This course takes you from installation and configuration, all the way up to getting the most out of the advanced features in SQL Monitor to help you proactively monitor your SQL Server estate. Learn how to explore in-depth issues in your environment, run an analysis, manage alerts, create custom metrics, and more.

  • Forums

    SQL Monitor Forum

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

  • 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