-
Dec 20th, 2018, 05:00 AM
#1
User Management Database Design
Hi all,
Currently we have three tables that manages users. The schema below fits for a dozen of users. Now that the user list is expanding to almost a hundred and still growing, the schema below needs improvement.
[dbo].[Users] – the list of users in the system
[dbo].[Roles] – list of roles/permissions/resources in the system (Employee Dashboard or Employee Entry or Employee List, etc.)
[dbo].[UserRoles] – assignment of a Role to a User
We are on the process of brainstorming on applying grouping logic. Any ideas or suggestions on improving the schema above would be helpful.
Thanks.
- kgc
-
Dec 20th, 2018, 06:28 AM
#2
Re: User Management Database Design
I'm in no way a professional, but i would know of two ways.
1) As described above with an m:n-Intermix-Table. The Table "UserRoles" consisting just (or at least) of Foreign Keys to Tables "Users" and "Roles", additional columns not withstanding
2) Without an Intermix-Table (in your case "UserRoles"). In that case every Entry in "Roles" getting a unique numeric value, with an additional numeric column in "Users" named "Rights" (or whatever).
The Idea being that the Field "Rights" in Table "Users" is just a Bitmask of the Values in "Roles"
Kind of like (in "Roles") --> Note the "binary" notation of values
Add Employee - Value 1
Edit Employee - Value 2
Delete Employee - Value 4
etc.
in Table "Users" the Field "Rights" would be like:
User Zvoni - Rights=5 --> Meaning, i would have the rights to add and delete an Employee, but not edit him/her
But as i said: I'm no professional, so i've no idea if my model is sound
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Dec 20th, 2018, 07:23 AM
#3
Re: User Management Database Design
Originally Posted by KGComputers
the schema below needs improvement.
Why? What are you try to achieve that that schema can't do for you? That schema can handle as many users as you like.
-
Dec 20th, 2018, 08:37 AM
#4
Re: User Management Database Design
@jmc.
Scenario.
1. There are 10 or more modules in a current system/software.
2. Each module can have 10 or more roles.
3. Each user has access to each module and could have one or more or all
roles for a specific module.
The issue is that each user needs to have their permissions explicitly assigned.
-
Dec 20th, 2018, 08:57 AM
#5
Re: User Management Database Design
Right, OK.. still fail to see the problem.
User table:
ID - 1 UserName - TGnome
ID - 2 UserName - KGComputers
ID - 3 UserName - BillyBob
Role table:
ID - 1 Role - Admin
ID - 2 Role - Shipping
ID - 3 Role - Receiving
ID - 4 Role - Accounting
ID - 5 Role - Sales
ID - 6 Role - Janitorial
UseRole
UserID - 1 RoleId - 1
UserID - 1 RoleId - 4
UserID - 1 RoleId - 5
UserID - 3 RoleId - 6
UserId - 2 RoleId - 3
UserId - 2 RoleId - 5
Multiple users... with one or more roles... it's a common setup.. Not sure what else you really need.
-tg
-
Dec 20th, 2018, 09:17 AM
#6
Re: User Management Database Design
Originally Posted by KGComputers
@jmc.
Scenario.
1. There are 10 or more modules in a current system/software.
2. Each module can have 10 or more roles.
3. Each user has access to each module and could have one or more or all
roles for a specific module.
The issue is that each user needs to have their permissions explicitly assigned.
So add a Module table and a ModuleId foreign key in the Role table. The number of users isn't really relevant. Your issue seems to be more how to structure a UI to set/edit these permissions than how to store them.
-
Dec 20th, 2018, 09:37 AM
#7
Re: User Management Database Design
Ah... I must have issed that... I read it a little too fast...
I'd keep the original three tables and structure and add two new tables:
table Module:
ID - Name
table ModuleRole
Id - ModuleId - RoleId
then when you add a module to a user, it adds the roles to the user ... this allows you to add roles by module to a user, but then fine-tune the roles for a given user w/o affecting other users or having to create alternate one-off modules for individual users....
-tg
-
Dec 20th, 2018, 01:22 PM
#8
Re: User Management Database Design
Not sure how far you want to go in assigning access rights, but an idea to look at would be rights per function:
Read Only (On/Off,Yes/No) (can view data, but not make changes)
Update (On/Off,Yes/No) (Add/Change capability)
Delete (On/Off,Yes/No) (Can Delete records)
Sub-section of rights for various report types by user (users can run some reports, but not others).
Also, you might want to consider User Groups. You can set up User Groups with a set of rights, then just assign a user to that group. That makes it a lot easier to set up rights for a new user that fits into a specific group.
Just some ideas to think about, without knowing more about the exact roles for each user.
-
Dec 20th, 2018, 01:45 PM
#9
Re: User Management Database Design
I've got that exact same 3 user, role and userrole tables.
I created a role group table that looks like this
Code:
use stufiles
go
drop table RoleGroups_T
go
create table RoleGroups_T
(
RoleId int
,SubRoleId int
,primary key clustered (RoleId,SubRoleId)
)
go
insert into RoleGroups_T select 90000, RoleId from Role_T where RoleId between 11000 and 11018
insert into RoleGroups_T select 90001, RoleId from Role_T where RoleId between 11100 and 11114
--insert into Role_T values(90001,'YEAR_ALL','Year All','')
--insert into Role_T values(90000,'BLDG_ALL','Buildings All','')
more in next post....
-
Dec 20th, 2018, 01:46 PM
#10
Re: User Management Database Design
Then modify the query that pulls roles to UNION this table - care taken to exclude GROUP ROLES if the user has that specific role already (in like a DENIED state).
-
Dec 20th, 2018, 01:49 PM
#11
Re: User Management Database Design
Originally Posted by szlamany
Then modify the query that pulls roles to UNION this table - care taken to exclude GROUP ROLES if the user has that specific role already (in like a DENIED state).
-
Dec 20th, 2018, 01:50 PM
#12
Re: User Management Database Design
Forums s#$%ks when posting code!!!!!!!!!!!!!!
Argh!!!
-
Dec 20th, 2018, 05:35 PM
#13
Re: User Management Database Design
In earlier asp.net versions (classic asp.net NOT MVC) there was a user rights database that was ready to go.
I can't recall now how it was set up but I was using it for a few years as I was keeping a User Management as part of on website I was developing and it was quite to the point. I don't know what happened to the database in later asp.net versions but it was very useful because you had a ready to go user management. Of course the downside was some "insane" SQL code(that in the end I became quite expert on managing it and remember giving advice to the forum here a few times) for managing the users but you can just look at the schema to get you going.
In fact....... Found my old app. Here is the schema diagram: I don't know how it will show in the forum but...
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 20th, 2018, 08:03 PM
#14
Re: User Management Database Design
Originally Posted by sapator
In earlier asp.net versions (classic asp.net NOT MVC) there was a user rights database that was ready to go.
I can't recall now how it was set up but I was using it for a few years as I was keeping a User Management as part of on website I was developing and it was quite to the point. I don't know what happened to the database in later asp.net versions but it was very useful because you had a ready to go user management. Of course the downside was some "insane" SQL code(that in the end I became quite expert on managing it and remember giving advice to the forum here a few times) for managing the users but you can just look at the schema to get you going.
In fact....... Found my old app. Here is the schema diagram: I don't know how it will show in the forum but...
That has been superseded by Identity, which also incorporates third-party membership providers, e.g. Facebook and Twitter, as well as two-factor authentication. I've used it a couple of projects already and am currently using it with two-factor authentication for the first time right now.
-
Dec 20th, 2018, 08:10 PM
#15
Re: User Management Database Design
Originally Posted by szlamany
Forums s#$%ks when posting code!!!!!!!!!!!!!!
Argh!!!
Not really. Here's some SQL:
sql Code:
IF EXISTS
(
SELECT *
FROM information_schema.TABLES
WHERE TABLE_NAME = 'Species'
AND TABLE_SCHEMA = 'dbo'
)
BEGIN
DECLARE @sql NVARCHAR(MAX)
DECLARE @schema NVARCHAR(3) = 'dbo'
SET @sql = N''
SELECT @sql = @sql + N'
ALTER TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N' DROP CONSTRAINT ' + QUOTENAME(c.name)
FROM sys.objects AS c INNER JOIN sys.tables AS t
ON c.parent_object_id = t.[object_id] INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE c.[type] IN ('D', 'C', 'F', 'PK', 'UQ')
AND s.[name] = @schema
ORDER BY c.[type]
SELECT @sql = @sql + N'
DROP VIEW ' + QUOTENAME(s.name) + N'.' + QUOTENAME(v.name)
FROM sys.views AS v INNER JOIN sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
WHERE s.[name] = @schema
EXEC sys.sp_executesql @sql
-- drop all tables
EXEC sp_MSForEachTable 'DROP TABLE ?'
END
GO
and here's some VB:
vb.net Code:
Private Sub startAllButton_Click(sender As Object, e As EventArgs) Handles startAllButton.Click
If Me.worker.IsBusy() Then
'Only run one test at a time.
MessageBox.Show("Cannot start new tasks. Please wait for all current tasks to complete.",
"Start Tasks Failed",
MessageBoxButtons.OK,
MessageBoxIcon.Error)
Else
'A new test cannot be started while this test is running.
Me.startAllButton.Enabled = False
'The running test can be cancelled.
Me.cancelAllButton.Enabled = True
'Clear all previous data.
Me.runningTasksGrid.Rows.Clear()
Me.completedTasksGrid.Rows.Clear()
'Start the specified number of background tasks.
For i = 1 To CInt(Me.taskCountSpinner.Value)
'Generate a random token to identify each task.
Me.worker.RunWorkerAsync(Guid.NewGuid())
Next
End If
End Sub
Just use HIGHLIGHT tags with the appropriate option.
-
Dec 21st, 2018, 03:41 AM
#16
Re: User Management Database Design
No problem.
If you prefer that, here is the schema:
Code:
IF OBJECT_ID('AspNetUserRoles', 'U') IS NOT NULL
BEGIN
DROP TABLE AspNetUserRoles;
END
IF OBJECT_ID('AspNetUserClaims', 'U') IS NOT NULL
BEGIN
DROP TABLE AspNetUserClaims;
END
IF OBJECT_ID('AspNetUserLogins', 'U') IS NOT NULL
BEGIN
DROP TABLE AspNetUserLogins;
END
IF OBJECT_ID('AspNetRoles', 'U') IS NOT NULL
BEGIN
DROP TABLE AspNetRoles;
END
IF OBJECT_ID('AspNetUsers', 'U') IS NOT NULL
BEGIN
DROP TABLE AspNetUsers;
END
CREATE TABLE [dbo].[AspNetUsers] (
[Id] NVARCHAR (128) NOT NULL,
[UserName] NVARCHAR (MAX) NULL,
[PasswordHash] NVARCHAR (MAX) NULL,
[SecurityStamp] NVARCHAR (MAX) NULL,
[Discriminator] NVARCHAR (128) NOT NULL,
[ApplicationId] UNIQUEIDENTIFIER NOT NULL,
[LegacyPasswordHash] NVARCHAR (MAX) NULL,
[IsAnonymous] BIT DEFAULT ((0)) NOT NULL,
[LastActivityDate] DATETIME2 NOT NULL,
[Email] NVARCHAR (256) NULL,
[PasswordQuestion] NVARCHAR (256) NULL,
[PasswordAnswer] NVARCHAR (128) NULL,
[IsApproved] BIT NOT NULL,
[IsLockedOut] BIT NOT NULL,
[CreateDate] DATETIME2 NOT NULL,
[LastLoginDate] DATETIME2 NOT NULL,
[LastPasswordChangedDate] DATETIME2 NOT NULL,
[LastLockoutDate] DATETIME2 NOT NULL,
[FailedPasswordAttemptCount] INT NOT NULL,
[FailedPasswordAttemptWindowStart] DATETIME2 NOT NULL,
[FailedPasswordAnswerAttemptCount] INT NOT NULL,
[FailedPasswordAnswerAttemptWindowStart] DATETIME2 NOT NULL,
[Comment] NTEXT NULL,
CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC),
FOREIGN KEY ([ApplicationId]) REFERENCES [dbo].[Applications] ([ApplicationId]),
);
INSERT INTO AspNetUsers(Id,UserName,PasswordHash,Discriminator,SecurityStamp,
ApplicationId,IsAnonymous,LastActivityDate,LegacyPasswordHash,
Email,PasswordQuestion,PasswordAnswer,IsApproved,IsLockedOut,CreateDate,
LastLoginDate,LastPasswordChangedDate,LastLockoutDate,FailedPasswordAttemptCount,
FailedPasswordAnswerAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAttemptWindowStart,Comment)
SELECT Users.UserId,Users.UserName,(Memberships.Password+'|'+CAST(Memberships.PasswordFormat as
varchar)+'|'+Memberships.PasswordSalt),'User',NewID(),Users.ApplicationId,
Users.IsAnonymous,Users.LastActivityDate,Memberships.Password,
Memberships.Email,Memberships.PasswordQuestion,Memberships.PasswordAnswer,
Memberships.IsApproved,Memberships.IsLockedOut,Memberships.CreateDate,Memberships.LastLoginDate,Memberships.LastPasswordCha
ngedDate,
Memberships.LastLockoutDate,Memberships.FailedPasswordAttemptCount, Memberships.FailedPasswordAnswerAttemptWindowsStart,
Memberships.FailedPasswordAnswerAttemptCount,Memberships.FailedPasswordAttemptWindowStart,Memberships.Comment
FROM Users
LEFT OUTER JOIN Memberships ON Memberships.ApplicationId = Users.ApplicationId
AND Users.UserId = Memberships.UserId;
CREATE TABLE [dbo].[AspNetRoles] (
[Id] NVARCHAR (128) NOT NULL,
[Name] NVARCHAR (MAX) NOT NULL,
PRIMARY KEY NONCLUSTERED ([Id] ASC),
);
INSERT INTO AspNetRoles(Id,Name)
SELECT RoleId,RoleName
FROM Roles;
CREATE TABLE [dbo].[AspNetUserRoles] (
[UserId] NVARCHAR (128) NOT NULL,
[RoleId] NVARCHAR (128) NOT NULL,
CONSTRAINT [PK_dbo.AspNetUserRoles] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC),
CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id])
ON DELETE CASCADE,
CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
);
INSERT INTO AspNetUserRoles(UserId,RoleId)
SELECT UserId,RoleId
FROM UsersInRoles;
CREATE TABLE [dbo].[AspNetUserClaims] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[ClaimType] NVARCHAR (MAX) NULL,
[ClaimValue] NVARCHAR (MAX) NULL,
[User_Id] NVARCHAR (128) NOT NULL,
CONSTRAINT [PK_dbo.AspNetUserClaims] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_User_Id] FOREIGN KEY ([User_Id]) REFERENCES [dbo].[AspNetUsers]
([Id]) ON DELETE CASCADE
);
GO
CREATE NONCLUSTERED INDEX [IX_User_Id]
ON [dbo].[AspNetUserClaims]([User_Id] ASC);
CREATE TABLE [dbo].[AspNetUserLogins] (
[UserId] NVARCHAR (128) NOT NULL,
[LoginProvider] NVARCHAR (128) NOT NULL,
[ProviderKey] NVARCHAR (128) NOT NULL,
CONSTRAINT [PK_dbo.AspNetUserLogins] PRIMARY KEY CLUSTERED ([UserId] ASC, [LoginProvider] ASC, [ProviderKey] ASC),
CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers]
([Id]) ON DELETE CASCADE
);
GO
CREATE NONCLUSTERED INDEX [IX_UserId]
ON [dbo].[AspNetUserLogins]([UserId] ASC);
ALTER TABLE AspNetUsers
ADD [Profile_DateOfBirth] DateTime NULL,
[Profile_City] NVARCHAR (MAX) NULL,
[Profile_UserStats_Height] INT NULL,
[Profile_UserStats_Weight] INT NULL;
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 23rd, 2018, 10:56 AM
#17
Re: User Management Database Design
The number of users isn't really relevant. Your issue seems to be more how to structure a UI to set/edit these permissions than how to store them.
@jmc yes - that's one of the concerns we have aside from the DB. That's one of the priorities next year.
In earlier asp.net versions (classic asp.net NOT MVC) there was a user rights database that was ready to go.
I can't recall now how it was set up but I was using it for a few years as I was keeping a User Management as part of on website I was developing and it was quite to the point. I don't know what happened to the database in later asp.net versions but it was very useful because you had a ready to go user management. Of course the downside was some "insane" SQL code(that in the end I became quite expert on managing it and remember giving advice to the forum here a few times) for managing the users but you can just look at the schema to get you going.
In fact....... Found my old app. Here is the schema diagram: I don't know how it will show in the forum but...
@sapator - yes we have an existing project using the asp.net identity. but this does not fit our client's needs.
Also, you might want to consider User Groups. You can set up User Groups with a set of rights, then just assign a user to that group. That makes it a lot easier to set up rights for a new user that fits into a specific group.
Just some ideas to think about, without knowing more about the exact roles for each user.
@jdc - this is one of the ideas brought up during our brain storm sessions with the team. Thanks for bringing this up.
I've got that exact same 3 user, role and userrole tables.
I created a role group table that looks like this
Code:
use stufiles
go
drop table RoleGroups_T
go
create table RoleGroups_T
(
RoleId int
,SubRoleId int
,primary key clustered (RoleId,SubRoleId)
)
go
insert into RoleGroups_T select 90000, RoleId from Role_T where RoleId between 11000 and 11018
insert into RoleGroups_T select 90001, RoleId from Role_T where RoleId between 11100 and 11114
--insert into Role_T values(90001,'YEAR_ALL','Year All','')
--insert into Role_T values(90000,'BLDG_ALL','Buildings All','')
more in next post....
@szlamany - i'll include this idea on our meeting next year.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|