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 (👁4719)


 

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

);