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