As a follow on editorial for database security, I was reminded that the needs of ORM tools may be a little bit different than database access you control yourself. One of the comments addressed Entity Framework (EF) specifically because it assures your database schema is synchronized with the code being executed.
When software using EF first connects to a database it validates the Schema of the database against the EF data models. By default, EF will create a new database if one doesn’t exist, using SQL Express. Most business applications have this feature disabled. Next, it compares all of the models, and the inferred relationships from the models against the actual tables and foreign keys in the database. You can enable EF to automatically synchronize the models to the database. Any modifications needed to bring the database up to the latest version are applied through EF generated DML code.
EF has become very rich in capabilities from the original release. Now you have the ability to specify exactly what kinds of relationships are intended between your models and your actual database implementation. It allows you to easily change the names of objects so that things such as column names do not have to match exactly the properties of the mapped EF Model. There’s much more than that when it comes to the power available for you for migrating change to your database.
From a security perspective, EF has the ability for you to separate the migration of your database changes from the validation at startup of your code hosting the EF connection. You can have EF generate migrations scripts directly in Visual Studio, which produce Fluent or SQL DML code that may be applied on demand. The neat part about this implementation is that you can preview or modify the migrations, as well as write your own custom migrations to be applied.
From a security perspective, this allows you to separate database schema change requirements from the execution of your application. You can have an account that has database schema modification privileges in your database, but is only used for database migrations. Then, for the run time execution of your application, you have another account that only has the appropriate privileges needed for execution. If you are not using stored procedures from EF, something you can do if you find the need, you can still grant only the necessary permissions to your application account. You can grant read, delete, modify permissions to any table or view. You can grant execution permissions to functions or stored procedures. Your application account would also need the ability to list schema items in order to validate the database against the EF version. However, this account may be highly restricted in SQL Roles, so that it can’t do things such as Backup, Restore, DBO, etc. It can be reduced specifically to that security required to implement your application.
If you do this work up front it can make it really easy to implement a rather granular security model without a huge amount of work, so you would actually implement a security plan, even if using an ORM automation tool.
This implementation works just as well if you are using a data first implementation for EF, such as T4 templates to build your models. The real key is the separation of Migration actions from your application actions.
Cheers,
Ben