Appendix B. IAM Database Schema

Appendix B. IAM Database Schema
Prev

IAM에서 사용되는 DB Schema 정보이다. 아래의 DDL 문장들은 HSQL DB를 기준으로 작성이 되어있다. 해당 내용을 참고해 실제 프로젝트에서 사용되는 DB에 맞는 DDL 문장으로 수정 해 사용 가능하다.

B.1.User Schema

사용자 정보를 가지는 테이블 이다.

			
CREATE TABLE USERS (
    USER_ID      VARCHAR(20) NOT NULL,
    USER_NAME    VARCHAR(50) NOT NULL,
    PASSWORD     VARCHAR(50) NOT NULL,
    ENABLED      CHAR(1),
    CREATE_DATE  VARCHAR(8),
    MODIFY_DATE  VARCHAR(8),
    CONSTRAINT PK_USERS
    PRIMARY KEY ( USER_ID )
);

B.2.Group Schema

사용자 그룹에 관련된 정보를 가지는 테이블이다.

CREATE TABLE GROUPS (
    GROUP_ID     VARCHAR(20) NOT NULL,
    GROUP_NAME   VARCHAR(50) NOT NULL,
    CREATE_DATE  VARCHAR(8),
    MODIFY_DATE  VARCHAR(8),
    CONSTRAINT PK_GROUPS
    PRIMARY KEY ( GROUP_ID )
); 

CREATE TABLE GROUPS_HIERARCHY ( 
    PARENT_GROUP  VARCHAR(50) NOT NULL,
    CHILD_GROUP   VARCHAR(50) NOT NULL,
    CREATE_DATE   VARCHAR(8),
    MODIFY_DATE   VARCHAR(8),
    CONSTRAINT PK_GROUPS_HIERARCHY PRIMARY KEY ( PARENT_GROUP, CHILD_GROUP ),
    CONSTRAINT FK_GROUPS1 FOREIGN KEY (PARENT_GROUP) REFERENCES GROUPS (GROUP_ID),
    CONSTRAINT FK_GROUPS2 FOREIGN KEY (CHILD_GROUP) REFERENCES GROUPS (GROUP_ID)
);

CREATE TABLE GROUPS_USERS (
    GROUP_ID     VARCHAR(20) NOT NULL,
    USER_ID      VARCHAR(20) NOT NULL,
    CREATE_DATE  VARCHAR(8),
    MODIFY_DATE  VARCHAR(8),
    CONSTRAINT PK_GROUPS_USERS PRIMARY KEY ( GROUP_ID, USER_ID ),
    CONSTRAINT FK_GROUPS_USERS_GROUP_ID FOREIGN KEY (GROUP_ID) REFERENCES GROUPS (GROUP_ID),
    CONSTRAINT FK_GROUPS_USERS_USER_ID FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID)
);

B.3.Role Schema

Role에 관련된 정보를 가지는 테이블이다.

CREATE TABLE ROLES (
    ROLE_ID      VARCHAR(50)  NOT NULL,
    ROLE_NAME    VARCHAR(50),
    DESCRIPTION  VARCHAR(100),
    CREATE_DATE  VARCHAR(8),
    MODIFY_DATE  VARCHAR(8),
    CONSTRAINT PK_ROLES PRIMARY KEY ( ROLE_ID )
);

CREATE TABLE ROLES_HIERARCHY (
    PARENT_ROLE  VARCHAR(50) NOT NULL,
    CHILD_ROLE   VARCHAR(50) NOT NULL,
    CREATE_DATE  VARCHAR(8),
    MODIFY_DATE  VARCHAR(8),
    CONSTRAINT PK_ROLES_HIERARCHY PRIMARY KEY ( PARENT_ROLE, CHILD_ROLE ),
    CONSTRAINT FK_ROLES1 FOREIGN KEY(PARENT_ROLE) REFERENCES ROLES(ROLE_ID),
    CONSTRAINT FK_ROLES2 FOREIGN KEY(CHILD_ROLE) REFERENCES ROLES (ROLE_ID)
);

B.4.Secured Resource Schema

보호대상자원에 관련된 정보를 가지는 테이블이다.

	
CREATE TABLE SECURED_RESOURCES ( 
    RESOURCE_ID       VARCHAR(10) NOT NULL,
    RESOURCE_NAME     VARCHAR(50), 
    RESOURCE_PATTERN  VARCHAR(300) NOT NULL,
    DESCRIPTION       VARCHAR(100),
    RESOURCE_TYPE     VARCHAR(10) NOT NULL,
    SORT_ORDER        NUMERIC,
    CREATE_DATE       VARCHAR(8),
    MODIFY_DATE       VARCHAR(8),
    CONSTRAINT PK_RECURED_RESOURCES
    PRIMARY KEY ( RESOURCE_ID )
); 

CREATE TABLE SECURED_RESOURCES_ROLES ( 
    RESOURCE_ID  VARCHAR(10) NOT NULL, 
    ROLE_ID      VARCHAR(50) NOT NULL, 
    CREATE_DATE  VARCHAR(8), 
    MODIFY_DATE  VARCHAR(8), 
    CONSTRAINT PK_SECURED_RESOURCES_ROLES PRIMARY KEY ( RESOURCE_ID, ROLE_ID ),
    CONSTRAINT FK_SECURED_RESOURCES FOREIGN KEY(RESOURCE_ID) REFERENCES SECURED_RESOURCES(RESOURCE_ID),
    CONSTRAINT FK_ROLES4 FOREIGN KEY (ROLE_ID) REFERENCES ROLES(ROLE_ID)
);

CREATE TABLE CANDIDATE_SECURED_RESOURCES (
    CANDIDATE_RESOURCE_ID    NUMERIC(10) NOT NULL,
    BEANID                   VARCHAR(128),
    PACKAGES                 VARCHAR(128) NOT NULL,
    CLASSES                  VARCHAR(128),
    METHOD                   VARCHAR(128),
    PARAMETER                VARCHAR(128),
    REQUESTMAPPING           VARCHAR(128),
    POINTCUT                 VARCHAR(128),
    CANDIDATE_RESOURCE_TYPE  VARCHAR(10) NOT NULL,
    CONSTRAINT	CANDIDATE_SECURED_RESOURCES_PK	PRIMARY KEY(CANDIDATE_RESOURCE_ID)
);

B.5.View Resource Schema

화면제어에 관련된 정보를 가지는 테이블이다.

CREATE TABLE VIEW_RESOURCES (
    VIEW_RESOURCE_ID  VARCHAR(50) NOT NULL,   -- ex.) LOGI_001
    CATEGORY          VARCHAR(255),           -- ex.) 물류 > 입고관리
    VIEW_NAME         VARCHAR(50) NOT NULL,   -- ex.) 미입고현황
    DESCRIPTION       VARCHAR(255) NOT NULL,  -- ex.) 입고되지 않은 주문 리스트
    VIEW_INFO         VARCHAR(255),           -- ex.) 화면에 대한 추가 정보
    CONSTRAINT PK_VIEW_RESOURCES  PRIMARY KEY(VIEW_RESOURCE_ID)
);

CREATE TABLE VIEW_RESOURCES_MAPPING  (
    VIEW_RESOURCE_ID  VARCHAR(50) NOT NULL,
    REF_ID            VARCHAR(50) NOT NULL,   -- ex.) ROLE_ID or USER_ID
    MASK              INTEGER NOT NULL,       -- ex.) 1(R), 2(W), 4(C), 8(D), 16(A)
    PERMISSIONS       VARCHAR(255) NOT NULL,  -- ex.) READ, WRITE, CREATE, DELETE, ADMINISTRATION
    REF_TYPE          VARCHAR(10) NOT NULL,   -- ex.) ROLE or USER [todo:// or GROUP]
    CONSTRAINT PK_VIEW_RESOURCES_MAPPING  PRIMARY KEY(VIEW_RESOURCE_ID, REF_ID),
    CONSTRAINT FK_MAPPING_VIEW_RESOURCE_ID  FOREIGN KEY (VIEW_RESOURCE_ID) REFERENCES VIEW_RESOURCES (VIEW_RESOURCE_ID)
);

B.6.Restricted Times Schema

시간에 의한 제어와 관련된 정보를 가지는 테이블이다.

CREATE TABLE RESTRICTED_TIMES (
    TIME_ID     VARCHAR(10) NOT NULL,
    TIME_TYPE   VARCHAR(10) NOT NULL,  -- crash, holiday, weekend, improve, daily
    START_DATE  VARCHAR(8),
    START_TIME  VARCHAR(6) NOT NULL,
    END_DATE    VARCHAR(8),
    END_TIME    VARCHAR(6) NOT NULL,
    DESCRIPTION	VARCHAR(100),
    CONSTRAINT PK_RESTRICTED_TIMES PRIMARY KEY(TIME_ID)
);

CREATE TABLE RESTRICTED_TIMES_RESOURCES (
    TIME_ID      VARCHAR(10) NOT NULL,
    RESOURCE_ID  VARCHAR(10) NOT NULL,
    CONSTRAINT PK_RESTRICTED_TIMES_RESOURCES  PRIMARY KEY(TIME_ID, RESOURCE_ID),
    CONSTRAINT FK_TIMES_RESOURCES_TIME_ID FOREIGN KEY (TIME_ID) REFERENCES RESTRICTED_TIMES(TIME_ID),
    CONSTRAINT FK_TIMES_RESOURCES_RESOURCE_ID FOREIGN KEY(RESOURCE_ID) REFERENCES SECURED_RESOURCES(RESOURCE_ID)
);

CREATE TABLE TIMES_RESOURCES_EXCLUSION (
    TIME_ID      VARCHAR(10) NOT NULL,
    RESOURCE_ID  VARCHAR(10) NOT NULL,
    ROLE_ID      VARCHAR(50) NOT NULL,
    CONSTRAINT PK_EXCLUSION  PRIMARY KEY(TIME_ID, RESOURCE_ID, ROLE_ID),
    CONSTRAINT FK_EXCLUSION_TIME_ID FOREIGN KEY (TIME_ID) REFERENCES RESTRICTED_TIMES(TIME_ID),
    CONSTRAINT FK_EXCLUSION_RESOURCE_ID FOREIGN KEY(RESOURCE_ID) REFERENCES SECURED_RESOURCES(RESOURCE_ID),
    CONSTRAINT FK_EXCLUSION_ROLE_ID FOREIGN KEY(ROLE_ID) REFERENCES ROLES(ROLE_ID)
);

CREATE TABLE RESTRICTED_TIMES_ROLES (
    TIME_ID  VARCHAR(10) NOT NULL,
    ROLE_ID  VARCHAR(50) NOT NULL,
    CONSTRAINT PK_RESTRICTED_TIMES_ROLES  PRIMARY KEY(TIME_ID, ROLE_ID),
    CONSTRAINT FK_TIMES_ROLES_TIME_ID FOREIGN KEY (TIME_ID) REFERENCES RESTRICTED_TIMES(TIME_ID),
    CONSTRAINT FK_TIMES_ROLES_ROLE_ID FOREIGN KEY(ROLE_ID) REFERENCES ROLES(ROLE_ID)
);

B.7.IDS Schema

ID 자동 생성과 관련된 테이블이다.

CREATE TABLE IDS ( 
    TABLE_NAME  VARCHAR(16) NOT NULL, 
    NEXT_ID     NUMERIC(30) NOT NULL, 
    PRIMARY KEY ( TABLE_NAME )
);

Prev
Appendix A. Authentication / Authorization	Home