I am working on a project for DoD by using Microsoft's MVC and all other related technologies. For the security purpose, I have to follow the Security Technical Implementation Guide (STIG).
In version 3, Release 9, section 3.10.1, it indicates
Allow access to the database through views no directly to underlying tables in the database.
and
(APP3540.4: CAT II) The Designer will ensure the application does not directly access the tables in a database.
Can I use Entity Framework with LINQ?
Here's a direct link to the STIG. It's one revision old but I'm looking at the latest and they're exactly the same. I think the text hasn't changed for many years, which is unfortunate since it isn't very clear. The rule title says to use parameterized statements (which I see as any CRUD operation), but also not to provide direct access to the table.
Entity Framework sends parameterized queries to the database, which is a form of direct access. Do I think it's unsafe? Absolutely not. But one could argue the STIG says not to do this. The problem is the STIG also says use a stored procedure in lieu of direct access, but you can just as easily have SQL injection vulnerabilities if it's written incorrectly.
My interpretation? In the end, the entire point is to address SQL injection so as long as you're using parameterized queries (i.e. EF, Dapper, ADO.NET, etc) or writing proper stored procedures to mitigate that, you should be okay.
The STIG isn't actually requiring ReadOnly, just that views be used instead of talking directly to tables.
So instead of connecting to tblEmployees, you'd connect to vwEmployees (which is the View of tblEmployees).
This is so you can have greater control over permissions, access controls, and limitations.
Example: User role 1 can see column A and B, but not C and D. If you connect directly to the table, user role 1 will have permissions to columns A-D. So a View can be created which only includes columns A and B.
Example2: Other DoD rules include things like capturing who last changed the data. Many times this is accomplished by Triggers which save the username/datetime to columns in the table for each row. Obviously this isn't something a user or the program should have access to. With direct access to the tables, you can't remove this permission. So you create a view of all columns except for those two.
As for the question, the database connections won't care whether you're talking to a view or table, they'll behave the same as far as the program is concerned.
Thanks. Sean.
Well, that's one way to prevent SQL injection, I suppose, but man, leave it to the U.S. Government to apply asinine rules to what should be simple things.
The restriction basically requires the website to be readonly. Presumably, this applies only to public-facing properties, so you could still allow direct access to the database on an internal application (otherwise, I have no idea how you would actually ever persist anything). Regardless, Entity Framework can handle this easily. As far as using views go, you don't really need to do much special. If you use Code First with an existing database and name your views such that they follow EF conventions (pluralized versions of the entity names), then you wouldn't have to really do anything special.
To make EF function with an existing database you just have to modify the constructor of your DbContext subclass to 1) reference the connection string explicitly and 2) disable the initializer:
public class ApplicationContext : DbContext
{
public ApplicationContext()
: base("ConnectionStringName")
{
Database.SetInitializer<ApplicationContext>(null);
}
// DbSets here
}
If your view names don't line up with EF conventions, then you can explicitly state what your entity should reference:
[Table("awesomefooview")]
public class Foo
{
...
}
Technically, you'll still have Entity Framework API methods that allow write, but since the backing is a view, these will raise exceptions if run. If you can do something like run a stored procedure to make changes, which sort of indirectly allows write access, but technically the application itself isn't touching the database, then you can also make Entity Framework use these. See Code First Insert/Update/Delete Stored Procedures for more information (unfortunately, only EF6 and up, though).
