Hinzufügen eines Microsoft Identity Frameworks zu einer
bestehenden Datenbank
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:
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
);
|