MVC: Security Best Practises for Entity Framework 4.3 Code First and SQL Server

With Entity Framework, using code first development is a real time saver - however there are several best practises to consider when using SQL Server as your data store.

1. The user credentials you specify in the web.config when developing your application must have elevated privileges to drop/create the database depending on the database initialiser you are using for development. The use of web.config transforms for specifying different configurations for different build environments is essential, so that a connection string for a production build has user credentials with the lowest possible privileges.

2. Use DataAnnotations to specify schemas. In most of the examples I have seen, objects are created in the dbo schema. An example of how to specify a schema for your entity is:

[Table("Course", Schema = "Students")]
    public class Course
        public int CourseID { get; set; }
        public string Title { get; set; }
        public int Credits { get; set; }
        public virtual ICollection<Enrollment> Enrollments { get; set; }

Why do this? Well, when the database and the user login are created (see item 3), the database user created for the login can then only be set explicit permissions for the schema(s) it needs to. 

3. When publishing to a production server for the first time, create the database manually and use Code First Migrations in Entity Framework 4.3 to publish an SQL script that can be run separately in the context of your database by a user with the correct privileges. This prevents your application requiring elevated privileges on your database; potentially disastrous if your the production server hosting your application is compromised.

I will add other best practises as I encounter them, however these are some things I have noted so far. Please add your own best practises below or comment on the above!


Popular posts from this blog

Connecting to SQL Azure with Dynamic IP Addresses

HTML to PDF Conversion in MVC 4

WebAPI and Subscriber Authentication by Custom HTTP Headers