Today we’re going to hit my top 5 – well, top 6 – boots-on-the-ground security tips for SQL server. First, though…
I’m pleased as punch to invite you to our next live webinar on April 21: Jen and Sean’s All-Time 5 Favorite SQL Server Security Tactics!
Register: www.tinyURL.com/2022-03-LearnSQL
Okay, let’s get to the IRL security tips.
Schemas
Remember when the only option for schema-level permissions dbo? I do.
Back then, granting permissions was mostly at the object level, and it wasn’t fun. Grant UserX permissions to 80 objects, and then someone creates a new object. UserX didn’t automatically get rights to the new objects. Of course, you could give someone permission to the dbo schema – the only schema – but that’s very bad practice.
Grant permissions. New objects are created. Repeat, repeat.
Now, two decades later, we’re still mostly using the default dbo schema.
Schemas are a great way to separate code by function: put history tables in a History schema, staging tables in a Staging schema, shipping tables in a Shipping schema, etc. It’s very handy, and visually separates your database objects.
But schemas are in the database Security folder for a reason: schemas are a great way to separate your database into security groups.
At its base level, a schema is a security group that lets you grant permissions to current and future objects. That means that users get access to all future objects in that schema. It’s a way to assign permissions and not have to think about it again.
Here’s an example: You have several schemas with dozens of objects in each one. Different users need different levels of permissions to each schema. Instead of giving permissions at the to a user to individual objects, or to the other schemas, you can create a new schema to hold stored procedures that access those objects.
The sales group needs to access specific tables in 4 different schemas. It doesn’t make sense to grant Sales permissions to those four schemas. Instead, create a Sales schema with a set of stored procedures that can access those tables. With this method, the sales force has access to the objects they need – and only the objects they need. It’s easier for the data team to manage, too: when Sales needs access to new objects, you just create a new SP in the Sales schema. The sales group already has access!
Note that this is much like the way database roles work, only this is a level below that.
In short, use schemas liberally.
db_owner
I’m a firm believer in explicit permissions…just what a user needs to perform their job, and no more. When you grant a user db_owner, that is the height of implied permission, and it’s the lazy way to manage security.
Power users who need deeper permissions in a database often don’t need all the permissions afforded them by db_owner. They typically need permissions like CREATE TABLE, CREATE PROCEDURE, CREATE VIEW, etc. They don’t need to manage queues, or assign new permissions.
There are several tasks inside a database that only a DBA should have the rights to do. Even if you trust every single user with database permissions, mistakes do happen. (Is there a single data pro in the world who hasn’t hit F5 and then said, “Oh, oops” (or worse)?) Mistakes will happen far, far less often if you limit permissions.
Another practical reason to keep non-DBAs out of db_owner is Restricted mode. Any user with db_owner permissions can connect to a database in Restricted mode. DBAs use Restricted mode in (usually) emergency situations, where it’s vital to eject all DB users while you work out an issue. If entire groups have db_owner, you lose the option to kick everyone out and keep them out.
Dynamic SQL
The SQL world is full of warnings against dynamic SQL. I agree, it can be dangerous if done wrong, but I don’t advise against using it completely…just do it right.
The first way to protect your dynamic SQL is to never use parameters directly. If you have an SP with parameters, and you use one of them in your dynamic SQL, you must inspect the contents of that parameter before you do anything with it. Check it – thoroughly – for anything that could cause damage.
In fact, create another SP that does the checking so all your stored procedures can use it. This way, any changes you make to the string checker become global. If your SP finds issues with the string, log the details logged for visual inspection. This will help you refine your check code. If the parameter fails the check, exit the SP. Don’t take any chances!
Here are some things to check for:
- If passing in code that has a USE [database] statement, parse the text and make sure the database exists on the server. In fact, when you find yourself adding “USE [database]”, pass in the database name instead and build that string inside the SP. This makes it easier to check the database name.
- Check that there’s no hexadecimal or binary code. It’s common to convert malicious code to hex and then decode in inside the SP.
- CREATE LOGIN, ALTER LOGIN
- CREATE USER, ALTER USER
- Anything that references system tables.
- Anything that uses system functions (like current_user()).
- Anything with http or URL. If your system specifically must pass in URLs, validate that they are approved URLs.
- 1 = 1
- 1 = 0
This is not a comprehensive list, but it’s a good start.
The book “Security SQL Server” by Denny Cherry has a good chapter on SQL injection. (I will note, however, that I find the book’s xp_cmdshell notes misleading. Read our guide to xp_cmdshell instead.)
Deny, Deny, Deny
There are two ways to handle security around a new application launch:
- Grant minimal permissions, deny everything else, and then become really busy with security requests. Or,
- Start by giving a lot of permissions and then close it all back down if something happens.
Scenario #1 is much, much better. Scenario #2 is an absolute disaster.
Deny permissions both at the database level and at the schema level. There are plenty of permissions that bad actors can use to do their bidding…there’s no reason to make it easier for them.
Most users, especially those run by an application, do not need to create or alter objects, view definitions, truncate tables, or any of the other advanced functions that DBAs and developers typically do. So why open those up by default?
Crossover Groups
Crossover groups make things insufferable to manage and I avoid them whenever possible.
Crossover groups are those that have the same permissions. For example the Sales role has
- execute permissions on the Customer schema
- execute and update permissions on the Shipping schema
It turns out that the Marketing role also needs execute permissions on the Customer schema. “Oh hey, look!” says a DBA. “The Sales role has that permission!” The DBA then add the Marketing role to Sales.
While Marketing has officially gotten what it needs, it also now has unintended update permissions in Customer.
This is one simple example. In real life, when a full range of permissions is involved, these crossovers get more and more complicated. As you add permissions to one group or another, you can really mess things up. (This is how Accounting ended up getting sysadmin permission to all of production. Er….just as a hypothetical* example.)
Use smaller roles, with much smaller permissions definitions. In the example above, create more roles!
- Customer_Execute,
- Customer_Update,
- Shipping_Execute, and
- Shipping_Update.
Each role only has a single permission assigned, but in your shop you can combine the permissions…as long as you keep them small.
There’s no single solution to the crossover problem, and it’ll take some trial and error to get it right.
*Actually, real.
Secondary Systems
The absolute biggest problem I have with security is that I have to give users rights I don’t want to. Because they need to see certain objects to do their jobs.
I’m only half joking.
This is where a secondary system can come into play. A secondary system – a development or QA server – allows users to see the objects they need without having permissions in prod.
This is typically as simple as a backup and restore on another server. If they need access to data, then they can have it! And they can’t mess anything up that will bring down the business.
This isn’t a unique concept. There are lots of solutions out there: solutions for keeping the schema current, keeping the data current, masking the sensitive data, deleting the sensitive date, etc.
The biggest issue here is that most companies don’t view this from a security perspective. They only bother to set up a secondary server if they need actual copies of the data in other environments.
But keeping users out of your prod system is all-important, even if they just need to look at schema definitions.
The Bottom Line
So what have we learned today?
- Schemas are great for security. Use them!
- db_owner is only for DBAs.
- Check dynamic SQL…thoroughly.
- Deny permissions by default.
- Avoid crossover groups.
- Keep people out of production with a standby system.
LinkedIn Live event April 21 –
“Learn SQL Server Security: Practical Security!”