ASP.NET MVC 5 Storing identity users in Oracle database
Finally got Identity 2.0 to work with my Oracle DB. Here's what I did:
The following steps work if you don't want to make any changes to the default IdentityUser (ex. if you're ok with having a char ID instead of int or long) and just want the tables on your existing Oracle schema.
1) Create Identity tables on Oracle. You can change the table names if you want to, just make sure to include the necessary columns for Identity to work with it. You can also add any extra columns you may need on your application (script originally found on Devart, I copied it to a gist in case URL breaks):
Gist here
2) If you're using an EDMX file, you need to add a new connection string cause the one that gets generated automatically won't work, you need a standard connection string. Try following this template:
<add name="IdentityContext" connectionString="Data Source=localhost:1521/xe;PASSWORD=password;USER ID=username;" providerName="Oracle.ManagedDataAccess.Client" />3) Tell your ApplicationDbContext to use your new connectionString
public ApplicationDbContext(): base("IdentityContext", throwIfV1Schema: false){}4) Tell Identity to use your existing schema and tables. Add this method inside the ApplicationDbContext definition found in IdentityModels.cs:
protected override void OnModelCreating(DbModelBuilder modelBuilder){base.OnModelCreating(modelBuilder); // MUST go first.modelBuilder.HasDefaultSchema("YOUR_SCHEMA"); // Use uppercase!modelBuilder.Entity<ApplicationUser>().ToTable("AspNetUsers");modelBuilder.Entity<IdentityRole>().ToTable("AspNetRoles");modelBuilder.Entity<IdentityUserRole>().ToTable("AspNetUserRoles");modelBuilder.Entity<IdentityUserClaim>().ToTable("AspNetUserClaims");modelBuilder.Entity<IdentityUserLogin>().ToTable("AspNetUserLogins");}5) Rebuild and thats it!
You Can Use This Script to Create identity table Properly in Oracle DB
`
CREATE TABLE incapp.AspNetRoles ( Id VARCHAR2(128) NOT NULL, Name VARCHAR2(256) NOT NULL ) pctfree 20 pctused 70 tablespace INCTBL; create unique index incapp.pk_aspnetroles on incapp.AspNetRoles ( id ) pctfree 10 tablespace incidx; alter table incapp.AspNetRoles add ( constraint pk_aspnetroles primary key (id) ); create public synonym AspNetRoles for incapp.AspNetRoles; grant select,insert,update,delete on aspnetroles to webapps; CREATE TABLE incapp.AspNetUserRoles ( UserId VARCHAR2(128) NOT NULL, RoleId VARCHAR2(128) NOT NULL ) pctfree 20 pctused 70 tablespace INCTBL; create unique index incapp.pk_AspNetUserRoles on incapp.AspNetUserRoles ( UserId, RoleId ) pctfree 10 tablespace incidx; alter table incapp.AspNetUserRoles add ( constraint pk_AspNetUserRoles primary key (UserId, RoleId) ); create public synonym AspNetUserRoles for incapp.AspNetUserRoles; grant select,insert,update,delete on incapp.AspNetUserRoles to webapps; CREATE TABLE incapp.AspNetUsers ( Id VARCHAR2(128) NOT NULL, Email VARCHAR2(256) NULL, EmailConfirmed NUMBER(1) NOT NULL, PasswordHash VARCHAR2(256) NULL, SecurityStamp VARCHAR2(256) NULL, PhoneNumber VARCHAR2(256) NULL, PhoneNumberConfirmed NUMBER(1) NOT NULL, TwoFactorEnabled NUMBER(1) NOT NULL, LockoutEndDateUtc TIMESTAMP(7) NULL, LockoutEnabled NUMBER(1) NOT NULL, AccessFailedCount NUMBER(10) NOT NULL, UserName VARCHAR2(256) NOT NULL ) pctfree 20 pctused 70 tablespace INCTBL; create unique index incapp.pk_AspNetUsers on incapp.AspNetUsers ( id ) pctfree 10 tablespace incidx; alter table incapp.AspNetUsers add ( constraint pk_AspNetUsers primary key (id) ); create public synonym AspNetUsers for incapp.AspNetUsers; grant select,insert,update,delete on incapp.AspNetUsers to webapps; CREATE TABLE AspNetUserClaims ( Id NUMBER(10) NOT NULL, UserId VARCHAR2(128) NOT NULL, ClaimType VARCHAR2(256) NULL, ClaimValue VARCHAR2(256) NULL ) pctfree 20 pctused 70 tablespace INCTBL; create unique index incapp.pk_AspNetUserClaims on incapp.AspNetUserClaims ( id ) pctfree 10 tablespace incidx; alter table incapp.AspNetUserClaims add ( constraint pk_AspNetUserClaims primary key (id) ); create public synonym AspNetUserClaims for incapp.AspNetUserClaims; grant select,insert,update,delete on incapp.AspNetUserClaims to webapps; CREATE SEQUENCE incapp.AspNetUserClaims_SEQ; create public synonym AspNetUserClaims_SEQ for incapp.AspNetUserClaims_SEQ; CREATE OR REPLACE TRIGGER incapp.AspNetUserClaims_INS_TRG BEFORE INSERT ON incapp.AspNetUserClaims FOR EACH ROW BEGIN :NEW.Id := AspNetUserClaims_SEQ.NEXTVAL; END; / CREATE TABLE incapp.AspNetUserLogins ( LoginProvider VARCHAR2(128) NOT NULL, ProviderKey VARCHAR2(128) NOT NULL, UserId VARCHAR2(128) NOT NULL ) pctfree 20 pctused 70 tablespace INCTBL; create unique index incapp.pk_AspNetUserLogins on incapp.AspNetUserLogins ( LoginProvider, ProviderKey, UserId ) pctfree 10 tablespace incidx; alter table incapp.AspNetUserLogins add ( constraint pk_AspNetUserLogins primary key (LoginProvider, ProviderKey, UserId) ); create public synonym AspNetUserLogins for incapp.AspNetUserLogins; grant select,insert,update,delete on incapp.AspNetUserLogins to webapps; create unique index incapp.RoleNameIndex on incapp.AspNetRoles ( name ) pctfree 10 tablespace incidx; alter table incapp.AspNetRoles add ( constraint uq_RoleNameIndex unique (name) ); create index incapp.IX_AspNetUserRoles_UserId on incapp.AspNetUserRoles ( UserId ) pctfree 10 tablespace incidx; create index incapp.IX_AspNetUserRoles_RoleId on incapp.AspNetUserRoles ( RoleId ) pctfree 10 tablespace incidx; create unique index incapp.UserNameIndex on incapp.AspNetUsers ( UserName ) pctfree 10 tablespace incidx; create index incapp.IX_AspNetUserClaims_UserId on incapp.AspNetUserClaims ( UserId ) pctfree 10 tablespace incidx; create index incapp.IX_AspNetUserLogins_UserId on incapp.AspNetUserLogins ( UserId ) pctfree 10 tablespace incidx; ALTER TABLE incapp.AspNetUserRoles ADD CONSTRAINT FK_UserRoles_Roles FOREIGN KEY (RoleId) REFERENCES incapp.AspNetRoles (Id) ON DELETE CASCADE; ALTER TABLE incapp.AspNetUserRoles ADD CONSTRAINT FK_UserRoles_Users FOREIGN KEY (UserId) REFERENCES incapp.AspNetUsers (Id) ON DELETE CASCADE; ALTER TABLE incapp.AspNetUserClaims ADD CONSTRAINT FK_UserClaims_Users FOREIGN KEY (UserId) REFERENCES incapp.AspNetUsers (Id) ON DELETE CASCADE; ALTER TABLE incapp.AspNetUserLogins ADD CONSTRAINT FK_UserLogins_Users FOREIGN KEY (UserId) REFERENCES incapp.AspNetUsers (Id) ON DELETE CASCADE;`
Modify ConnectionString Like Below
<add name="DBContext" connectionString="Data Source=AAAA:1521/AA;PASSWORD=AAAA;USER ID=AAAA;" providerName="Oracle.ManagedDataAccess.Client" />Then Modify your OnModelCreating method Like This
`
protected override void OnModelCreating(DbModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.HasDefaultSchema("AAAAA"); modelBuilder.Entity<ApplicationUser>() .ToTable("ASPNETUSERS").Property(p => p.UserName).HasColumnName("USERNAME"); modelBuilder.Entity<ApplicationUser>() .ToTable("ASPNETUSERS").Property(p => p.AccessFailedCount).HasColumnName("ACCESSFAILEDCOUNT"); modelBuilder.Entity<ApplicationUser>() .ToTable("ASPNETUSERS").Property(p => p.LockoutEnabled).HasColumnName("LOCKOUTENABLED"); modelBuilder.Entity<ApplicationUser>() .ToTable("ASPNETUSERS").Property(p => p.LockoutEndDateUtc).HasColumnName("LOCKOUTENDDATEUTC"); modelBuilder.Entity<ApplicationUser>() .ToTable("ASPNETUSERS").Property(p => p.TwoFactorEnabled).HasColumnName("TWOFACTORENABLED"); modelBuilder.Entity<ApplicationUser>() .ToTable("ASPNETUSERS").Property(p => p.PhoneNumberConfirmed).HasColumnName("PHONENUMBERCONFIRMED"); modelBuilder.Entity<ApplicationUser>() .ToTable("ASPNETUSERS").Property(p => p.PhoneNumber).HasColumnName("PHONENUMBER"); modelBuilder.Entity<ApplicationUser>() .ToTable("ASPNETUSERS").Property(p => p.SecurityStamp).HasColumnName("SECURITYSTAMP"); modelBuilder.Entity<ApplicationUser>() .ToTable("ASPNETUSERS").Property(p => p.PasswordHash).HasColumnName("PASSWORDHASH"); modelBuilder.Entity<ApplicationUser>() .ToTable("ASPNETUSERS").Property(p => p.EmailConfirmed).HasColumnName("EMAILCONFIRMED"); modelBuilder.Entity<ApplicationUser>() .ToTable("ASPNETUSERS").Property(p => p.Email).HasColumnName("EMAIL"); modelBuilder.Entity<ApplicationUser>() .ToTable("ASPNETUSERS").Property(p => p.Id).HasColumnName("ID"); modelBuilder.Entity<IdentityUserRole>() .ToTable("ASPNETUSERROLES").Property(p => p.RoleId).HasColumnName("ROLEID"); modelBuilder.Entity<IdentityUserRole>() .ToTable("ASPNETUSERROLES").Property(p => p.UserId).HasColumnName("USERID"); modelBuilder.Entity<IdentityUserLogin>() .ToTable("ASPNETUSERLOGINS").Property(p => p.UserId).HasColumnName("USERID"); modelBuilder.Entity<IdentityUserLogin>() .ToTable("ASPNETUSERLOGINS").Property(p => p.ProviderKey).HasColumnName("PROVIDERKEY"); modelBuilder.Entity<IdentityUserLogin>() .ToTable("ASPNETUSERLOGINS").Property(p => p.LoginProvider).HasColumnName("LOGINPROVIDER"); modelBuilder.Entity<IdentityUserClaim>() .ToTable("ASPNETUSERCLAIMS").Property(p => p.Id).HasColumnName("ID"); modelBuilder.Entity<IdentityUserClaim>() .ToTable("ASPNETUSERCLAIMS").Property(p => p.UserId).HasColumnName("USERID"); modelBuilder.Entity<IdentityUserClaim>() .ToTable("ASPNETUSERCLAIMS").Property(p => p.ClaimType).HasColumnName("CLAIMTYPE"); modelBuilder.Entity<IdentityUserClaim>() .ToTable("ASPNETUSERCLAIMS").Property(p => p.ClaimValue).HasColumnName("CLAIMVALUE"); modelBuilder.Entity<IdentityRole>() .ToTable("ASPNETROLES").Property(p => p.Id).HasColumnName("ID"); modelBuilder.Entity<IdentityRole>() .ToTable("ASPNETROLES").Property(p => p.Name).HasColumnName("NAME"); } `
This Worked fine with my project.
You can find some interesting points in here. (ASP.NET MVC, EF with NgGet Oracle 12.2.1..)