Roles Database Design
Roles Storage
SecurityRole Table Column Name | DataType | Nullable | Notes |
SRO_SecurityRoleID | INT | no | IDENTITY, PRIMARY KEY |
SRO_BaseSecurityRoleID | INT | yes | Foreign Key to SRO_SecurityRoleID |
SRO_Name | NVARCHAR(100) | no | Unique |
SRO_LastModified | DATETIME | no | DEAFULT(GETUTCDATE()) |
SRO_LastModifiedBy | NVARCHAR(100) | no | Usercode of the Last Modifed user |
SecurityRoleAccess Table Column Name | DataType | Nullable | Notes |
SRA_SecurityRoleAccessID | INT | no | IDENTITY, PRIMARY KEY |
SRA_SecurityRoleID | INT | no | Foreign Key to SecurityRole.SRO_SecurityRoleID |
SRA_SecurityAccessID | INT | no | Foreign Key to SecurityAccess |
Notes: Unique Index on SRA
SecurityRoleID, SRAAccessIdentifier
Audit_Role Table Column Name | DataType | Nullable | Notes |
ARO_RoleAuditID | INT | no | IDENTITY |
ARO_ModificationDate | DATETIME | no | DEFAULT(GETUTCDATE()), Partition Key* |
ARO_SecurtiyRoleID | INT | no | Soft Foreign key to SecurityRole |
ARO_ModifedByUser | NVARCHAR(100) | no | |
ARO_RoleName | NVARCHAR(100) | no | |
ARO_Modifications | NVARCHAR(MAX) | no | Text of all modification permissions removed or added back from Base Role |
*Partitioned on Enterprise editions of SQL only.