postgre ddl

테스트용 ddl 

java

닫기 줄바꿈

select *
​​from TEST_TEAM;
​
/* 1. 팀정보 시퀀스 생성 */
DROP SEQUENCE SQ_N_TEST_TEAM_01;
CREATE SEQUENCE SQ_N_TEST_TEAM_01 START 1;

​/* 1.테스트 테이블 팀정보 */
CREATE TABLE TEST_TEAM (
‌TEAM_SN 	NUMERIC(12) NOT NULL,
‌TEAM_NM 	VARCHAR(60) NOT NULL,
‌TEAM_DC 	VARCHAR(200),
‌SORT    	NUMERIC(4),
‌DEL_YN      CHAR(1)         NOT NULL,
​​​​REGISTER_ID VARCHAR(20)     NOT NULL,
​​​​REG_DT      TIMESTAMP       NOT NULL,
​​​​UPDUSR_ID   VARCHAR(20)     NOT NULL,
​​​​MDFCN_DT    TIMESTAMP       NOT NULL,
‌CONSTRAINT  TEST_TEAM_PK PRIMARY KEY (TEAM_SN)
);

COMMENT ON TABLE TEST_TEAM IS '팀정보';
COMMENT ON COLUMN TEST_TEAM.TEAM_SN IS '팀일련번호';
COMMENT ON COLUMN TEST_TEAM.TEAM_NM IS '팀명';
COMMENT ON COLUMN TEST_TEAM.TEAM_DC IS '팀설명';
COMMENT ON COLUMN TEST_TEAM.SORT IS '팀순서'; 
COMMENT ON COLUMN TEST_TEAM.DEL_YN IS '삭제여부';
COMMENT ON COLUMN TEST_TEAM.REGISTER_ID IS '등록자ID';
COMMENT ON COLUMN TEST_TEAM.REG_DT IS '등록일시';
COMMENT ON COLUMN TEST_TEAM.UPDUSR_ID IS '수정자ID';
COMMENT ON COLUMN TEST_TEAM.MDFCN_DT IS '수정일시';

/* 팀테스트 데이터 등록 */
INSERT INTO TEST_TEAM (TEAM_SN,TEAM_NM,TEAM_DC,SORT,DEL_YN,REGISTER_ID,REG_DT,UPDUSR_ID,MDFCN_DT) 
‌‌‌​​​VALUES (nextval('SQ_N_TEST_TEAM_01'),'청팀','청색옷을 입은 청색팀',1,'N','ADMIN',NOW(),'ADMIN',NOW());
INSERT INTO TEST_TEAM (TEAM_SN,TEAM_NM,TEAM_DC,SORT,DEL_YN,REGISTER_ID,REG_DT,UPDUSR_ID,MDFCN_DT) 
‌‌‌​​​VALUES (nextval('SQ_N_TEST_TEAM_01'),'홍팀','홍색옷을 입은 홍색팀',2,'N','ADMIN',NOW(),'ADMIN',NOW());
INSERT INTO TEST_TEAM (TEAM_SN,TEAM_NM,TEAM_DC,SORT,DEL_YN,REGISTER_ID,REG_DT,UPDUSR_ID,MDFCN_DT) 
‌‌‌​​​VALUES (nextval('SQ_N_TEST_TEAM_01'),'백팀','백색옷을 입은 백색팀',3,'N','ADMIN',NOW(),'ADMIN',NOW());
INSERT INTO TEST_TEAM (TEAM_SN,TEAM_NM,TEAM_DC,SORT,DEL_YN,REGISTER_ID,REG_DT,UPDUSR_ID,MDFCN_DT) 
‌‌‌​​​VALUES (nextval('SQ_N_TEST_TEAM_01'),'흑팀','흑색옷을 입은 흑색팀',4,'N','ADMIN',NOW(),'ADMIN',NOW());
INSERT INTO TEST_TEAM (TEAM_SN,TEAM_NM,TEAM_DC,SORT,DEL_YN,REGISTER_ID,REG_DT,UPDUSR_ID,MDFCN_DT) 
‌‌‌​​​VALUES (nextval('SQ_N_TEST_TEAM_01'),'레드팀','빨간색옷을 입은 팀',5,'N','ADMIN',NOW(),'ADMIN',NOW());


/* 2. 멤버정보 시퀀스 생성 */
DROP SEQUENCE SQ_N_TEST_MEMBER_01;
CREATE SEQUENCE SQ_N_TEST_MEMBER_01 START 1;

​/* 2.테스트 테이블 멤버정보 */
CREATE TABLE TEST_MEMBER (
‌MEMBER_SN 	NUMERIC(12) NOT NULL,
‌MEMBER_NM 	VARCHAR(60) NOT NULL,
‌MEMBER_DC 	VARCHAR(200),
‌SORT    	NUMERIC(4),
‌TEAM_SN     NUMERIC(12),
‌DEL_YN      CHAR(1)         NOT NULL,
​​​​REGISTER_ID VARCHAR(20)     NOT NULL,
​​​​REG_DT      TIMESTAMP       NOT NULL,
​​​​UPDUSR_ID   VARCHAR(20)     NOT NULL,
​​​​MDFCN_DT    TIMESTAMP       NOT NULL,
‌CONSTRAINT  TEST_MEMBER_PK PRIMARY KEY (MEMBER_SN),
‌CONSTRAINT  TEST_MEMBER_FK1 FOREIGN KEY (TEAM_SN) REFERENCES TEST_TEAM(TEAM_SN) ON DELETE CASCADE
);

COMMENT ON TABLE TEST_MEMBER IS '멤버정보';
COMMENT ON COLUMN TEST_MEMBER.MEMBER_SN IS '멤버일련번호';
COMMENT ON COLUMN TEST_MEMBER.MEMBER_NM IS '멤버명';
COMMENT ON COLUMN TEST_MEMBER.MEMBER_DC IS '멤버설명';
COMMENT ON COLUMN TEST_MEMBER.SORT IS '멤버순서'; 
COMMENT ON COLUMN TEST_MEMBER.TEAM_SN IS '팀일련번호';
COMMENT ON COLUMN TEST_MEMBER.DEL_YN IS '삭제여부';
COMMENT ON COLUMN TEST_MEMBER.REGISTER_ID IS '등록자ID';
COMMENT ON COLUMN TEST_MEMBER.REG_DT IS '등록일시';
COMMENT ON COLUMN TEST_MEMBER.UPDUSR_ID IS '수정자ID';
COMMENT ON COLUMN TEST_MEMBER.MDFCN_DT IS '수정일시';

select *
​​from TEST_MEMBER;
​​
/* 멤버 테스트 데이터 등록 */
INSERT INTO TEST_MEMBER (MEMBER_SN,MEMBER_NM,MEMBER_DC,SORT,TEAM_SN,DEL_YN,REGISTER_ID,REG_DT,UPDUSR_ID,MDFCN_DT) 
‌‌‌​​​VALUES (nextval('SQ_N_TEST_MEMBER_01'),'이순신','거북선 선장',1,1,'N','ADMIN',NOW(),'ADMIN',NOW());
‌‌‌​​​
INSERT INTO TEST_MEMBER (MEMBER_SN,MEMBER_NM,MEMBER_DC,SORT,TEAM_SN,DEL_YN,REGISTER_ID,REG_DT,UPDUSR_ID,MDFCN_DT) 
‌‌‌​​​VALUES (nextval('SQ_N_TEST_MEMBER_01'),'홍길동','전설속에 등장인물',2,NULL,'N','ADMIN',NOW(),'ADMIN',NOW());
‌‌‌​​​
INSERT INTO TEST_MEMBER (MEMBER_SN,MEMBER_NM,MEMBER_DC,SORT,TEAM_SN,DEL_YN,REGISTER_ID,REG_DT,UPDUSR_ID,MDFCN_DT) 
‌‌‌​​​VALUES (nextval('SQ_N_TEST_MEMBER_01'),'김길동','보통사람',3,1,'N','ADMIN',NOW(),'ADMIN',NOW()); 
‌‌‌​​
INSERT INTO TEST_MEMBER (MEMBER_SN,MEMBER_NM,MEMBER_DC,SORT,TEAM_SN,DEL_YN,REGISTER_ID,REG_DT,UPDUSR_ID,MDFCN_DT) 
‌‌‌​​​VALUES (nextval('SQ_N_TEST_MEMBER_01'),'최길순','그냥보통사람',4,2,'N','ADMIN',NOW(),'ADMIN',NOW()); 			  
‌‌‌​​
INSERT INTO TEST_MEMBER (MEMBER_SN,MEMBER_NM,MEMBER_DC,SORT,TEAM_SN,DEL_YN,REGISTER_ID,REG_DT,UPDUSR_ID,MDFCN_DT) 
‌‌‌​​​VALUES (nextval('SQ_N_TEST_MEMBER_01'),'이영희','그냥평범한사람',5,3,'N','ADMIN',NOW(),'ADMIN',NOW()); 					  
‌‌‌​​

/* 1.권한정보 */
CREATE TABLE COMTNAUTHORINFO (
‌AUTHOR_CODE VARCHAR(30) NOT NULL,
‌AUTHOR_NM VARCHAR(60) NOT NULL,
‌AUTHOR_DC VARCHAR(200),
‌AUTHOR_CREAT_DE TIMESTAMP,
‌USE_AT CHAR(1),
‌CONSTRAINT  COMTNAUTHORINFO_PK PRIMARY KEY (AUTHOR_CODE)
);

COMMENT ON TABLE COMTNAUTHORINFO IS '권한정보';
COMMENT ON COLUMN COMTNAUTHORINFO.AUTHOR_CODE IS '권한코드';
COMMENT ON COLUMN COMTNAUTHORINFO.AUTHOR_CREAT_DE IS '권한생성일';
COMMENT ON COLUMN COMTNAUTHORINFO.AUTHOR_DC IS '권한설명';
COMMENT ON COLUMN COMTNAUTHORINFO.AUTHOR_NM IS '권한명';
COMMENT ON COLUMN COMTNAUTHORINFO.USE_AT IS '사용여부';


/* 2. 롤계층구조 */

DROP SEQUENCE SQ_N_COMTNROLES_HIERARCHY_01;
CREATE SEQUENCE SQ_N_COMTNROLES_HIERARCHY_01 START 1;

CREATE TABLE COMTNROLES_HIERARCHY (
​​​​COMTNROLES_HIERARCHY_SN numeric(12),
‌PARNTS_ROLE VARCHAR(30) NOT NULL,
‌CHLDRN_ROLE VARCHAR(30) NOT NULL,,
‌CONSTRAINT  COMTNROLES_HIERARCHY_PK PRIMARY KEY (COMTNROLES_HIERARCHY_SN),
​​​​CONSTRAINT  COMTNROLES_HIERARCHY_FK1 FOREIGN KEY (PARNTS_ROLE) REFERENCES COMTNAUTHORINFO(AUTHOR_CODE) ON DELETE CASCADE,
​​​​CONSTRAINT  COMTNROLES_HIERARCHY_FK2 FOREIGN KEY (CHLDRN_ROLE) REFERENCES COMTNAUTHORINFO(AUTHOR_CODE) ON DELETE CASCADE
‌
);

COMMENT ON TABLE COMTNROLES_HIERARCHY IS '롤계층구조';
COMMENT ON COLUMN COMTNROLES_HIERARCHY.COMTNROLES_HIERARCHY_SN IS '롤계층일련번호';
COMMENT ON COLUMN COMTNROLES_HIERARCHY.CHLDRN_ROLE IS '자식롤';
COMMENT ON COLUMN COMTNROLES_HIERARCHY.PARNTS_ROLE IS '부모롤';

CREATE UNIQUE INDEX COMTNROLES_HIERARCHY_I01 ON COMTNROLES_HIERARCHY (
‌PARNTS_ROLE ASC
);


CREATE INDEX COMTNROLES_HIERARCHY_I02 ON COMTNROLES_HIERARCHY (
‌CHLDRN_ROLE ASC
);

/* 3 롤정보 */
CREATE TABLE COMTNROLEINFO (
‌ROLE_CODE VARCHAR(50) NOT NULL,
‌ROLE_NM VARCHAR(60) NOT NULL,
‌ROLE_TY VARCHAR(80),
‌ROLE_PTTRN VARCHAR(300),
‌ROLE_DC VARCHAR(200),
‌ROLE_SORT numeric,
‌ROLE_CREAT_DE TIMESTAMP,
‌USE_AT CHAR(1),
‌CONSTRAINT  COMTNROLEINFO_PK PRIMARY KEY (ROLE_CODE)
);

COMMENT ON TABLE COMTNROLEINFO IS '역할정보';
COMMENT ON COLUMN COMTNROLEINFO.ROLE_CODE IS '롤코드';
COMMENT ON COLUMN COMTNROLEINFO.ROLE_CREAT_DE IS '롤생성일';
COMMENT ON COLUMN COMTNROLEINFO.ROLE_DC IS '롤설명';
COMMENT ON COLUMN COMTNROLEINFO.ROLE_NM IS '롤명';
COMMENT ON COLUMN COMTNROLEINFO.ROLE_PTTRN IS '롤패턴';
COMMENT ON COLUMN COMTNROLEINFO.ROLE_SORT IS '롤정렬';
COMMENT ON COLUMN COMTNROLEINFO.ROLE_TY IS '롤유형';
COMMENT ON COLUMN COMTNROLEINFO.USE_AT IS '사용여부';

/* 4. 권한역할 관계 */
DROP SEQUENCE SQ_N_COMTNAUTHORROLERELATE_01;
CREATE SEQUENCE SQ_N_COMTNAUTHORROLERELATE_01 START 1;

CREATE TABLE COMTNAUTHORROLERELATE (
​​​​COMTNAUTHORROLERELATE_SN numeric(12),
‌AUTHOR_CODE VARCHAR(30) NOT NULL,
‌ROLE_CODE VARCHAR(50) NOT NULL,
‌CREAT_DT TIMESTAMP,
‌CONSTRAINT  COMTNAUTHORROLERELATE_PK PRIMARY KEY (COMTNAUTHORROLERELATE_SN),
​​​​CONSTRAINT  COMTNAUTHORROLERELATE_FK1 FOREIGN KEY (AUTHOR_CODE) REFERENCES COMTNAUTHORINFO(AUTHOR_CODE) ON DELETE CASCADE,
​​​​CONSTRAINT  COMTNAUTHORROLERELATE_FK2 FOREIGN KEY (ROLE_CODE) REFERENCES COMTNROLEINFO(ROLE_CODE) ON DELETE CASCADE

);

COMMENT ON TABLE COMTNAUTHORROLERELATE IS '권한역할관계';
COMMENT ON COLUMN COMTNAUTHORROLERELATE.COMTNAUTHORROLERELATE_SN IS '권한역할일련번호';
COMMENT ON COLUMN COMTNAUTHORROLERELATE.AUTHOR_CODE IS '권한코드';
COMMENT ON COLUMN COMTNAUTHORROLERELATE.CREAT_DT IS '생성일시';
COMMENT ON COLUMN COMTNAUTHORROLERELATE.ROLE_CODE IS '롤코드';

CREATE INDEX COMTNAUTHORROLERELATE_I01 ON COMTNAUTHORROLERELATE (
‌AUTHOR_CODE ASC
);


CREATE INDEX COMTNAUTHORROLERELATE_I02 ON COMTNAUTHORROLERELATE (
‌ROLE_CODE ASC
);




/* 5 사용자별 권한정보 */
DROP SEQUENCE SQ_N_COMTNEMPLYRSCRTYESTBS_01;
CREATE SEQUENCE SQ_N_COMTNEMPLYRSCRTYESTBS_01 START 1;

CREATE TABLE COMTNEMPLYRSCRTYESTBS (
​​​​COMTNEMPLYRSCRTYESTBS_SN numeric(12),
‌USER_ID VARCHAR(20) NOT NULL,
‌AUTHOR_CODE VARCHAR(30) NOT NULL,
‌INSTT_ENTRPS_MNO VARCHAR(20) NOT NULL,
‌USE_AT CHAR(1),
‌RMK VARCHAR(100),
‌REGISTER_ID VARCHAR(20),
‌REGIST_DT TIMESTAMP,
‌UPDR_ID VARCHAR(20),
‌UPD_DT TIMESTAMP,
‌CONSTRAINT  COMTNEMPLYRSCRTYESTBS_PK PRIMARY KEY (COMTNEMPLYRSCRTYESTBS_SN)
);

COMMENT ON TABLE COMTNEMPLYRSCRTYESTBS IS '사용자별권한정보';
COMMENT ON COLUMN COMTNEMPLYRSCRTYESTBS.COMTNEMPLYRSCRTYESTBS_SN IS '사용자별권한일련번호';
COMMENT ON COLUMN COMTNEMPLYRSCRTYESTBS.AUTHOR_CODE IS '권한코드';
COMMENT ON COLUMN COMTNEMPLYRSCRTYESTBS.INSTT_ENTRPS_MNO IS '기관업체관리번호';
COMMENT ON COLUMN COMTNEMPLYRSCRTYESTBS.REGISTER_ID IS '등록자아이디';
COMMENT ON COLUMN COMTNEMPLYRSCRTYESTBS.REGIST_DT IS '등록일시';
COMMENT ON COLUMN COMTNEMPLYRSCRTYESTBS.RMK IS '비고';
COMMENT ON COLUMN COMTNEMPLYRSCRTYESTBS.UPDR_ID IS '수정자아이디';
COMMENT ON COLUMN COMTNEMPLYRSCRTYESTBS.UPD_DT IS '수정일시';
COMMENT ON COLUMN COMTNEMPLYRSCRTYESTBS.USER_ID IS '사용자아이디';
COMMENT ON COLUMN COMTNEMPLYRSCRTYESTBS.USE_AT IS '사용여부';


/* 6 즐겨찾기메뉴 */
DROP SEQUENCE SQ_N_COMTNFAVORMENUINFO_01;
CREATE SEQUENCE SQ_N_COMTNFAVORMENUINFO_01 START 1;

CREATE TABLE COMTNFAVORMENUINFO (
​​​​COMTNFAVORMENUINFO_SN numeric(12),
‌USER_ID VARCHAR(20) NOT NULL,
‌MENU_ID VARCHAR(8) NOT NULL,
‌MENU_NM VARCHAR(50),
‌GROUP_ID VARCHAR(8),
‌SORT_NO NUMERIC,
‌LVL NUMERIC,
‌SYSTEM_GBN VARCHAR(8),
‌AUTH VARCHAR(10),
‌MENU_URL VARCHAR(100),
‌CONSTRAINT  COMTNFAVORMENUINFO_1_PK PRIMARY KEY (COMTNFAVORMENUINFO_SN)
);


COMMENT ON TABLE COMTNFAVORMENUINFO IS '즐겨찾기메뉴';
COMMENT ON COLUMN COMTNFAVORMENUINFO.COMTNFAVORMENUINFO_SN IS '즐겨찾기메뉴일련번호';
COMMENT ON COLUMN COMTNFAVORMENUINFO.AUTH IS '버튼권한';
COMMENT ON COLUMN COMTNFAVORMENUINFO.GROUP_ID IS '그룹아이디';
COMMENT ON COLUMN COMTNFAVORMENUINFO.LVL IS '레벨';
COMMENT ON COLUMN COMTNFAVORMENUINFO.MENU_ID IS '메뉴아이디';
COMMENT ON COLUMN COMTNFAVORMENUINFO.MENU_NM IS '메뉴명';
COMMENT ON COLUMN COMTNFAVORMENUINFO.MENU_URL IS '메뉴URL';
COMMENT ON COLUMN COMTNFAVORMENUINFO.SORT_NO IS '메뉴순서';
COMMENT ON COLUMN COMTNFAVORMENUINFO.SYSTEM_GBN IS '시스템구분';
COMMENT ON COLUMN COMTNFAVORMENUINFO.USER_ID IS '사용자아이디';


/* 7 권한별 메뉴설정 */
DROP SEQUENCE SQ_N_COMTNMENUCREATINFO_01;
CREATE SEQUENCE SQ_N_COMTNMENUCREATINFO_01 START 1;

CREATE TABLE COMTNMENUCREATINFO (
​​​​COMTNMENUCREATINFO_SN numeric(12),
‌MENU_ID VARCHAR(8) NOT NULL,
‌AUTHOR_CODE VARCHAR(30) NOT NULL,
‌READ_ATHR VARCHAR(1),
‌REG_ATHR VARCHAR(1),
‌DEL_ATHR VARCHAR(1),
‌UPD_ATHR VARCHAR(1),
‌EXCEL_ATHR VARCHAR(1),
‌PRINT_ATHR VARCHAR(1),
‌MNL_ATHR VARCHAR(1),
‌USE_AT CHAR(1),
‌REGISTER_ID VARCHAR(20),
‌REGIST_DT TIMESTAMP,
‌UPDR_ID VARCHAR(20),
‌UPT_DT TIMESTAMP,
‌CONSTRAINT  COMTNMENUCREATINFO_1_PK PRIMARY KEY (COMTNMENUCREATINFO_SN)
);

COMMENT ON TABLE COMTNMENUCREATINFO IS '권한별 메뉴설정';
COMMENT ON COLUMN COMTNMENUCREATINFO.COMTNMENUCREATINFO_SN IS '권한별메뉴일련번호';
COMMENT ON COLUMN COMTNMENUCREATINFO.AUTHOR_CODE IS '권한코드';
COMMENT ON COLUMN COMTNMENUCREATINFO.DEL_ATHR IS '삭제권한';
COMMENT ON COLUMN COMTNMENUCREATINFO.EXCEL_ATHR IS '엑셀다운권한';
COMMENT ON COLUMN COMTNMENUCREATINFO.MENU_ID IS '메뉴아이디';
COMMENT ON COLUMN COMTNMENUCREATINFO.MNL_ATHR IS '메뉴얼권한';
COMMENT ON COLUMN COMTNMENUCREATINFO.PRINT_ATHR IS '출력권한';
COMMENT ON COLUMN COMTNMENUCREATINFO.READ_ATHR IS '읽기권한';
COMMENT ON COLUMN COMTNMENUCREATINFO.REGISTER_ID IS '등록자아이디';
COMMENT ON COLUMN COMTNMENUCREATINFO.REGIST_DT IS '등록일시';
COMMENT ON COLUMN COMTNMENUCREATINFO.REG_ATHR IS '등록권한';
COMMENT ON COLUMN COMTNMENUCREATINFO.UPDR_ID IS '수정자아이디';
COMMENT ON COLUMN COMTNMENUCREATINFO.UPD_ATHR IS '수정권한';
COMMENT ON COLUMN COMTNMENUCREATINFO.UPT_DT IS '수정일시';
COMMENT ON COLUMN COMTNMENUCREATINFO.USE_AT IS '사용여부';


/* 8 메뉴정보 */

CREATE TABLE COMTNMENUINFO (
‌MENU_ID VARCHAR(8) NOT NULL,
‌MENU_NM VARCHAR(50),
‌GROUP_ID VARCHAR(8) NOT NULL,
‌PROGRAM_ID VARCHAR(8),
‌SORT_NO NUMERIC,
‌MENU_DC VARCHAR(100),
‌UP_MENU_ID VARCHAR(8),
‌LVL NUMERIC,
‌MENU_GBN VARCHAR(1),
‌SYSTEM_GBN VARCHAR(8),
‌USE_AT CHAR(1),
‌REGISTER_ID VARCHAR(20),
‌REGIST_DT TIMESTAMP,
‌UPDR_ID VARCHAR(20),
‌UPD_DT TIMESTAMP,
‌CONSTRAINT  COMTNMENUINFO_1_PK PRIMARY KEY (MENU_ID)
);

COMMENT ON TABLE COMTNMENUINFO IS '메뉴정보';
COMMENT ON COLUMN COMTNMENUINFO.GROUP_ID IS '그룹아이디';
COMMENT ON COLUMN COMTNMENUINFO.LVL IS '레벨';
COMMENT ON COLUMN COMTNMENUINFO.MENU_DC IS '메뉴설명내용';
COMMENT ON COLUMN COMTNMENUINFO.MENU_GBN IS '메뉴사용구분';
COMMENT ON COLUMN COMTNMENUINFO.MENU_ID IS '메뉴아이디';
COMMENT ON COLUMN COMTNMENUINFO.MENU_NM IS '메뉴명';
COMMENT ON COLUMN COMTNMENUINFO.PROGRAM_ID IS '프로그램아이디';
COMMENT ON COLUMN COMTNMENUINFO.REGISTER_ID IS '등록자아이디';
COMMENT ON COLUMN COMTNMENUINFO.REGIST_DT IS '등록일시';
COMMENT ON COLUMN COMTNMENUINFO.SORT_NO IS '메뉴순서';
COMMENT ON COLUMN COMTNMENUINFO.SYSTEM_GBN IS '시스템구분';
COMMENT ON COLUMN COMTNMENUINFO.UPDR_ID IS '수정자아이디';
COMMENT ON COLUMN COMTNMENUINFO.UPD_DT IS '수정일시';
COMMENT ON COLUMN COMTNMENUINFO.UP_MENU_ID IS '상위메뉴번호';
COMMENT ON COLUMN COMTNMENUINFO.USE_AT IS '사용여부';

/* 9 프로그램목록 */
CREATE TABLE COMTNPROGRMLIST (
‌PROGRAM_ID VARCHAR(8) NOT NULL,
‌PROGRAM_NM VARCHAR(50) NOT NULL,
‌PROGRAM_URL VARCHAR(100) NOT NULL,
‌SYSTEM_GBN VARCHAR(8) NOT NULL,
‌PROGRAM_GBN VARCHAR(8) NOT NULL,
‌RMK VARCHAR(100),
‌USE_AT CHAR(1),
‌REGISTER_ID VARCHAR(20),
‌REGIST_DT TIMESTAMP,
‌UPDR_ID VARCHAR(20),
‌UPD_DT TIMESTAMP,
‌CONSTRAINT  COMTNAUTHORROLERELATE_1_PK PRIMARY KEY (PROGRAM_ID)
);

COMMENT ON TABLE COMTNPROGRMLIST IS '프로그램목록';
COMMENT ON COLUMN COMTNPROGRMLIST.PROGRAM_GBN IS '프로그램구분';
COMMENT ON COLUMN COMTNPROGRMLIST.PROGRAM_ID IS '프로그램아이디';
COMMENT ON COLUMN COMTNPROGRMLIST.PROGRAM_NM IS '프로그램명';
COMMENT ON COLUMN COMTNPROGRMLIST.PROGRAM_URL IS '프로그램URL';
COMMENT ON COLUMN COMTNPROGRMLIST.REGISTER_ID IS '등록자아이디';
COMMENT ON COLUMN COMTNPROGRMLIST.REGIST_DT IS '등록일시';
COMMENT ON COLUMN COMTNPROGRMLIST.RMK IS '비고';
COMMENT ON COLUMN COMTNPROGRMLIST.SYSTEM_GBN IS '시스템구분';
COMMENT ON COLUMN COMTNPROGRMLIST.UPDR_ID IS '수정자아이디';
COMMENT ON COLUMN COMTNPROGRMLIST.UPD_DT IS '수정일시';
COMMENT ON COLUMN COMTNPROGRMLIST.USE_AT IS '사용여부';

java

닫기 줄바꿈

/* 1.권한정보 */
INSERT INTO COMTNAUTHORINFO VALUES ('ROLE_ANONYMOUS'               ,'모든 사용자'      , '', NOW(),'Y');
INSERT INTO COMTNAUTHORINFO VALUES ('IS_AUTHENTICATED_ANONYMOUSLY' ,'스프링시큐리티 내부사용(롤부여 금지)'      , '', NOW(),'Y');
INSERT INTO COMTNAUTHORINFO VALUES ('IS_AUTHENTICATED_REMEMBERED'  ,'스프링시큐리티 내부사용(롤부여 금지)', '', NOW(),'Y');
INSERT INTO COMTNAUTHORINFO VALUES ('IS_AUTHENTICATED_FULLY'       ,'스프링시큐리티 내부사용(롤부여 금지)'    , '', NOW(),'Y');
INSERT INTO COMTNAUTHORINFO VALUES ('ROLE_USER'                    ,'일반 사용자'      , '', NOW(),'Y');
INSERT INTO COMTNAUTHORINFO VALUES ('ROLE_ADMIN'                   ,'관리자'           , '', NOW(),'Y');


/* 2. 롤계층구조 */
INSERT INTO COMTNROLES_HIERARCHY VALUES (nextval('SQ_N_COMTNROLES_HIERARCHY_01'), 'ROLE_ANONYMOUS'               ,'IS_AUTHENTICATED_ANONYMOUSLY');
INSERT INTO COMTNROLES_HIERARCHY VALUES (nextval('SQ_N_COMTNROLES_HIERARCHY_01'), 'IS_AUTHENTICATED_ANONYMOUSLY' ,'IS_AUTHENTICATED_REMEMBERED');
INSERT INTO COMTNROLES_HIERARCHY VALUES (nextval('SQ_N_COMTNROLES_HIERARCHY_01'), 'IS_AUTHENTICATED_REMEMBERED'  ,'IS_AUTHENTICATED_FULLY');
INSERT INTO COMTNROLES_HIERARCHY VALUES (nextval('SQ_N_COMTNROLES_HIERARCHY_01'), 'IS_AUTHENTICATED_FULLY'       ,'ROLE_USER');
INSERT INTO COMTNROLES_HIERARCHY VALUES (nextval('SQ_N_COMTNROLES_HIERARCHY_01'),'ROLE_USER'                    ,'ROLE_ADMIN');


/* 3 롤정보 */
INSERT INTO COMTNROLEINFO(ROLE_CODE,ROLE_NM,ROLE_TY,ROLE_PTTRN,ROLE_DC,ROLE_SORT,ROLE_CREAT_DE,USE_AT) VALUES('ROLE00001','로그인롤','url','\A/grain/mbr/.*\.do.*\Z','로그인허용을 위한 룰',1,NOW(),'Y');
INSERT INTO COMTNROLEINFO(ROLE_CODE,ROLE_NM,ROLE_TY,ROLE_PTTRN,ROLE_DC,ROLE_SORT,ROLE_CREAT_DE,USE_AT) VALUES('ROLE00002','접근테스트롤','url','\A/grain/main/.*\.do.*\Z','로그인메인화면 접근 허용을 위한 룰',10,NOW(),'Y');
INSERT INTO COMTNROLEINFO(ROLE_CODE,ROLE_NM,ROLE_TY,ROLE_PTTRN,ROLE_DC,ROLE_SORT,ROLE_CREAT_DE,USE_AT) VALUES('ROLE00099','모든접근제한','url','\A/.*\.do.*\Z','모든자원에 대한 접근 제한 룰',999,NOW(),'Y');

/* 4. 권한역할 관계 */
INSERT INTO COMTNAUTHORROLERELATE(COMTNAUTHORROLERELATE_SN,AUTHOR_CODE,ROLE_CODE,CREAT_DT) VALUES(nextval('SQ_N_COMTNAUTHORROLERELATE_01'),'ROLE_ANONYMOUS','ROLE00001',NOW());
INSERT INTO COMTNAUTHORROLERELATE(COMTNAUTHORROLERELATE_SN,AUTHOR_CODE,ROLE_CODE,CREAT_DT) VALUES(nextval('SQ_N_COMTNAUTHORROLERELATE_01'),'ROLE_ADMIN','ROLE00099',NOW());