SQL: Should we use schemas other than dbo in SQL Server databases? (Part 2)

In a recent post, I described the first reason why I think you should be using schemas apart from dbo in your SQL Server database designs. It was to provide a form of grouping like you use with files in your filesystem, and to avoid prefixes. Now we need to talk about the second main reason for using schemas.

Security Boundary

I need to start by saying that if you’re idea of database design and access is to have an ORM that builds queries on the fly and needs access to the entire database without any form of security control apart from what your application provides, you might as well stop reading this post here.

But if you’re interested in building more secure applications for enterprises, read on.

Another key advantage of schemas is that it allows you to group objects that have similar security requirements and to then be able to assign those permissions at the schema level. This could be tables, views, stored procedures, functions, etc.

Let’s take an example:

If I need to expose a website to the public, I’m concerned about what will happen when (nowadays it’s really not “if”) the site gets compromised in some way. The primary aim of your security design should be to minimize the damage when this occurs.

The easiest way that I find to do this, is to create a separate schema in the database, and have that schema used by the website. For simplicity, let’s call the schema Website.

In the Website schema, I’d place just the objects that the website is permitted to use. Specifically, that will be a set of views, functions, and/or stored procedures. Generally, there would be no objects like tables in that schema. This also helps to enforce a design pattern where the interaction between the application and the database is clear and defined.

Then, the website will connect to the database using a single login/user and the ONLY permissions it will have (or its role will have), are SELECT and EXECUTE permissions on the schema. It will have no other permissions at all.

At least then, no matter what happens to the website and how it gets trampled on, you know exactly the limits of what it will be able to do.

And before you cut corners, I’m not talking about having an option where you create a query in the application and pass the whole query to the database as a parameter.

I’d apply the same logic to other types of access such as how Power BI connects to that database across an enterprise gateway.

Schemas are an important part of your security arsenal.

2018-09-17