487093c47f3ecdb53a7d97b4347a2b4da9e421e1.svn-base 1.5 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253
  1. ---------------------------------------------------
  2. -- 권한관리
  3. ---------------------------------------------------
  4. CREATE TABLE CmUserGroup
  5. (
  6. SiteId int NOT NULL , -- 현장 고유번호
  7. UserGroupId int identity(1,1) NOT NULL , -- 사용자 그룹 고유번호
  8. Name nvarchar(32) NOT NULL , -- 사용자 그룹 이름
  9. PRIMARY KEY (SiteId ASC , UserGroupId ASC)
  10. );
  11. DBCC CHECKIDENT( CmUserGroup, RESEED, 1 )
  12. --CREATE TABLE InmePermission
  13. CREATE TABLE CmUserGroupPermission
  14. (
  15. SiteId int NOT NULL , -- 현장 고유번호
  16. UserGroupId int NOT NULL , -- 사용자 그룹 고유번호
  17. MenuId int NOT NULL , -- 메뉴 고유번호
  18. MenuPermission smallint NULL , -- 그룹별 메뉴 사용 권한
  19. PRIMARY KEY (SiteId ASC , UserGroupId ASC , MenuId ASC)
  20. );
  21. ALTER TABLE CmUser
  22. ADD CONSTRAINT FK__CmUser__CmUserGroup
  23. FOREIGN KEY (SiteId , UserGroupId)
  24. REFERENCES CmUserGroup(SiteId , UserGroupId)
  25. ;
  26. ALTER TABLE CmUserGroup
  27. ADD FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
  28. ;
  29. ALTER TABLE CmUserGroupPermission
  30. ADD CONSTRAINT FK__CmUserGroupPermission__CmUserGroup
  31. FOREIGN KEY (SiteId , UserGroupId)
  32. REFERENCES CmUserGroup(SiteId , UserGroupId)
  33. ON DELETE CASCADE
  34. ;
  35. --ALTER TABLE CmUserGroupPermission
  36. -- ADD CONSTRAINT FK__CmUserGroupPermission__CmMenu
  37. -- FOREIGN KEY (MenuId) REFERENCES CmMenu(MenuId)
  38. --;
  39. --ALTER TABLE CmUserToUserGroup
  40. -- ADD FOREIGN KEY (SiteId , UserId) REFERENCES CmUser(SiteId , UserId)
  41. --;