March 12, 2015 at 10:49 am
I have a question about SQL server database user and roles permission best practice.
I see a lot of topic talking about SQL server security best practice, but very few of talking about user and role permission best practice.
As I understand, if we have a web/database application, usually we create an application user, put it into a role, that has execute permission to stored procedures of s schema.
Everything like select, insert, delete, update needs to write as a stored procedure.
The problems is we have a hundred databases, we need to write a lot of procedures, and also modern design of .net applications that uses entity framework, they create logical models in visual studio, and generated table , code, objects, and queries automatically, it seems not necessarily need to use stored procedures.
Any recommedations or good articles about best practice of database role or user permissions setup for web applications?
Thanks
March 12, 2015 at 1:03 pm
The best place to go for this type of information is Denny Cherry. Here's his book. He also as lessons on Pluralsight. The new version of the book is due in May.
App derived database code does not substitute for good security. In fact, lots and lots of bad security practices come out of app derived code. I'd suggest getting a very good understanding of the phrase "SQL Injection" before you completely surrender your databases to app dev.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2015 at 2:00 pm
Thanks,
But the modern design of web applications now, for example using entity framework, it will be easier to generate code for developers, and all the commands are in the code. That is how entity framework works for them.
Or should we tell decelopers not to use entity framework to develop web applications?
Thanks
March 12, 2015 at 3:09 pm
No, entity framework, nhibernate and the other ORM tools are fine. But, they don't negate the need for security in any way. In fact, it makes it more important. Those tools can use parameterized queries as well as stored procedures. You can still do security, and you have to. You can't ignore it, or you'll make the news as the next big data leak.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2015 at 3:18 pm
Thanks,
Can you elaborate a little about entity framework can use parameterized queries as well as stored procedures, instead of direct query?
Thanks
March 12, 2015 at 3:33 pm
The example here shows how to use a parameterized query. There are others out there. It's a fairly standard approach. And here's a way to use procedures. Other examples of this. Using an ORM tool doesn't preclude controlling security, using procedures, and, most importantly, avoiding SQL Injection.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2015 at 4:00 pm
Thank you, that is very good information, I will read those to see if I come up with more questions.
March 12, 2015 at 4:23 pm
By reading this article, as I understand, it seems telling us for new applications the new way is preferred, only for existing or legacy database that we still can use entity framework with stored procedures.
see below: Quote first paragraph:
entity framework makes data access as simple as accessing properties of a class. Another is that it can automatically generate database commands – for queries as well as database changes – on your behalf by reading your Entity Data Model and supporting metadata about the database schema. For many developers, especially those beginning brand new applications with brand new databases, removing the need to write so much redundant code and work out how to express the database commands is a huge productivity improvement.
However, there are many more developers who are writing applications against legacy databases that are replete with important data logic in views and stored procedures. You certainly want to leverage these for a variety of reasons – corporate mandate, you have the benefit of database gurus in house, or some other good cause –while continuing to gain the many other benefits of the Entity Framework (EF) in your application
If so, my question is what is the best way to implement permissions if commands(update, insert etc) are used in entity framework code, previously when using stored procedures we just need to grant execute to stored procedures, if using the commands in code, what permissions should we grant, select, update, insert.. seem all needed. or dbreader, dbwriter, execute.
THanks,
March 13, 2015 at 6:04 am
Don't get stuck on either/or approaches with EF and procedures. It's both. You can, and depending on the code in question, should, be able to code using straight EF queries for most stuff and switch in the procedures as needed.
As far as security goes, you will have to expose more through EF. No question. But, you'll have to work with both the business and the development teams to ensure that you're meeting the proper level of security and that you're avoiding SQL Injection.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2015 at 7:44 am
Grant Fritchey (3/12/2015)
The best place to go for this type of information is Denny Cherry. Here's his book. He also as lessons on Pluralsight. The new version of the book is due in May.App derived database code does not substitute for good security. In fact, lots and lots of bad security practices come out of app derived code. I'd suggest getting a very good understanding of the phrase "SQL Injection" before you completely surrender your databases to app dev.
+1
That book rocks! And of course, Denny rocks too! lol ...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply