Login Database Design

Login Storage

SecurityUser Table
Column Name DataType Nullable Notes
SUS_SecurityLoginID INT no IDENTITY, Primary Key
SUS_WindowsUser BIT no Defines if the login is attached to a Windows User
SUS_WindowsSID NVARCHAR(100) Yes only populated for Windows Users
SUS_LoginID NVARCHAR(100) no unique
SUS_CreatedDate DATETIME no Default GETUTCDATE
SUS_ContactID INT yes Foreign Key to Contact Person
SUS_LastLogin DATETIME yes Updated when used logs in, always in UTC Time
SUS_LastActivity DATETIME yes Updated when the user performs activity always in UTC time


SecurityLoginDetails Table
Column Name DataType Nullable Notes
SLD_SecurityLoginID INT no Primary Key, Foreign Key to SecurityLogin Table
SLD_LoginEnabled BIT no DEFAULT 1
SLD_LastPasswordChange DATETIME yes Only used for non-Windows User
SLD_PasswordHash NVARCHAR(256) yes Only used for non-Windows User
SLD_LockedOut bit no Default 0, Only used for non-Windows User
SLD_LastBadPasswordAttempt DATETIME yes
SLD_FailedLoginAttempts INT yes Default 0


SecurityWindowsGroup Table
Column Name DataType Nullable Notes
SWG_SecurityWindowsGroupID INT no IDENTITY, Primary Key
SWG_WindowsGroupSID NVARCHAR(100) no UNIQUE
SWG_WindowsGroupName NVARCHAR(256) yes Not sure that we should have this, probably can just pull the name from the Domain
SWG_DeniedAccess BIT no Default to 0, if set the specified group is explictly denied access to the system.


SecurityWindowsUser Table
Column Name DataType Nullable Notes
SWU_SecurityWindowsUserID INT no IDENTITY, Primary Key
SWu_WindowsUserSID NVARCHAR(100) no UNIQUE
SWU_WindowsUserName NVARCHAR(256) yes Not sure that we should have this, probably can just pull the name from the Domain
SWU_DeniedAccess BIT no Default to 0, if set the specified group is explictly denied access to the system.


SecurityWindowsGroup_Role_Xref Table
Column Name DataType Nullable Notes
SRX_SecurityWindowsGroup_Role_Xref_ID INT no IDENTITY, Primary Key
SRX_WindowsGroupID INT no Foreign Key SecurityWindowsGroup
SRX_SecurityRoleID INT no Foreign Key SecurityRole
SRX_HierarchyID INT yes
SRX_IncludeChildren BIT yes Check constraint can only be Null if SRX_HierarchyID is NULL


SecurityWindowsUser_Role_Xref Table
Column Name DataType Nullable Notes
SRX_SecurityWindowsUser_Role_Xref_ID INT no IDENTITY, Primary Key
SRX_WindowsUserID INT no Foreign Key SecurityWindowsGroup
SRX_SecurityRoleID INT no Foreign Key SecurityRole
SRX_HierarchyID INT yes
SRX_IncludeChildren BIT yes Check constraint can only be Null if SRX_HierarchyID is NULL

Logins' Security Database Code

Security_WindowsValidation - Validates Windows user has access to the system with the inputs of the Windows User SID and the SIDs of Groups user belongs to. Return value differs all user data doesn't exist and user has User-CreateSelf privaliges.

GetWindowsSecurityAccess - Table Value function that Lists all distinct/Hiearchy specific Access belonging to a Window User,
Parameters are UserSID and the SIDs of groups the User belongs to.

Security Login Audit

Last edited Feb 28, 2011 at 10:40 PM by mbulava, version 16

Comments

No comments yet.