Geeks With Blogs
Blog Moved to http://podwysocki.codebetter.com/ Blog Moved to http://podwysocki.codebetter.com/
In previous posts, I covered what you can do with strong names and the new features in Visual Studio 2005.  My perspective regarding security is that it is way overlooked with our programming and needs to be reinforced in all day to day operations.
 
Today, what we're going to cover is the SqlClientPermission and the SqlClientPermissionAttribute.  We can use this attribute to restrict how the database can be accessed.  I'm always of the mindset is to restrict most permissions unless specifically granted to me, especially in the ASP.NET realm.  I think it makes more sense to make sure that malicious code can't misuse the database, as well as enforcing a certain connection string.  Today we will cover those in detail.
 
First off, let's look at your common, everyday data access class.  We have a requirement to restrict that the connection string does not allow a blank password.  Here is how we would accomplish that goal:
 
[SqlClientPermission(SecurityAction.PermitOnly, Unrestricted=false, AllowBlankPassword=false, KeyRestrictionBehavior=KeyRestrictionBehavior.AllowOnly, KeyRestrictions="Server=;Initial Catalog=;User Id=;Password=;Connection Timeout=;")]
public class DataAccess { ... }
 
This would permit:
DataAccess.ConnectionString = "Server=(local); Initial Catalog=Security; User Id=SecureUser; Password=pWd!123;";
And it would not permit:
DataAccess.ConnectionString = "Server=(local); Initial Catalog=Security; User Id=SecureUser; Password=;"
DataAccess.ConnectionString = "Server=(local); Initial Catalog=Security; Integrated Security=SSPI;"
 
Now from the example up above, we also note that we must set Unrestricted to false in order for set pretty much any restriction with the SqlClientPermissionAttribute.  This is a requirement of the .NET framework.
 
Next, let's look at some further examples.  Now we have a requirement for using Windows Integrated Security only in our connection string and with a set database and a set server.  The attribute gives us a way to restrict that.  Below is the example on how to accomplish this goal:
 
[SqlClientPermission(SecurityAction.PermitOnly, Unrestricted=false, ConnectionString="Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=SecureDatabase;server=(local);")]
public class DataAccess { ... }
 
This would permit:
DataAccess.ConnectionString = "Server=(local); Initial Catalog=SecureDatabase; Integrated Security=SSPI";
And it would not permit:
DataAccess.ConnectionString = "Server=(local); Initial Catalog=SecureDatabase; User Id=SecureUser; Password=;"
DataAccess.ConnectionString = "Server=OTHERSERVER; Initial Catalog=Security; Integrated Security=SSPI;"
 
 For the enterprise, this is a pretty inflexible approach for configuration purposes.  We restricted ourselves to only that particular server and that particular database.  This might be ok if you have a small web application where the database name and location don't change.  We can solve this a better way by using KeyRestrictions.  So, let's follow through with an example of that below:
 
[SqlClientPermission(SecurityAction.PermitOnly, Unrestricted=false, ConnectionString="Integrated Security=SSPI;" KeyRestrictionBehavior=KeyRestrictionBehavior.AllowOnly, KeyRestrictions="Integrated Security=; Server=; ConnectionTimeout=; Initial Catalog=;")]
public class DataAccess { ... }
 
This would permit:
DataAccess.ConnectionString = "Server=(local); Initial Catalog=SecureDatabase; Integrated Security=SSPI";
And it would not permit:
DataAccess.ConnectionString = "Server=(local); Initial Catalog=SecureDatabase; User Id=SecureUser; Password=;"
 
So, we have covered quite a few scenarios.  Unfortunately there isn't much out there on this subject and there needs to be more attention to it.
Posted on Friday, May 12, 2006 9:53 AM Microsoft , .NET , Windows , C# | Back to top


Comments on this post: .NET Code Access Security - SqlClient

# re: .NET Code Access Security - SqlClient
Requesting Gravatar...
please give me step by step
Left by abdhesh on Sep 24, 2008 2:18 AM

Your comment:
 (will show your gravatar)


Copyright © Matthew Podwysocki | Powered by: GeeksWithBlogs.net