Steve Jones (@way0utwest@bluesky), who has coordinated the T-SQL Tuesday blog party since Adam Machanic stepped down, is also hosting this month. And he asks us about database permissions. How we check who has access to what within the database. I’m going to focus on whether people have access to sensitive data, rather than whether people have the ability to do malicious things such as dropping tables. After all, news articles about data issues tend to be more about sensitive data leaking rather than database systems going down.
In his invitation, Steve casually mentions that many long-living systems have people that have too many permissions, and that the fact that there are these overprivileged people can go unnoticed. As a consultant, I like to notice. It’s important for us here at LobsterPot Solutions to understand the database permissions that our customers use. When we do a health check, permissions is one of the areas we will explore.
When a user accesses SQL Server environments, it’s either via their personal account or via a login and password combination. Often, these logins are used in a number of scenarios, and quite a lot of people know them. Ideally not. It’s worth noting that applications often access databases using a dedicated SQL login that is remarkably privileged, while user permissions are (hopefully) more about the person’s role and often managed within the application. If an application connects to the database as the user who is running the application, this often means that the user could access the database using other applications (such as SSMS) or by using scripts. It’s best if the only users who can do this are trusted and responsible and know what they’re doing within the database. It’s all well and good that their account might be able to look at data through the controlled code of the application, but ad hoc access doesn’t tend to have the same guardrails.
The history of connections to the database can show which users are using which applications to access the data, and this can highlight some issues.
My preference is that applications connect to the data as a particular login (I don’t really mind whether this is a managed user or a SQL login, so long as it’s controlled). And that all other access be done through people’s Windows accounts, with those accounts locked down enough so that ad hoc access doesn’t become a problem. But although our checks will look at the amount of access users have, it’s rare that permissions are the weak point of security.
When my doctor’s receptionist answers my phone call, they can look up my record and give me information. At this point, the application is doing its job by letting the receptionist see my data, but is the receptionist supposed to just answer my questions from their desk near the waiting room? Did I have to identify myself? Could I have asked about someone else’s data? Maybe there are people in the waiting room who hear my news. And maybe there’s someone sitting near me when I call my wife to let her know (making me the weak point). Security is a fickle thing – I don’t much care if a stranger found out my leg was fractured. Heck, maybe I was going to write it on my blog one day, for the world to read. The crutches were a bit of a clue too.
Applications often monitor who is doing what in the system, but someone with direct access to a database might not go through the application. This is where understanding database permissions can matter, but it still doesn’t mean that the system is insecure.
A lot of sensitive data ends up being visible to developers, administrators, analysts, managers, operators, and more. There are things that can be done to reduce the chance of someone accidentally seeing data they shouldn’t, but much of the time the issues are within people rather than the permissions that have been granted.
Even a SQL Injection attack is not something that can be easily avoided using database permissions, because an attack like this will connect as the application, but circumventing any restrictions that the application might have in place. This can’t even be spotted by seeing which applications have accessed the database, because it’ll present the same way as someone accessing the database through appropriate use of the application.
These are all conversations I will have with customers who want me to review their database security.
There are some things that I think we do differently though.
One is that of access through ‘privileged accounts’.
I see some systems where people need to use a privileged account to get access to a database. But they use that privileged account every day because that’s what their regular job needs. I’m not a fan of this. My perspective is that your regular account should let you do your normal duties. But if you need to do something special, such as deploying a data change in production, then you should escalate your rights in that moment for that (and go through all the other rigmarole required to do so). In fact, my preference is that someone’s privileged account is denied access to all the usual things that they do, to discourage them from using it all the time. To paraphrase Syndrome from the movie The Incredibles – if all our user accounts are super, then none of them are.
The other thing I like to do is regularly script the environment, including permissions, and to check this into source control. This makes it very visible if anything has changed.
But let’s not think that this means we’re protected from harm. The danger in our systems is far less likely to be related to a login having too much access, and far more likely to be because someone looks at data they shouldn’t, or even that someone listens in to a conversation while you’re on the bus.
And that stuff isn’t very scriptable.
@robfarley.com@bluesky (previously @rob_farley@twitter)
