Data Security in Layers: The Onion Model By Adam Machanic, Microsoft MVP and founder of Data Education 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.
SQL Server Permissions
Stored Procedure Layer Encryption
Sensitive Data
Layering Security Provides Multiple Levels of Protection Against Attack Copyright Š2012 Data Education. All Rights Reserved.
Check out our SQL Training Courses at http://dataeducation.com