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 SRASecurityRoleID, 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.



Last edited Feb 3, 2011 at 3:42 PM by mbulava, version 17

Comments

No comments yet.