Hi, I have a question for database permission.
What is the best practice to setup permissions for an application account?
I know it is to stick on the rule : that we should only grant permissions needed. keep minimum as possible.
And db_owner is not a good option even the database is only used internally.
I usually setup a role that can execute stored procedures and functions.
But in recent .net applications, developers use entity frame work which generate SQL commands in the code. So they may not necessarily use stored procedures.
In this case they also need have select, update, insert, delete permissions to the database for the application execute account.
What is the best practice to setup permissions in this case?
Thanks,
permission for application account
March 20th, 2015 6:27pm
Best practice is to have a security policy within the organisation. If that policy is all stored procedures, it is all stored procedures, and developers will have to comply.
If you don't have a security policy, you will have to dance along the developer's pipes. Which means that in this case, the application account needs to be member of db_datareader and db_datawriter. You will still grant EXECUTE permission on any stored procedure.
I can't say that I'm a great fan of EF or any other OR/M, but admittedly one advantage is that if used consistently, the risk for SQL injection is about nil.
Free Windows Admin Tool Kit Click here and download it now
March 20th, 2015 6:54pm
>>>>What is the best practice to setup permissions in this case?
In that specific case (especially if it uses dynamic sql as well) I would go with db_owner.... BUT better option would be re-write all ad-hoq queries as stored procedures if it is possible in your case.
March 23rd, 2015 2:22am
This topic is archived. No further replies will be accepted.
Other recent topics
