1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253 |
- ---------------------------------------------------
- -- 권한관리
- ---------------------------------------------------
- CREATE TABLE CmUserGroup
- (
- SiteId int NOT NULL , -- 현장 고유번호
- UserGroupId int identity(1,1) NOT NULL , -- 사용자 그룹 고유번호
- Name nvarchar(32) NOT NULL , -- 사용자 그룹 이름
- PRIMARY KEY (SiteId ASC , UserGroupId ASC)
- );
- DBCC CHECKIDENT( CmUserGroup, RESEED, 1 )
- --CREATE TABLE InmePermission
- CREATE TABLE CmUserGroupPermission
- (
- SiteId int NOT NULL , -- 현장 고유번호
- UserGroupId int NOT NULL , -- 사용자 그룹 고유번호
- MenuId int NOT NULL , -- 메뉴 고유번호
- MenuPermission smallint NULL , -- 그룹별 메뉴 사용 권한
- PRIMARY KEY (SiteId ASC , UserGroupId ASC , MenuId ASC)
- );
- ALTER TABLE CmUser
- ADD CONSTRAINT FK__CmUser__CmUserGroup
- FOREIGN KEY (SiteId , UserGroupId)
- REFERENCES CmUserGroup(SiteId , UserGroupId)
- ;
- ALTER TABLE CmUserGroup
- ADD FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE CmUserGroupPermission
- ADD CONSTRAINT FK__CmUserGroupPermission__CmUserGroup
- FOREIGN KEY (SiteId , UserGroupId)
- REFERENCES CmUserGroup(SiteId , UserGroupId)
- ON DELETE CASCADE
- ;
- --ALTER TABLE CmUserGroupPermission
- -- ADD CONSTRAINT FK__CmUserGroupPermission__CmMenu
- -- FOREIGN KEY (MenuId) REFERENCES CmMenu(MenuId)
- --;
- --ALTER TABLE CmUserToUserGroup
- -- ADD FOREIGN KEY (SiteId , UserId) REFERENCES CmUser(SiteId , UserId)
- --;
|