I have been helping a small to medium sized business implement a new CRM using entity framework core for sql server. They are very protective over their sql database to the point where they refuse to grant permissions to most of the tables and only grant some permissions on some columns (this is how they have worked in the past, using views to do all data operations).
I have argued the case for giving entity framework permissions, but have been refused on the grounds that large sized enterprises would under no circumstances have credentials that have full access to the live database.
what are the best practices for database access when releasing to a production environment? Do major organizations normally have a username and password that has full control over both the data and also the schema for migrations, or is there a correct approach to limit the access that the ORM has ?
I agree with Dan. There's not much point in using EF if you're creating stored procedures for all CRUD operations. For any sizable system that's a lot of procedures. I rewrote some older code, moving from all stored procedures to EF Core, giving EF full read/write access. Stored procedures are easier in that you know absolutely everything going on in the database. No surprises. But a lot of code to write and maintain. EF Core lets you focus on using the data, without having to write much SQL code. The way I see it, the previous system still had complete data access. It had to or it couldn't manage the data. It was just broken up into hundreds of stored procedures instead of a single data context. In the end, I didn't see EF Core's privileges as much of an additional security risk. If the idea is that the complexity of the procedures kept it from being attacked, I think that's a weak argument. Once an attacker has system access one might presume they can figure out the rest.