title: Should data security be performed on the database side?
body: <p>We're in the process of setting up a new framework and way of doing business for our new internal apps. Our current design dictates that all security logic should be handled by our database, and all information (and I mean all) will be going in and out of the database via stored procedures.</p>
  
  <p>The theory is, the data access layer requests info from a stored procedure and passes over authentication to the database. The database determines the user's role/permissions and decides whether or not to perform the task (whether that be retrieving data or making an update).</p>
  
  <p>I guess this means fewer database transactions. One call to the database. If the security was in our data access layer, this would require 1 database call to determine if the user had proper permissions, and then 1 separate database call to perform the action.</p>
  
  <p>I, for one, find the SQL Management studio completely lacking as an IDE. My main concern is we will end up having to maintain some nasty amount of business logic in our stored procedures for some very minimal performance gains.</p>
  
  <p>Right now, we're using LINQ for our ORM. It seems light and fast, but best of all, its really easy to rapidly develop in.</p>
  
  <p>Is the maintenance cost worth the performance gain? Are we fooling ourselves into thinking there will even be a noticeable performance gain? Or are we just making a nightmare for ourselves?</p>
  
  <p>Our environment:</p>
  
  <ul>
  <li>Internal, non-mission critical business apps</li>
  <li>C#/ASP.NET 3.5</li>
  <li>Windows 2003</li>
  <li>MS SQL Server 2005</li>
  <li>35 Medium sized web apps with approx 500 users</li>
  </ul>