Data Security in Layers: The Onion Model

In this post, Data Education founder and trainer Adam Machanic continues his discussion of security through layers. Here, he explains how to think of SQL Server security as a multi-layered schema.

Generally speaking, the more levels an attacker must penetrate in order to access a valuable resource, the better the chance is that an attack will not be successful. Developers should strive to carefully construct multiple layers of protection for any sensitive data, in order to ensure that if one security measure is breached, others obstacles will keep an attacker at bay.

The first layer of defense is everything outside of the database server, all of which falls into the realm of authentication. Once a user is authenticated, SQL Server’s declarative permissions system kicks in, and a login is authorized to access one or more databases, based on user mappings.

From there, each user is authorized to access resources in the database; another layer that can be added for additional security here is use of stored procedures. By assigning permissions only via stored procedures, it is possible to maintain greater control over when and why escalation should take place.

Of course, the stored procedure itself must have access to whatever tables and columns are required, and these resources can further be locked down if necessary, using encryption or row-level security schemes.

The figure below shows some of the layers that should be considered when defining a SQL Server security scheme, in order to secure the sensitive data as well as possible. (In future posts, I’ll talk about how best to control access to resources using stored procedures as the primary access layer into the data once a user is authenticated.)

SQL Server security scheme

Layering security provides multiple levels of protection against attack.

A stored procedure layer provides an ideal layer of indirection between the data and the data access, allowing for additional security to be programmed in via parameters or other inline logic. For instance, it is trivial to log every access to sensitive data via a stored procedure, by including logging code in the procedure. Likewise, a stored procedure might be used to force users to access data on a granular basis, by requiring parameters that are used as predicates to filter data. These security checks are difficult or impossible to force on callers without using stored procedures to encapsulate the data access logic.