ASP.NET Identity With Oracle Database

In this topic i will try to give you some idea about using ASP.NET identity with Oracle Database for our MVC applications.

• At the beginning, I have done some research about suing identity with Oracle DB. There few useful articles i found, but not with complete details. Today i will try to cover most of the steps in this article.

• Below script include identity tables for Oracle DB. You can find some scripts on the internet, but here my good friend (DB guy) at one of my work place, taught me to recreate those scripts in a better way for Oracle DB. You can run those scripts in your DB. (If you have any problems running this scripts, let me know in the comment section).

 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;  

• After creating identity tables you can create ASP.NET MVC Project with Individual Use Account Option

• Then go to NuGet Package Manager and install Oracle.ManagedDataAccess and Oracle.ManagedDataAccess.EntityFramework.

• Installation process will update your Web.Config file with relevant settings.
• In this article i assume you already have your data source and ODA (Oracle Data Access) Drivers are already installed in your PC.
• Update your oralce connection string in web.config file with credentials.
• Remember to comment or remove existing connection string that by default come with project.

• Ok, then go to IdentityModels.cs  in Models folder and add  OnModelCreating method.

• Here is the complete code (IdentityModels.cs)

 using System.Data.Entity;  
 using System.Security.Claims;  
 using System.Threading.Tasks;  
 using Microsoft.AspNet.Identity;  
 using Microsoft.AspNet.Identity.EntityFramework;  
 namespace OracleIdentity.Models  
 {  
   // You can add profile data for the user by adding more properties to your ApplicationUser class, please visit https://go.microsoft.com/fwlink/?LinkID=317594 to learn more.  
   public class ApplicationUser : IdentityUser  
   {  
     public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser> manager)  
     {  
       // Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType  
       var userIdentity = await manager.CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);  
       // Add custom user claims here  
       return userIdentity;  
     }  
   }  
   public class ApplicationDbContext : IdentityDbContext<ApplicationUser>  
   {  
     public ApplicationDbContext()  
       : base("DefaultConnection", throwIfV1Schema: false)  
     {  
     }  
     public static ApplicationDbContext Create()  
     {  
       return new ApplicationDbContext();  
     }  
     protected override void OnModelCreating(DbModelBuilder modelBuilder)  
     {  
       base.OnModelCreating(modelBuilder);  
       modelBuilder.HasDefaultSchema("AAAAA"); //uppercase  
       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");  
     }  
   }  
 }  

• Remember to use uppercase for Default Schema Name

modelBuilder.HasDefaultSchema("AAAAA"); //uppercase  

• Rebuild the Project > Debug.
• Then go to register and try to register user.

• If the connection string details correct you will redirect to Home View with after successful login.

• You can check your Oracle DB ASPNETUSERS table.

(Using Toad)

• Or simply use "SELECT * FROM ASPNETUSERS WHERE EMAIL = 'youremail@mail.com';"
• If you have any questions related to article. kindly let me know in the comment section.

      Enjoy, Thank you !