Do you allow Entity Framework? / Database Security Permissions

Question

Do you allow Entity Framework? / Database Security Permissions

SkyMac

Mr or Mrs. 500

Points: 539
More actions
July 21, 2013 at 6:37 pm

#395175

I know this topic has been talked a lot but, i'm having this issue right now, the Developers team at work want to start using Entity framework, and that would mean to low down database security because we'd have to assign permissions over tables or views. Untill now, we only assigned permissions on SPs. I know that EF can be used with SPs but developers are like hypnotized by microsoft and say all the time that using EF is more performant than SPs and bla bla bla.
I'm only worried on having to trust our DBs security a little more on what they develop because of the fact that i won't be seeing querys like before. I have to give them an answer about this issue, so:
my question, FOR DBAs: Do you allow EF on your databases and assigning permissions over tables or views? What is your experience with it as a DBA?
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 1

As a DBA in organizations where I have worked, we don't own the data, the business do. The only real reason they would need access to live data is if data validation needs to take place. In this scenario, the business need to sign up to and accept this risk.

In your position, I would speak to the business \ end user about how sensitive the data is. If it is sensitive data obviously they shouldn't have access to it. DDL_admin is as far as I would allow applications to go permission wise on live. In development, if the database has been desensitized, they can do their development work here and have slightly higher permission.

SkyMac Mr or Mrs. 500 Points: 539 More actions · Answer 2

i'm not talking about developer user access to data.... i'm talking about EF applications and permissions for those apps... but thanks for your time anyway.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

If system architecture wants to use ORM methodology (eg. Entity Framework, NHibernate and any other) then, as DBA, you should provide relevant permission/access model.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Lowell SSC Guru Points: 323504 More actions · Answer 4

for me, it doesn't matter if it's entity framework or an ADO connection or anything else. it doesn't make a real difference in what it's going to do.

in our shop, our application typically connects as a user with db_DataReader , db_DataWriter, and EXECUTE permissions;

we don't grant it db_ddladmin, so that login cannot create new tables or procs or anything, we feel that anything like that has to go though the DBA.

we also grant it VIEW DEFINITION as well, so it can see the structure of all objects.

this is the basic permissiosn for the role,a nd any user(s) the applciation would use go into this riole:

CREATE ROLE [AppAccess]

EXEC sp_addrolemember N'db_datareader', N'AppAccess'

EXEC sp_addrolemember N'db_datawriter', N'AppAccess'

GRANT EXECUTE TO [AppAccess]

Grant View Definition ON SCHEMA::[dbo] To [AppAccess]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DiverKas SSCrazy Points: 2049 More actions · Answer 5

SkyMac (7/21/2013)
I know this topic has been talked a lot but, i'm having this issue right now, the Developers team at work want to start using Entity framework, and that would mean to low down database security because we'd have to assign permissions over tables or views. Untill now, we only assigned permissions on SPs. I know that EF can be used with SPs but developers are like hypnotized by microsoft and say all the time that using EF is more performant than SPs and bla bla bla.
I'm only worried on having to trust our DBs security a little more on what they develop because of the fact that i won't be seeing querys like before. I have to give them an answer about this issue, so:
my question, FOR DBAs: Do you allow EF on your databases and assigning permissions over tables or views? What is your experience with it as a DBA?
Thanks!

Having worked on both sides of this fence, I can say without hesitation EF and ANY of the ORM's are NOT more performant that T-SQL directly. Having said that, it is really convenient to work with the DB using EF than all of the previous incarnations of data access.

EF using procs is viable and probably the safest route, but the ability to have EF roll classes based on the data objects is attractive to some. Laziness mostly.

Create a role and assign all the tables and views to it and give that read write access, then assign the role to the EF login. That way you can cherry pick which ones they get when they attach the EF container.

I like Lowells advice where you cant get out of making the devs only use SP's.