Security is very much on our minds these days. Scarcely a day goes by without hearing of a yet another data breach involving millions of emails, passwords, and other personal information. Many of us have been the victims of identity theft, credit card fraud, or phishing emails. As criminals become more sophisticated, we search for new means of protection: heavy encryption, multi-part authentication, increased use of biometrics. But how many of us have overlooked the fundamentals of good security, or let things go lax while we focused on other issues? I was recently asked to conduct an in-depth security review on one of our servers, and my findings both surprised and alarmed me, and made me realize that we had to go back to basics to set things right.
Don’t Mix It Up
Windows authentication is our preferred method of access, and SQL Server logins are not permitted, except where it’s impossible to avoid them, as in third-party software that requires same. Windows authentication is more robust overall, and we are more easily able to enforce rules and requirements for passwords. This also helps when employees leave the company: disabling the Windows account will remove access to SQL Server as well.
No Singles Allowed
One of the things I found was a large number of individual NT user accounts as logins, either at the server level or in the databases. Individual users are tough to manage, and setting and keeping track of the permissions for a couple of dozen developers would be chore. Users come and go, also, and several of the accounts I found were for people no longer with the company.
Active Directory groups are a much better choice for controlling access. Grouping users by job title or department not only simplifies permissions management but acts as a base level of self-documentation. “Corp_Domain\JShmoe” doesn’t say much about who JShmoe is, or what he or she does. “Database Developers” makes clear who’s involved, and gives some expectation of the level of access to expect. Service accounts will be an exception to this guideline, but even they should be grouped where feasible.
There’s a caveat to using AD groups: some users will belong to multiple groups, and one must take care to avoid this if at all possible, as it will complicate another fundamental:
The Principle Of Least Privilege
The idea here is straightforward and sensible: groups should be given the lowest level of access and permission necessary for its members to do their jobs. If they need access to the data, but shouldn’t be allowed to change it, then db_datareader is an appropriate role, but db_owner is not. Service accounts used by applications should ideally be given permission to execute stored procedures (which in turn will provide necessary permissions to the underlying objects), but little beyond that.
Even DBA’s should not be above the law: although there’s a need for personnel with sysadmin rights, there are more restricted roles that still allow the necessary latitude for those charged with monitoring systems, managing storage and security, and so on.
This is where multiple AD group membership can be a hazard, if you’re not careful. If I belong to one group with read-only rights, but another group that has right access, SQL Server will enforce the latter, not the former. Only in the case of an explicit deny is this not true. It’s best to work closely with management and Windows admins to make sure there are groups with clearly delineated roles and no overlap in members.
The Perks and Perils of Ownership
When creating a new database, unless told otherwise, SQL Server will make the individual user running the script (or using the GUI) the database owner. This can result in unintentionally elevated privileges, often across multiple databases. A best practice is to disable the “sa” account (which ought to be done anyway), and assign that as the database owner immediately after creating the database, or specifying same in the create script. I have also seen service accounts used as the owner, but this has the same perils as an individual, and is perhaps more of a security risk, since the password for the account may be stored in source control by the development teams, or documented in hard copy.
Hey, You, Get Outta My Prod
“Dev Out Of Prod” has become a mantra over the last few years. As a former developer, this one stings a bit, but as a DBA, I understand the need for restriction. Developers should not be working on production servers, nor deploying code, and if possible should be excluded entirely. Even read-only access can be seen as a risk, because quite often the sensitivity of data is in the eye of the beholder. There’s also the need for accountability when things do go awry, and limiting the number of individuals with unfettered access to as few as possible simplifies auditing. If there’s a need for access to production data, for debugging or reporting, for example, providing a separate server and redacting or encrypting the data where necessary is a safer road than allowing too many people to use a production server as a sandbox.
Keeping It Real
Security is a real concern for us, day in and day out. Remembering to get the fundamentals right: limiting login options, limiting permissions, using groups and roles appropriately: is the first and most important step in keeping our data safe, and our clients happy.
