Readdy Write  
0,00 €
Your View Money
Views: Count
Self 20% 0
Your Content 60% 0

Users by Links 0
u1*(Content+Views) 10% 0
Follow-Follower 0
s2*(Income) 5% 0

Count
Followers 0
Login Register as User

Hinzufügen eines Microsoft Identity Frameworks zu einer bestehenden Datenbank

16.02.2021 (👁25652)


 

Wenn man eine bestehende Asp.Net Core Anwendung besitzt und eine gesichertes User Verwaltungssystem einbinden möchte,

dann kann man eine Asp.Net Core ->Angular / React Anwendung mit Authentication / Identity erstellen.

Danach muss man nur noch die Datenbank per Update-Database an die bestehende Datenbank einbinden.

 

Das Identity Framework erstellt ein komplettes User Login/Logout Register System fĂŒr bestehende Anwendungen.

Die User Seiten sind natĂŒrlich verwaltbar.

Wichtig ist: auf der Serverseite sind keine Passwörter oder private Daten frei zugĂ€nglich auch nicht fĂŒr Systemadministratoren.

 

Die bestehende Datenbank muss man in der Datei appsettings.json einstellen

Hier die lokale Datenbank zur Website.

  "ConnectionStrings": {

    "DefaultConnection": "Server=.\\SQLEXPRESS;Database=codedocu_de;Trusted_Connection=True;MultipleActiveResultSets=true"

 

Im SQL Server Connectionstring

   //"Data" "Source=DESKTOP-F634M99\\SQLEXPRESS;Initial",

    //"Catalog=codedocu_de;Integrated" "Security=True;Connect",

    //"Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"

 

Dann startet man die Integration aus dem ASP.Net Core / Angular oder React Projekt

Indem man in der Package Manager Console eingibt:

Update-database

 

Fertig.. dadurch wird in die angebundene Datenbank das Identity Framework installiert

 

 

Folgende Tabellen werden per SQL Skript angelegt:

 

Identity Tabellen AspNetUser**

In der Datenbank findet man die neuen Tabellen als dbo.AspNet* Tabellen

 

1428ca0b..

raimund.popp@code...de

RAIMUND...DE

Raimund...de

RAIMUND...DE

True

AQAAAAEAACcQA..

..

b
.

NULL

False

False

NULL

True

0

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

 

CREATE TABLE [dbo].[AspNetUsers] (

    [Id]                   NVARCHAR (450)     NOT NULL,

    [UserName]             NVARCHAR (256)     NULL,

    [NormalizedUserName]   NVARCHAR (256)     NULL,

    [Email]                NVARCHAR (256)     NULL,

    [NormalizedEmail]      NVARCHAR (256)     NULL,

    [EmailConfirmed]       BIT                NOT NULL,

    [PasswordHash]         NVARCHAR (MAX)     NULL,

    [SecurityStamp]        NVARCHAR (MAX)     NULL,

    [ConcurrencyStamp]     NVARCHAR (MAX)     NULL,

    [PhoneNumber]          NVARCHAR (MAX)     NULL,

    [PhoneNumberConfirmed] BIT                NOT NULL,

    [TwoFactorEnabled]     BIT                NOT NULL,

    [LockoutEnd]           DATETIMEOFFSET (7) NULL,

    [LockoutEnabled]       BIT                NOT NULL,

    [AccessFailedCount]    INT                NOT NULL,

    CONSTRAINT [PK_AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC)

);

GO

CREATE NONCLUSTERED INDEX [EmailIndex]

    ON [dbo].[AspNetUsers]([NormalizedEmail] ASC);

GO

CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex]

    ON [dbo].[AspNetUsers]([NormalizedUserName] ASC) WHERE ([NormalizedUserName] IS NOT NULL);

 

 

AspNetUserRoles

CREATE TABLE [dbo].[AspNetUserRoles] (

    [UserId] NVARCHAR (450) NOT NULL,

    [RoleId] NVARCHAR (450) NOT NULL,

    CONSTRAINT [PK_AspNetUserRoles] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC),

    CONSTRAINT [FK_AspNetUserRoles_AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE,

    CONSTRAINT [FK_AspNetUserRoles_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE

);

GO

CREATE NONCLUSTERED INDEX [IX_AspNetUserRoles_RoleId]

    ON [dbo].[AspNetUserRoles]([RoleId] ASC);

 

 

AspNetUserLogins

CREATE TABLE [dbo].[AspNetUserLogins] (

    [LoginProvider]       NVARCHAR (128) NOT NULL,

    [ProviderKey]         NVARCHAR (128) NOT NULL,

    [ProviderDisplayName] NVARCHAR (MAX) NULL,

    [UserId]              NVARCHAR (450) NOT NULL,

    CONSTRAINT [PK_AspNetUserLogins] PRIMARY KEY CLUSTERED ([LoginProvider] ASC, [ProviderKey] ASC),

    CONSTRAINT [FK_AspNetUserLogins_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE

);

GO

CREATE NONCLUSTERED INDEX [IX_AspNetUserLogins_UserId]

    ON [dbo].[AspNetUserLogins]([UserId] ASC);

 

 

 

[AspNetRoleClaims]

CREATE TABLE [dbo].[AspNetRoleClaims] (

    [Id]         INT            IDENTITY (1, 1) NOT NULL,

    [RoleId]     NVARCHAR (450) NOT NULL,

    [ClaimType]  NVARCHAR (MAX) NULL,

    [ClaimValue] NVARCHAR (MAX) NULL,

    CONSTRAINT [PK_AspNetRoleClaims] PRIMARY KEY CLUSTERED ([Id] ASC),

    CONSTRAINT [FK_AspNetRoleClaims_AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE

);

GO

CREATE NONCLUSTERED INDEX [IX_AspNetRoleClaims_RoleId]

    ON [dbo].[AspNetRoleClaims]([RoleId] ASC);

 

 

AspNetRoles

CREATE TABLE [dbo].[AspNetRoles] (

    [Id]               NVARCHAR (450) NOT NULL,

    [Name]             NVARCHAR (256) NULL,

    [NormalizedName]   NVARCHAR (256) NULL,

    [ConcurrencyStamp] NVARCHAR (MAX) NULL,

    CONSTRAINT [PK_AspNetRoles] PRIMARY KEY CLUSTERED ([Id] ASC)

);

GO

CREATE UNIQUE NONCLUSTERED INDEX [RoleNameIndex]

    ON [dbo].[AspNetRoles]([NormalizedName] ASC) WHERE ([NormalizedName] IS NOT NULL);

 

 

 

 

AspNetUserClaims

CREATE TABLE [dbo].[AspNetUserClaims] (

    [Id]         INT            IDENTITY (1, 1) NOT NULL,

    [UserId]     NVARCHAR (450) NOT NULL,

    [ClaimType]  NVARCHAR (MAX) NULL,

    [ClaimValue] NVARCHAR (MAX) NULL,

    CONSTRAINT [PK_AspNetUserClaims] PRIMARY KEY CLUSTERED ([Id] ASC),

    CONSTRAINT [FK_AspNetUserClaims_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE

);

GO

CREATE NONCLUSTERED INDEX [IX_AspNetUserClaims_UserId]

    ON [dbo].[AspNetUserClaims]([UserId] ASC);

 

 

 

AspNetUserTokens

 

 

 

CREATE TABLE [dbo].[AspNetUserTokens] (

    [UserId]        NVARCHAR (450) NOT NULL,

    [LoginProvider] NVARCHAR (128) NOT NULL,

    [Name]          NVARCHAR (128) NOT NULL,

    [Value]         NVARCHAR (MAX) NULL,

    CONSTRAINT [PK_AspNetUserTokens] PRIMARY KEY CLUSTERED ([UserId] ASC, [LoginProvider] ASC, [Name] ASC),

    CONSTRAINT [FK_AspNetUserTokens_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE

);