정보 보안 랩(Lab) 파트 III: Oracle DB 11g XE 설치

나. 관계형 데이터베이스 관리 시스템(RDBMS) 설치하기

1. Oracle DB 11g XE 설치하기

oracle-xe-11.2.0-1.0.x86_64.rpm.zip 파일 압축 해제하기 위해 unzip 패키지를 설치하고 
설치할 때 netstat 명령어를 사용하므로 net-tools 패키지도 같이 설치한다.
# yum -y install unzip net-tools

 

[참고] Oracle 관련 파일들을 서버로 올리려면 winscp를 설치 해야함

WinSCP는 마이크로소프트 윈도우용으로 개발된 오픈소스 기반의 SFTP, SCP 및 FTP 프로토콜콜을 사용해서 파일을
서버에 업로드하는 프로그램이다.

WinSCP 설치 가이드 사이트

[중요!!]

VMware에서 메모리를 2G로 설정했음에도 메모리 스왑 부족으로 Oracle XE 11g 설치가 안 될 경우

SWAP(스왑) 파일을 생성해서 SWAP 메모리 용량을 늘려주면 된다.

 

- VMware 서버 메모리 증설를 위해 서버 종료

[root@localhost ~]# init 0

 

1.1. SWAP 메모리 용량 증설하기
SWAP 파일 위치: /root/swapfile
SWAP 파일 크기: 2G

 

1) 2G(bs * count) 스왑 파일 생성
[root@localhost ~]# dd if=/dev/zero of=swapfile bs=1024 count=2000000

위 사용 명령어 설명 :  
dd 명령어는 파일의 내용을 복사하거나 포맷하는 데 사용되는 명령어로 
if=/dev/zero: /dev/zero 장치에서 데이터를 읽기 | /dev/zero 장치는 0으로 채워진 장치
of=swapfile: swapfile 파일에 데이터를 쓰기
bs=1024: 1024 바이트 단위로 데이터를 읽고 쓰기

count=2000000: 2000000개의 1024 바이트 데이터를 읽고 쓰기

따라서 이 명령어는 swapfile 파일을 2000000개의 1024 바이트 0으로 채우며 약 2G의 스왑 메모리를 생성


출처: Bard 알려준 정보를 편집 함

2) 생성된 스왑 파일 확인

[root@localhost ~]# ls -lh swapfile
-rw-r--r--. 1 root root 2.0G 10월 26 16:29 swapfile

 

3) swapfile 파일을 스왑 파일로 포맷  

[root@localhost ~]# mkswap swapfile     

 

4) 보안을 위해 swap 파일 권한 변경     
[root@localhost ~]# chmod 600 swapfile     

 

5) wapfile 파일을 스왑 메모리로 활성화
[root@localhost ~]# swapon swapfile         

 

6) 스왑 사이즈 확인

[root@localhost ~]# free                               
              total        used        free      shared  buff/cache   available
Mem:        1863004      361140       68640      420028     1433224      925032
Swap:       4097144       69384     4027760

1.2. Oracle XE 11g 다운로드 및 업로드

- Oracle XE 사용하기 위한 최소 메모리 용량은 2G 이다.

 

1) 다운로드 사이트

https://www.oracle.com/database/technologies/xe-downloads.html

 

[참고] Oracle XE 11g 설치 상세 가이드 사이트

 

2) WinSCP를 이용해 리눅스 서버에 업로드

 

 

1.3. Oracle XE 설치

1) 업로드된 폴더에서 upzip 명령어로 파일 압축 풀기

# unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip

 

2) 압축이 풀린 폴더로 이동
# cd Disk1/

 

3) 폴더 확인
# ls
oracle-xe-11.2.0-1.0.x86_64.rpm  response  upgrade

 

4) 폴더 안에 있는 rpm  파일 설치

# rpm -Uvh oracle-xe-11.2.0-1.0.x86_64.rpm

사용 옵션 설명: 
-U: 패키지를 업그레이드
-v: 자세한 정보를 출력
-h: 설치 진행 상황을 # 문자를 이용하여 출력

출처: Bard 알려준 정보를 편집 함

 

5) Oracle-xe 설정하기

- 아래 명령어를 입력하면 다일로그 형식의 설정을 하며 http port번호를 9090한 이유는 JSP 서비스를 이미 설정 됨 

 

# /etc/init.d/oracle-xe configure

Oracle Database 11g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 11g Express
... 생략 ...

Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:9090  <-- 9090입력

Specify a port that will be used for the database listener [1521]:  <-- 1521번 포트 사용하기 위해서 엔터

Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of
different passwords for each database account.  This can be done after
initial configuration:  <-- DBA(DataBase Administrator) 비밀번호 설정: oracle 입력
Confirm the password:   <-- DBA(DataBase Administrator) 비밀번호 설정 확인: oracle 입력

Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:  <-- 엔터

Starting Oracle Net Listener...Done
Configuring database...Done
Starting Oracle Database 11g Express Edition instance...Done
Installation completed successfully.

설치가 완료되면 업로드한 Oracle 관련 파일들 모두 삭제한다.
# cd ..
# rm -rf oracle-xe-11.2.0-1.0.x86_64.rpm.zip Disk1

 

6) rlwrap 프로그램 설치 

- rlwrap은 오라클 사용할 때 조금 더 편리하게 사용을 제공하는 프로그램

# yum -y install wget
# wget linuxmaster.net/tools/rlwrap -O /usr/local/bin/rlwrap
# chmod 755 /usr/local/bin/rlwrap

7) Oracle 사용자 설정
- 설치 완료 후, 리룩스에서 oracle 계정 유/무 확인

# grep oracle /etc/passwd
oracle:x:1002:1002::/u01/app/oracle:/bin/bash

 

 

1.4. Oracle XE 접속하기

1) su 명령어로 oracle 계정으로 스위치

# su - oracle

su - oracle 명령어를 사용하여 oracle 사용자로 전환해서 오라클 데이터베이스 관리 및 
오라클 관련 파일에 액세스할 수 있다.

 

2) /etc/skel/.bash* 파일을 현재 위치(.)에 복사 및 .bashrc 파일 편집 그리고 실행하기
-bash-4.2$ cp /etc/skel/.bash* .
-bash-4.2$ vi .bashrc
  ... 생략 ...

 

아래 2개 내용 추가

source /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
alias sqlplus='rlwrap sqlplus'

 

그래소 source 명령어를 사용해 .bashrc 실행

-bash-4.2$ source .bashrc

 

3) 기본 계정으로 접속하기
※ sqlplus: Oracle 서버에 접속하는 클라이언트 프로그램
※  접속 형식: sqlplus --help
     - 첫 번째: sqlplus
     - 두 번째: sqlplus 사용자/비밀번호
     - 세 번째: sqlplus / as sysdba

- 첫 번째 접속 방법으로 접속

$ sqlplus
SQL*Plus: Release 11.2.0.2.0 Production on 금 7월 7 15:19:55 2023
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: system
Enter password:   <-- oracle 입력
SQL> SHOW USER     // 로그인 사용자 확인 명령어
SQL> exit                     // 빠져 나온다.

- 두 번째 접속 방법으로 접속
$ sqlplus system/oracle
SQL> SHOW USER
SQL> exit

- 세 번째 접속 방법(관리자 권한으로) 접속
$ sqlplus / as sysdba
SQL> SHOW USER
SQL> exit

[참고] sqlplus의 자세한 도움말은 --help 을 사용한다.
$ sqlplus --help

 

1.5. Oracle DB 사용해보기

1) $ sqlplus / as sysdba 계정으로 접속

SQL> SELECT * FROM ALL_USERS;

USERNAME                          USER_ID CREATED
------------------------------ ---------- --------
XS$NULL                        2147483638 11/08/28
... 생략 ...
OUTLN                                   9 11/08/28
SYSTEM                                  5 11/08/28
USERNAME                          USER_ID CREATED
------------------------------ ---------- --------
SYS                                     0 11/08/28

2) DB 사용자 생성하기

- 사용자: scott
- 비밀번호: tiger

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY tiger;
ALTER USER SCOTT DEFAULT TABLESPACE USERS;
ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;
CONNECT SCOTT/tiger

 

3) scott 계정으로 접속 후, DEPT, EMP, BONUS 테이블 생성 및 데이터 추가하기

- 2번 작업 후, scott 계정으로 접속 됨

- 아래 코드들 복사해서 붙여넣기

DROP TABLE DEPT;

CREATE TABLE DEPT
(
    DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
    DNAME VARCHAR2(14),
    LOC VARCHAR2(13)
);
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

DROP TABLE EMP;
CREATE TABLE EMP
(
    EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);
INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP 
VALUES(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP 
VALUES(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP 
VALUES(7788,'SCOTT','ANALYST',7566,to_date('13-7-1987','dd-mm-yyyy')-85,3000,NULL,20);
INSERT INTO EMP 
VALUES(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP 
VALUES(7876,'ADAMS','CLERK',7788,to_date('13-7-1987','dd-mm-yyyy')-51,1100,NULL,20);
INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

DROP TABLE BONUS;
CREATE TABLE BONUS
(
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    SAL NUMBER,
    COMM NUMBER
);

DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
( 
    GRADE NUMBER,
    LOSAL NUMBER,
    HISAL NUMBER 
);
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;

SQL> exit  // DB 빠져 나옴

 

5) scott 계정으로 DB 접속 해서 계정확인

[oracle@localhost ~]$ sqlplus SCOTT/tiger
SQL> SHOW USER;
USER is "SCOTT"

6) 생성된 테이블 목록 확인

- MariaDB, MySQL: SHOW TABLES;
- Oracle: SELECT * FROM TAB; 

 

SQL> SELECT * FROM TAB;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                             TABLE
EMP                               TABLE
SALGRADE                   TABLE

[참고] 콘솔 화면에 테이블 컬럼이 많아 보기 불편하면 환경변수 LINESIZE를 사용해 설정
SQL> SET LINESIZE 200

7) EMP 테이블에 저장된 데이터를 출력하기

SQL> SELECT * FROM EMP;

- EMP 테이블에 저장된 데이터에서 사원명이 KING과 같은 이름을 출력해보기

SQL> SELECT * FROM EMP WHERE ENAME = 'KING';

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7839 KING       PRESIDENT            81/11/17       5000                    10

SQL> exit

 

1.6. SQLDeloper 다운로드
Oracle SQL Developer은 오라클사에서 제공하는 무료 DB 클라이언트 프로그램

- SQLDeloper 다운로드 사이트

SQL Developer

- SQLDeloper 설정 방법 참고 사이트

sqldeveloper 로 오라클 DBMS에 접속하기

 

1.7. SSH 터널링으로 DB 서버 접속하기 

1) 터널링 접속을 위해 서버에서 사용자 생성 및 비밀번호 생성/변경
- useradd: 사용자 생성하는 명령어
[root@localhost ~]# useradd tunneluser

 

- passwd: 비밀번호 변경하는 명령어

[root@localhost ~]# passwd tunneluser

2) SSH 터널링 접속 전 기존 ssh 공개된 저장된 파일 삭제
C:\Users\ITSC>del .ssh\known_hosts

 

※ .ssh\known_hosts: 공개키 저장된 파일

 

3) CMD 창에서 SSH 명령어로 접속

C:\Users\ITSC>ssh -L 1521:127.0.0.1:1521 tunneluser@192.168.100.155

명령어 옵션 설명: 
-L: 로컬 포워딩
1521: Local Port
127.0.0.1: Loopback(NIC 내부 사용을 위한 자신의 IP)
1521: Remote Port

The authenticity of host '192.168.100.155 (192.168.100.155)' can't be established.
ECDSA key fingerprint is SHA256:Y9FDAyN3ZXTlejpw9EB8REpzTm6SIgyz+SG3kux2YMU.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.100.155' (ECDSA) to the list of known hosts.
tunneluser@192.168.100.155's password:    <-- 패스워드 입력
Last login: Fri Oct 27 15:51:19 2023 from 192.168.100.1
[tunneluser@localhost ~]$        <-- 서버에 접속

 

4) CMD 새창에서 네트워크 상태 확인: 1521번(Oracle 사용 포트)을 확인 할 수 있다.
C:\Users\ITSC>netstat -na | findstr 1521
  TCP    127.0.0.1:1521         0.0.0.0:0              LISTENING
  TCP    [::1]:1521                  [::]:0                     LISTENING

 

 

5) SSH 터널링 후, SQLDeloper 에서 scott계정으로 Oracle 접속하기

- Name: OracleServer
- 사용자 이름: scott 롤: 기본값
- 비밀번호: tiger   비밀번호 저장: 체크
- 호스트 이름: localhost    <-- 터널링이 되어 있기 때문에 localhost로 접속 가능
- 포트: 1521
- SID: xe

 

위 설정 후, 테스트 버튼을 눌러 성공 메시지 확인

6) SQL 쿼리 실행하기
SQLDeloper에서 SQL문을 생성한 후에 "Ctrl + 엔터" 를 치면 실행 됨

SELECT * FROM TAB;

 

7) 테이블 생성하기
CREATE TABLE member (
    no number not null primary key,
    name varchar(20) not null,
    phone varchar(15),
    email varchar(50) not null
);

7) 생성한 테이블에 데이터 넣기
INSERT INTO member VALUES(1, '홍길동', '010-1111-2222', 'mrhong@naver.com'); 
INSERT INTO member VALUES(2, '홍길순', '010-2222-3333', 'koreasoon@daum.net');
COMMIT;

 

8) 생성한 테이블 내용 조회

SELECT * FROM member;

 

 

9) SQL 콘솔(리눅스) 창에서 member 테이블 데이터 조회

$ sqlplus SCOTT/tiger

SQL> SET LINESIZE 100                  // 화면 수정
SQL> SELECT * FROM member;      // member 테이블 데이터 조회

        NO NAME                 PHONE           EMAIL
---------- -------------------- --------------- --------------------------------------------------
         1 홍길동               010-1111-2222   mrhong@naver.com
         2 홍길순               010-2222-3333   koreasoon@daum.net