Documentation is missing information on how to set up database users and rights

[SetTrend]

Setting up a fresh database installation (using Entity Framework Core EnsureCreated()), I need to create a number of database users and grant appropriate rights to them for all objects that ModelBuilder is creating (e.g. entities, sequences etc., but also user permissions, like CREATE SESSION system privilege).

My goal is to end up with a single DBA user for later database migration and a less privileged application user for running the app.

I've been looking for corresponding ModelBuilder extension methods, but couldn't find any.

As far as I can see, documentation lacks information on how to create database users and grant privileges using EF Core Code-first.

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 1b15cd4c-9cba-c0e2-7618-7d621a3c5936
• Version Independent ID: f4681bc6-57be-6e69-cad9-776ce64d93bd
• Content: Overview of Entity Framework Core - EF Core
• Content Source: entity-framework/core/index.md
• Product: entity-framework
• Technology: entity-framework-core
• GitHub Login: @ajcvickers
• Microsoft Alias: avickers

[roji]

@SetTrend EF Core does not provide any extension methods for configuring users or rights; the recommended way to do this is simply to add whatever you need as raw SQL in your migrations. This is easy to do and allows you to specify exactly what you need, whereas EF Core extension methods would add almost no value over that (and would have to cover all the possible configuration options of all possible databases, which isn't practical).

[SetTrend]

I see ...

So, there isn't an intrinsic raw SQL feature without migrations? I'd require an initial seed, so automated tests can easily be run.

whereas EF Core extension methods would add almost no value over that (and would have to cover all the possible configuration options of all possible databases, which isn't practical).

Are you sure? I could envision that it would be beneficial, comprehensive and simple to add a Users feature to ModelBuilder. Imagine a code like this:

DbUser user1 = modelBuilder.Users.Add('userId_1', 'password_1');
DbUser user2 = modelBuilder.Users.Add('userId_2', 'password_2');

user1.AddPrivilege("CREATE TABLE");   // use plain strings for specific rights. Final syntax would be provider specific, e.g.: @$"GRANT {permissionList} TO {userId}"

user2.AddPrivileges(new string[]   // use plain strings for specific rights. Final syntax would be provider specific, e.g.: @$"GRANT {permissionList} TO {userId}"
  { "CREATE TABLE"
  , "ALTER TABLE"
  });

modelBuilder.Entity<Blog>()
  .GrantUser(user1, DbObjectRightsEnum.Create | DbObjectRightsEnum.Read | DbObjectRightsEnum.Update | DbObjectRightsEnum.Delete)

  .GrantUser(user2, DbObjectRightsEnum.Read)

  .Property(b => b.Url).IsRequired();

Same for Roles, of course:

DbRole appUserRole1 = modelBuilder.Roles.Add('role_name_1');
DbRole appUserRole2 = modelBuilder.Roles.Add('role_name_2');
DbUser user1 = modelBuilder.Users.Add('userId_1', 'password_1', new DbRole[] { appUserRole1, appUserRole2 });
DbUser user2 = modelBuilder.Users.Add('userId_2', 'password_2', new DbRole[] { appUserRole1 });

appUserRole1.AddPrivilege("CREATE SESSION");   // use plain strings for specific rights. Final syntax would be provider specific, e.g.: @$"GRANT {permissionList} TO {role_name}"
appUserRole1.AddPrivilege("CREATE TABLE");

appUserRole2.AddPrivileges(new string[]   // use plain strings for specific rights. Final syntax would be provider specific, e.g.: @$"GRANT {permissionList} TO {role_name}"
  { "CREATE SESSION"
  , "CREATE TABLE"
  , "ALTER TABLE"
  });

modelBuilder.Entity<Blog>()
  .GrantRole(appUserRole1, DbObjectRightsEnum.Create | DbObjectRightsEnum.Read | DbObjectRightsEnum.Update | DbObjectRightsEnum.Delete)

  .GrantRole(appUserRole2, DbObjectRightsEnum.Read)

  .Property(b => b.Url).IsRequired();

[roji]

So, there isn't an intrinsic raw SQL feature without migrations?

That's true - we have/had an issue tracking it somewhere but I can't find it right now.

Are you sure? I could envision that it would be beneficial, comprehensive and simple to add a Users feature to ModelBuilder. Imagine a code like this [...]

What would be the actual advantage of the code above, as opposed to using raw SQL instead? The proposed .NET API would pretty much duplicate the SQL syntax, providing very little value and most likely not covering the DDL SQL 100% completely and correctly. Our approach has been to not attempt to cover the entire possible DDL via .NET APIs, but rather to provide APIs for cases where it's required (e.g. CREATE TABLE), or where the use-case is very common and also database-agnostic (e.g. CREATE INDEX).

use plain strings for specific rights. Final syntax would be provider specific

I'm pretty sure that the concept of "privilege" and most other stuff here would be provider-specific. The above syntax would most likely be SQL Server-specific, meaning we'd have to do it again for each database.

[SetTrend]

What would be the actual advantage of the code above, as opposed to using raw SQL instead?

There are a couple of reasons:

1. No media break. — No error-prone SQL code involved.
2. Portability across database systems.
3. All database settings could be done programmatically.
4. Whenever EnsureCreated() is called, the database is fully been set up.

Actually, the above reasons are basically the reasons for using Entity Framework in the first place.

CREATE TABLE, ALTER TABLE, for example, are quite common rights across database systems. Same for user credentials and roles.

Let me give an example:

In my customer's new project I'm supposed to support a legacy Oracle DB while at the same time I need to support SQL server, because some of my customer's clients want to user Oracle DB while others want to use SQL Server.

So, I created an abstract EF database context by reverse-engineering, building all common entities and properties. Then I derived provider specific contexts, adding provider specific features, like changing data types.

In one of those two database contexts I already have to add amendments to what the base builder has created:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  base.OnModelCreating(modelBuilder);

  // Convert Oracle data types to SQL Server data types
  foreach (IMutableEntityType entity in modelBuilder.Model.GetEntityTypes())
    foreach (IMutableProperty prop in entity.GetDeclaredProperties())
    {
      string? columnTypeSql = prop.GetColumnType();
      string? columnDefaultSql = prop.GetDefaultValueSql();

      if (!string.IsNullOrWhiteSpace(columnTypeSql))
      {
        string newColumnType = columnTypeSql.ToUpper().Trim() switch
        {
          "BLOB" => "VARBINARY",
          "DATE" => "DATETIME",
          "XMLTYPE" => "XML",
          "NUMBER" => "INT",
          _ => columnTypeSql
        };

        if (newColumnType != columnTypeSql) prop.SetColumnType(newColumnType);
      }

      if (!string.IsNullOrWhiteSpace(columnDefaultSql))
      {
        string newColumnDefault = columnDefaultSql.ToUpper().Trim() switch
        {
          "SYSDATE" => "GETDATE()",
          _ => columnDefaultSql
        };

        if (newColumnDefault != columnDefaultSql) prop.SetDefaultValueSql(newColumnDefault);
      }
    }
}

For completing database set-up, right now I have to ...

1. add an additional method to the database context in order to finish database initialization for adding rights to each and every single database table (i.e. Entity) manually. Per user!
2. use error-prone SQL strings. (Which is actually quite a huge amount of strings.)
3. call my additional method manually as there is no hook I could add to EnsureCreated().
4. document this additional member function to the team, so they won't forget to call.

As I was showing above, EF Core can easily abstract provider details away from the programmer.

I believe these are plenty of reasons for providing user, role, privilege features to EF Core.

[roji]

Portability across database systems.

This is the crux of the matter - and unfortunately it simply doesn't seem to be true; while at a superficial glance, databases indeed have users and users can be granted privileges, when looking deeper there are some important differences.

As an example, compare SQL Server CREATE USER, PostgreSQL CREATE USER and Oracle CREATE USER. The various options are quite different, since the database capabilities around user, role and privilege management are different; that's the unfortunate reality of world of databases. In contrast, SQL query syntax and behavior are better standardized and generally tend to be similar across databases (although with important exceptions).

To summarize:

• If we provide a limited common denominator covering multiple databases (assuming a useful one actually exists!), any user needing database-specific functionality would still be stuck with using raw SQL.
• We could cover the entire realm of possibilities for each database, but that's a lot of effort for each database.
• In general, correctly modeling all user/privilege management in EF Core - with all the options etc. - would be a substantial amount of work.
• I do agree there's some value in an EF user/privilege management API - .NET APIs instead of raw SQL does mean a bit more safety and less room for errors. But given the investment required and database diversity, that value simply seems far too low to make sense.

So, I created an abstract EF database context by reverse-engineering, building all common entities and properties. Then I derived provider specific contexts, adding provider specific features, like changing data types.

I understand this isn't a perfect experience, but that seems quite orthogonal to the issue being discussed above. If you need to manage users and privileges, these need to be specified in any case - the question in this issue is whether that's done in raw SQL or via an EF API.

[SetTrend]

Absolutely agree.

I'm quite sure there must be a rather straightforward way for providing a minimum denominator solution that can be expanded later on.

I feel very much inclined to create a NuGet package - just like we did with the EFCore.CheckConstraints package - and start with a minimum common denominator NuGet package for all databases for a start: EFCore.UserPrivileges.

Later on, additional Nuget packages could then be created for each of the database systems (e.g. EFCore.UserPrivileges.SqlServer), adding additional features as overloads to the - then existing - DbUser/DbRole class or the GrantTo() Fluid API method.

I'm pretty much aware that this isn't done in a day or two. But I believe it's worth it.

As of today, I'm not quite sure where to start, but yesterday I already begun working into the EFCore.CheckConstraints package again. If you allowed me to ask you for your valuable hints from time to time that would be a great help and I would very much appreciate that.

[roji]

Triage decision: this isn't something we plan to implement in EF Core - there's not enough common ground across databases, and the value is generally limited given that raw SQL can be used (and given the investment required).

Of course, something may be doable in a nuget package, although unlike EFCore.CheckConstraints, support for this would probably need to interact with other parts of EF Core which aren't necessarily easily extensible to plugins (e.g. the migrations SQL provider).