logo

한국어

[GATEONE-Z] 유용한 SQL 쿼리 모음

관리자 2019.03.21 15:55 조회 수 : 45

-- 1. 윈도우 장비 서버해상도를 FULL SCREEN으로 일괄 변경하기 (윈도우 접속 설정)
DELETE FROM ONE_USER_DEVICE_SETTING;
INSERT INTO ONE_USER_DEVICE_SETTING (USERID, DEVID, RDP_CONSOLE, RDP_RESOLUTION, HTTP_BROWSER)  SELECT 'gateadm', DEVID, '0', 'fullscreen', 'I' FROM ONE_DEVICE WHERE ONE_DEVICE.WINDOW='Y';


-- 2. 로컬 리소스 드라이브 일괄설정 (윈도우 접속 설정)
UPDATE ONE_USER_DEVICE_SETTING SET DISK_SHARING = '로컬 디스크 (C:)|' WHERE USERID = 'gateadm' AND DEVID IN (SELECT DEVID FROM ONE_DEVICE WHERE ONE_DEVICE.WINDOW='Y');


-- 3. 장비 수정 쿼리
-- ※ 뒤에 WHERE 절을 없애면 모든 장비에 일괄 적용
UPDATE ONE_DEVICE SET TELNET='N', T_PORT='23', SSH='Y', S_PORT='22' ,RESIZE_TERM='1', TANDEM_6530='N', USE_GET_PTY='1', KEYBOARD_INTERACTIVE='0', SSH_LOGIN_PW_ONLY='0',  RELAY_CONN='N', RELAY_REG='N', RELAY_DEVID='0',  SECO_AUTH_COMM='', SECO_AUTH_PW='',  FTP='N', F_PORT='21', SFTP='Y', SF_PORT='22', FTP_PASV='1',  ENCRYPTION_AREA='F', FTP_SECO_AUTH_COMM='', FTP_SECO_AUTH_PW='',  WINDOW='N', W_PORT='3389', VNC='N', V_PORT='5900', VNC_SERVER_PASSWORD='',  HTTP='N', HTTP_DEFAULT_PAGE='', HTTP_DIRECT='N',  MTIME=NULL WHERE DEVID='39';


-- 4. 사용자 정보 수정 쿼리
-- ※ 전화번호 및 이메일은 암호화 되어 저장됨
UPDATE ONE_USER set MTIME=UNIX_TIMESTAMP(NOW()), ATTR='9', UATTR='9', COMPANY='SK', GID='10010', POSITION='', UNINUM='0', REGISTER='gateadm', TELECOM='SKT', DEPARTMENT='',TEAM='', WORK='', WEB_OTP='N',  DEV_OTP='N', VTERMS='1553007600', VTERME='unlimited', AUTH_FACTOR='0000', DESCR='', DESCR_note='', STAT='6', PERIOD='N', GTERMS='', GTERME='', AD_USER='N', OTP_SECRET='', KERBEROS_USE='N ' WHERE USERID='skcc5765';


-- 5. 사용자 접속 권한 등록
-- ※ hasu0707 사용자에 대한 DEVID:40번 장비의 접속 권한 등록
INSERT INTO PASS_USER_ACCESS_LIST (USERID, DEVID, VTERMS, VTERME, RTIME, FRPASS, TELNET, SSH, FTP, SFTP, WINDOW, VNC, HTTP ) values('hasu0707', '40', 1553475600, 4102444740, UNIX_TIMESTAMP(NOW()), 1, 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '' );


-- 6. 접속 권한 그룹 추가
-- ※ FLOW_USER와 FLOW_SYSTEM 테이블이 각각 사용자와 장비를 나타내며, 같은 FLOWID를 기준으로 어떤 사용자가 어느 장비에 접속할 지를 나타낸다.
INSERT INTO FLOW_USER (FLOWID, FLOWUSER) values ('1', 'skcc5714');
INSERT INTO FLOW_SYSTEM (FLOWID, FLOWSYSTEM, PROTOCOL) values ('1', '34', '1111110');


-- 7. 접속 권한 그룹(서버) 추가
-- ※ 접속 권한 그룹(서버)는 웹상에서 빈 상태로 먼저 등록한다.
INSERT INTO FLOW ( FLOWNAME, ATTR, PROTOCOL, REGISTER, EVENT_FACTOR, EVENT_RECEIVER, REQUESTER, RTIME  ) values ( 'GROUP_AIX', '4', '', 'gateadm', '0000', '00000', '',UNIX_TIMESTAMP(NOW())  );

-- ※ 3은 FLOW ID로 FLOW 테이블에서 확인해야 한다.
-- ※ 344는 ONE_DEVICE 테이블의 DEVID
-- ※ 1111110은 허용하는 프로토콜 TELNET,SSH,FTP,SFTP,WINDOW,VNC
INSERT INTO FLOW_SYSTEM (FLOWID, FLOWSYSTEM, PROTOCOL) values ('3', '1', '1111110');
INSERT INTO FLOW_SYSTEM (FLOWID, FLOWSYSTEM, PROTOCOL) values ('3', '2', '1111000');
INSERT INTO FLOW_SYSTEM (FLOWID, FLOWSYSTEM, PROTOCOL) values ('3', '3', '0111000');


-- 8. 그룹 추가
-- ※ ATTR이 0:공용그룹, 1:사용자그룹, 2:시스템그룹
-- ※ 10001는 그룹ID
-- ※ 그룹명은 "Unix파트/x86파트"
INSERT INTO ONE_GROUP (GID, GNAME, PGID, ATTR, DESCR, REGISTER, RTIME, LOCATION_GID, LOCATION_GNAME, USER_REQUEST_GROUP, OTP_TYPE, AUTH_FACTOR  , ACCU_RULE_UNIX, ACCU_RULE_WIN, ACCU_RULE_NETWORK, ACCU_RULE_LINUX, ACCU_RULE_AIX, ACCU_RULE_HPUX, ACCU_RULE_SECURITY ) VALUES (  '10001','Unix파트/x86파트', 0, '2', '', 'gateadm', UNIX_TIMESTAMP(NOW()), '10001', 'Unix파트/x86파트', '0'  ,'00', '0000'   , 0, 0, 0, 0, 0, 0, 0);


-- 9. 금지 룰 등록
-- ※ telnet_cmd는 금지 룰명
INSERT INTO ONE_POLICY_DENY (RULE_NAME, APPLY_DAY, APPLY_TIME, DENY_WR_MSG, DENY_WR_CNT, RTIME) VALUE ('telnet_cmd', '1111111', '0024', 'GATEONE : Not allowed command!', '0', UNIX_TIMESTAMP(NOW()));

-- ※ 3은 INTO ONE_POLICY_DENY 테이블의 SEQ 넘버
INSERT INTO ONE_POLICY_DENY_LIST (FSEQ, ACODE, PROTOCOL, CMD, WORD, REGEX, CONTAINS, RTIME) VALUE (3, '102', '1111', '', 'telnet', '1', '1', UNIX_TIMESTAMP(NOW()));


-- 10. 금지 룰 설정 (사용자 그룹 기반)
-- ※ 3은 INTO ONE_POLICY_DENY 테이블의 SEQ 넘버
-- ※ 10017은 ONE_GROUP의 GID

INSERT INTO ONE_POLICY (TYPE, TYPE_ID, USERID, DEVID, SERVICE_ID, TIME_ID, ACCOUNT_ID, DETECT_ID, ALLOW_ID, DENY_ID, SACL_ID, SCRIPT_ID, WINDOW_ID, RTIME) VALUE ('5', '10017', '', '', '', '', '', '', '', '3', '', '', '', UNIX_TIMESTAMP(NOW()));


-- 11. 장비ID/사용자명과 그룹명 SELECT
SELECT DEVID, ( SELECT GNAME FROM ONE_GROUP WHERE ONE_GROUP.GID=ONE_DEVICE.GID ) FROM ONE_DEVICE ORDER BY DEVID ASC;
SELECT USERID, ( SELECT GNAME FROM ONE_GROUP WHERE ONE_GROUP.GID=ONE_USER.GID ) FROM ONE_USER ORDER BY USERID ASC;


-- 12. 클라이언트 프로그램 경로 일괄 변경
-- ※ SSH_VERSION은 SecureCRT 버전 (8.x.x)
-- ※ 특정 사용자만 변경하려면 뒤에 WHERE USERID='xxx' 를 추가해 주면 된다.
UPDATE ONE_USER_PATH SET PATH_SSH = 'C:\\\\Program Files\\\\VanDyke Software\\\\SecureCRT\\\\SecureCRT.exe', SSH_VERSION='8';
UPDATE ONE_USER SET PATH_SSH = 'C:\\\\Program Files\\\\VanDyke Software\\\\SecureCRT\\\\SecureCRT.exe', SSH_VERSION='8';


-- 13. 도구 > 정책 (금지 객체 만들기)
-- 초기화
DELETE FROM ONE_CMD_WORD;
DELETE FROM ONE_POLICY;
DELETE FROM ONE_POLICY_DENY;
DELETE FROM ONE_POLICY_DENY_LIST;
ALTER TABLE ONE_POLICY AUTO_INCREMENT=1;
ALTER TABLE ONE_POLICY_DENY AUTO_INCREMENT=1;

-- 도구 > 정책 > 명령어 사전
INSERT INTO ONE_CMD_WORD (WORD, RTIME) VALUE ('reboot', UNIX_TIMESTAMP(NOW()));
INSERT INTO ONE_CMD_WORD (WORD, RTIME) VALUE ('rm', UNIX_TIMESTAMP(NOW()));
INSERT INTO ONE_CMD_WORD (WORD, RTIME) VALUE ('rmdir', UNIX_TIMESTAMP(NOW()));
INSERT INTO ONE_CMD_WORD (WORD, RTIME) VALUE ('shutdown', UNIX_TIMESTAMP(NOW()));
INSERT INTO ONE_CMD_WORD (WORD, RTIME) VALUE ('ssh', UNIX_TIMESTAMP(NOW()));
INSERT INTO ONE_CMD_WORD (WORD, RTIME) VALUE ('telnet', UNIX_TIMESTAMP(NOW()));

-- 도구 > 정책 > 룰 등록 > 금지
INSERT INTO ONE_POLICY_DENY (RULE_NAME, APPLY_DAY, APPLY_TIME, DENY_WR_MSG, DENY_WR_CNT, RTIME)  value ('위험한명령어', '1111111', '0024', 'GATEONE : Not allowed command!', '0', UNIX_TIMESTAMP(NOW()));
INSERT INTO ONE_POLICY_DENY_LIST (FSEQ, ACODE, PROTOCOL, CMD, WORD, REGEX, CONTAINS, RTIME) VALUE (1, '102', '1111', 'reboot', 'reboot', '1', '1', UNIX_TIMESTAMP(NOW()));
INSERT INTO ONE_POLICY_DENY_LIST (FSEQ, ACODE, PROTOCOL, CMD, WORD, REGEX, CONTAINS, RTIME) VALUE (1, '102', '1111', 'rm', 'rm', '1', '1', UNIX_TIMESTAMP(NOW()));
INSERT INTO ONE_POLICY_DENY_LIST (FSEQ, ACODE, PROTOCOL, CMD, WORD, REGEX, CONTAINS, RTIME) VALUE (1, '102', '1111', 'rmdir', 'rmdir', '1', '1', UNIX_TIMESTAMP(NOW()));
INSERT INTO ONE_POLICY_DENY_LIST (FSEQ, ACODE, PROTOCOL, CMD, WORD, REGEX, CONTAINS, RTIME) VALUE (1, '102', '1111', 'shutdown', 'shutdown', '1', '1', UNIX_TIMESTAMP(NOW()));
INSERT INTO ONE_POLICY_DENY_LIST (FSEQ, ACODE, PROTOCOL, CMD, WORD, REGEX, CONTAINS, RTIME) VALUE (1, '102', '1111', 'ssh', 'ssh', '1', '1', UNIX_TIMESTAMP(NOW()));
INSERT INTO ONE_POLICY_DENY_LIST (FSEQ, ACODE, PROTOCOL, CMD, WORD, REGEX, CONTAINS, RTIME) VALUE (1, '102', '1111', 'telnet', 'telnet', '1', '1', UNIX_TIMESTAMP(NOW()));

-- 도구 > 정책 > 룰 설정 > 사용자그룹
-- ※ 이 부분은 웹UI에서 처리한다.
-- INSERT INTO ONE_POLICY (TYPE, TYPE_ID, USERID, DEVID, SERVICE_ID, TIME_ID, ACCOUNT_ID, DETECT_ID, ALLOW_ID, DENY_ID, SACL_ID, SCRIPT_ID, WINDOW_ID, RTIME) VALUE ('5', '10008', '', '', '', '', '', '', '', '1', '', '', '', 'UNIX_TIMESTAMP(NOW())');


-- 14. 도구 > 업체(회사) 등록

INSERT INTO ONE_COMPANY (CNAME, CACCOUNT, GID, REGISTER, RTIME) VALUES ('(주)이씨큐밸리', '', 0, 'gateadm', UNIX_TIMESTAMP(NOW()));
INSERT INTO ONE_COMPANY (CNAME, CACCOUNT, GID, REGISTER, RTIME) VALUES ('SK', '', 0, 'gateadm', UNIX_TIMESTAMP(NOW()));
INSERT INTO ONE_COMPANY (CNAME, CACCOUNT, GID, REGISTER, RTIME) VALUES ('SK주식회사 C&C', '', 0, 'gateadm', UNIX_TIMESTAMP(NOW()));
INSERT INTO ONE_COMPANY (CNAME, CACCOUNT, GID, REGISTER, RTIME) VALUES ('현대백화점 그룹', '', 0, 'gateadm', UNIX_TIMESTAMP(NOW()));
INSERT INTO ONE_COMPANY (CNAME, CACCOUNT, GID, REGISTER, RTIME) VALUES ('현대홈쇼핑', '', 0, 'gateadm', UNIX_TIMESTAMP(NOW()));