Previously dealt with MySQL server and clear setting permissions for accounts and objects. Now I understand the MSSQL server and the Entity Framework. I created a login (login, password) , attached a user to this name and created a schema (owner dbo / sa). I have not had time to study all aspects of the internal workings of the MSSQL server, and even this is not the main goal.

Question : What minimum permissions need to be specified in the scheme for this login so that EF can fully function (CRUD and other typical tasks and internal processes) without touching other databases (in general so as not to worry about security)

Perhaps I miss some more important points. I will be glad to accompanying advice!

  • I have not climbed there for a long time, but there seem to be roles for the user or login for a specific database, something like db_owner - to get full access to the database. But this is not accurate :) - tym32167
  • @ tym32167 is true, you can add the db_owner role to the user, but I understand that this will give full control over a particular database. I would like the opposite effect, the minimum necessary access for EF operation - Novice

1 answer 1

EF is just a way to build queries to the database.

The minimum necessary access depends on what exactly you are going to do through EF. “Full” is a very vague definition. Usually, “fully” means “.. including migrations”. In migrations, you can write any code you like. Therefore, for the "full work" in general, you will have to issue db_owner.

But you can tighten the nuts.

If you only need to select data - db_datareader. If also to change - db_datawriter. If you call the store, issue the rights to the specific store. If you want strict control - expose the rights to each table and each column. If you want to change the scheme (migrations) - ddl_admin. If <something else> - give the rights to it something else .

Those. setting permissions for EF is no different for setting permissions for any other client code and any other client application.

  • I would say that the phrase настройка прав для EF basically does not make sense. There is a user right, which is used to connect to the database. - tym32167
  • @ tym32167 approximately it is said in my answer, only in more detail :) - PashaPash ♦
  • Thanks for the detailed answer. I will try again in two words: I heard that EF somehow monitors the stretched entities while working with them (for example, another transaction partially changed the data). I don’t know how it was done by ORM / MSSQL, so I wanted to learn about such pitfalls and their dependencies in the form of permissions. It is clear that you can write any kind of SQL code affecting other bases and objects, but the question was specifically the typical use of this particular ORM framework (migration, crud, tracking?). - Newcomer
  • @ tym32167 It is clear that the rights are granted to the user, through which the EF works, making up the SQL from my C # code. The question is in the combination of permissions required for this code. Well, perhaps hints on exactly how to achieve this better / more correctly. - Newbie
  • one
    @ Beginner tracking works at the .NET level, by comparing objects selected through the context with their actual values. in terms of SQL Server - EF does the usual CRUD - PashaPash ♦