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
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.
View this article on our website: http://dataeducation.com/blog/data-security-in-layers-the-onion-model
Copyright Š2012 Data Education. All Rights Reserved.
Check out our SQL Training Courses at http://dataeducation.com