[오라클] 4.사용자 계정, 데이터베이스, 테이블 생성

오늘은 데이터베이스에 로그인할 수 있는 사용자 계정과 데이터베이스를 생성하는 내용을 알아보도록 하자.

데이터베이스 접속

Predefined User Accounts Provided by Oracle Database

대부분의 데이터베이스 관리 시스템(DBMS)은 설치 후 두 가지 주요 계정을 제공한다. 하나는 시스템 관리 계정(관리자 계정)이고, 다른 하나는 샘플 데이터 계정이다. 이 샘플 계정은 데이터베이스와 함께 제공되는 예제 데이터베이스 스키마에 연결된 계정이다. 오라클에서 제공하는 샘플 계정들(HR, SH 등)은 학습 및 테스트 목적으로 제공된다. 그러나 실제 운영 환경에서는 보안 등의 이유로 이러한 계정들을 사용하지 않는 것이 일반적이다.

SYSTEM

그 다음 시스템 관리 계정(관리자 계정)에는 'SYS'와 'SYSTEM' 두 가지를 대표적으로 볼 수 있다. 일반적으로 데이터베이스를 관리하는 계정으로는 SYSTEM 계정을 많이 사용한다. SYSTEM 계정은 일반적인 데이터베이스 관리 작업(사용자 생성, 권한 부여 등)을 담당하고 데이터베이스 관리를 위한 최고 권한인 SYSDBA가 필요하지 않은 작업에 주로 사용된다.

SYS

SYS 계정은 데이터베이스의 모든 것을 관리할 수 있는 최고 권한을 가진 계정이다. 이 계정은 SYSDBA 권한을 통해 데이터베이스를 시작, 중지, 복구하는 등의 작업을 수행할 수 있고 오라클 데이터베이스의 모든 시스템 테이블과 뷰는 SYS 스키마에 저장된다.

관리자 계정과 샘플 계정

데이터베이스의 물리적 구조와 논리적 구조

데이터베이스에 대해 조금 더 이해해보자. 데이터베이스는 데이터를 저장하는 시스템이다. 이 시스템은 여러 데이터를 한곳에 모아 관리한다. 그런데 이 데이터를 하나의 파일에 모두 저장할까? 아니다, 여러 파일에 분산해서 저장한다. 물리적으로는 여러 개의 파일로 저장되지만, 논리적으로는 데이터베이스라는 하나의 범주 내에 묶여 관리된다.

물리적으로는 분산되어 저장

테이블 스페이스

데이터베이스에서 데이터를 구조화하여 저장하는 기본 단위를 테이블이라고 한다. 테이블은 엔티티(Entity), 또는 개체라고도 불리며 행과 열로 구성되며, 각각의 열은 데이터의 속성을, 행은 데이터의 개별 항목을 나타낸다. 학생 테이블과 도서 대여 테이블 존재한다고 가정했을 때 학생 테이블 데이터는 A 파일에 도서 대여 테이블의 데이터는 B 파일에 이런 식으로 한 곳에 저장되는 것이 아니다.
 
DBMS는 데이터를 효율적으로 관리하기 위해 데이터를 여러 물리적 파일에 분산하여 저장한다.  이러한 수많은 테이블들이 존재하는곳이 테이블 스페이스이다. 테이블 스페이스는 데이터베이스의 물리적 저장 공간을 관리하는 논리적 구조이다.

테이블 스페이스

스키마(Schema)

또한 테이블 스페이스에는 데이터베이스에서 데이터를 빠르게 조회하기 위해 사용되는 인덱스가 존재한다. 이러한 데이터베이스 내에서 테이블, 뷰, 인덱스, 시퀀스 등 다양한 데이터베이스 객체들의 논리적 그룹을 스키마라고 한다. 즉 스키마는 특정 사용자가 소유한 객체들의 집합을 의미한다.
 
이제 사용자가 데이터베이스를 사용할 수 있도록 데이터베이스 구조를 준비하고 사용자 계정을 생성한 후, 특정 스키마에 대한 권한을 부여하여 사용자가 그 스키마 내의 객체들을 사용할 수 있게 해보자.

Schema

PDB, CDB 경로

우리는 현재 두 가지 데이터베이스 서비스를 살펴보고 있다. 하나는 PDB(Pluggable Database)이다. PDB는 SYSTEM 계정이 관리하는 데이터베이스로, 아래 그림의 빨간 박스 경로에 위치해 있다. 반면, CDB(Container Database)는 파란 박스 경로에 SYSTEM 계정이 사용하는 데이터베이스가 저장되어 있다. 우리는 PDB를 사용하여 데이터를 관리할 것이므로, PDB의 SYSTEM 계정에 접속해보자.

PDB, CDB 디렉토리 경로

테이블 스페이스 생성

우리는 테이블 스페이스를 만들고, 사용자 계정을 추가하여 이 테이블 스페이스를 사용할 수 있도록 할 것이다. 테이블 스페이스를 만들고 새로운 사용자 계정을 생성하는 작업은 데이터베이스의 기본적인 구조와 보안 설정을 변경하는 중요한 작업이므로, 일반 사용자 권한으로는 수행할 수 없으며, 반드시 DBA 권한이 있는 DBA 모드로 접근해야한다. 그러기 위해서 SQL Developer에 접속하여 상단의 '보기' 메뉴에서 'DBA'를 클릭한다.

보기 -> DBA

그러면 좌측 하단에 DBA창이 생기는데  '접속' 위에 플러스 버튼을 눌러서 우리가 이전 시간에 생성했던 '오라클 학습용 PDB1 서버'를 선택하고 '확인' 버튼 눌러서 접속한다.

DBA를 통한 접속

그러면 좌측 하단에서 '오라클 학습용 PDB1 서버'에 접속된 것을 확인할 수 있다. 이제 제일 먼저 할 작업은 데이터베이스 내에 테이블 스페이스를 생성하는 것이다. 접속된 PDB서버를 열어보면 "저장 영역"이 보이고 그 안에 '테이블 스페이스'가 존재한다. 여기에서 새로운 테이블 스페이스를 추가하기 위해 '테이블 스페이스' 항목을 오른쪽 클릭하고, '새로 만들기'를 클릭한다.

테이블 스페이스 생성

'테이블 스페이스 이름'은 NEWLEC이라는 계정을 위한 테이블 스페이스로 구분하기 위해 NEWLEC_TABLESPACE1으로 입력한다. 그리고 이 테이블 스페이스 안에는 여러 개의 파일을 가지고 테이블 스페이스를 구성할 수 있는데 여러 개의 파일로 구성하려면 아래 그림 왼쪽에 '파일 사양'에서 플러스 버튼을 통해 추가할 수 있다. 하지만 우리는 파일 하나를 가지고 테이블 스페이스를 생성할 것이다. '파일 이름'은 DATA_FILE1라는 이름으로 입력하였고 '디렉토리'에는 DATA_FILE1 파일을 저장할 경로를 넣어준다. 
 
'파일 크기'는 허용 가능한 데이터 파일의한 크기를 설정하는것인데 여기서는 500MB로 설정했다. 만약 500BM를 넘어서면 확장될 수 있도록 설정할 수 있는데 '자동 확장 설정'을 체크해서 '다음 크기'에 확장될 수 있는 크기를 설정해준다. 근데 확장 크기까지 크기가 커졌다면 '제한 없음'을 체크하여 파일 크기의 제한을 없앨 수 있고 그게 아니라면 '최대 크기'에 원하는 크기를 설정해 크기를 한정할 수 있다. 그리고 이 데이터 파일을 영구적으로 저장할 것이면 '테이블스페이스 유형'을 '영구'로 설정하면된다. 이렇게 설정을 모두 마쳤으면 '확인' 버튼을 누른다.

테이블 스페이스 설정

 
이렇게 NEWLEC_TABLESPACE 테이블 스페이스를 생성했으면 데이터베이스의 트랜잭션 관리와 데이터 무결성을 위한 로그 스페이스를 하나 더 만들어줘야한다. 테이블 스페이스 이름을 NEWLEC_LOGSPACE로 입력하고 테이블 스페이스 유형을 '임시'로 변경한다. 파일 이름은 "LOG_FILE1"로 설정하고, 디렉토리에는 파일이 저장될 경로를 설정한다. 파일 크기는 이전과 동일하게 설정하되, 로그 파일이 부족하면 트랜잭션이 중단될 수 있으므로 충분한 크기로 설정해야한다. 기본 크기는 500MB로 하고, 자동으로 500MB씩 증가하며 최대 2GB까지 설정한다. 이렇게 설정이 완료되면 임시 데이터를 위한 테이블 스페이스와 영구 데이터를 저장하기 위한 테이블 스페이스가 생성되었다.

로그스페이스 생성

사용자 계정 생성

NEWLEC 사용자를 위한 저장 공간을 만들었으니 이제 사용자를 추가해보자 '보안'안에 '사용자'를 마우스 오른쪽 버튼을 눌러 새로 만들기를 클릭한다.

사용자 생성

'사용자 이름'은 우리가 NEWLEC을 위한 테이블 스페이스를 만들었기 때문에 NEWLEC을 대문자로 입력한다. 소문자로  입력시 로그인할 때 큰 따옴표로 감싸서 로그인 해야된다. 그리고 비밀번호를 설정해주고 하단 '기본 테이블스페이스'에 NEWLEC_TABLESPACE1, '임시 테이블스페이스'에는 NEWLEC_LOGSPACE를 선택해준다.
 
그 다음 이 사용자가 사용할 수 있는 권한을 지정해줘야 되는데 '시스템 권한'을 클릭해보자

사용자 생성 설정

'시스템 권한'에서는 사용자가 테이블이나 뷰를 생성, 수정, 삭제할 수 있는 권한을 부여할 수 있다. 각 명령에 대해 적절한 권한을 부여해야 하지만 우리는 학습을 하는것이기 때문에 "모두 관리자" 옵션을 선택하여 모든 시스템 권한을 부여받게 해주자 다만 'SYSKM', 'SYSOPER', 'SYSRAC' 권한은 해제한 후, '적용' 버튼을 눌러 사용자를 추가한다.
 
SYSKM: 키 관리 작업(예: 데이터베이스 암호화 관련 작업)을 수행할 수 있는 권한
SYSOPER: 데이터베이스를 시작하거나 중지할 수 있는 권한으로, 제한된 관리자 권한
SYSRAC: Real Application Clusters(RAC) 환경에서 관련 작업을 수행할 수 있는 권한

사용자 시스템 권한

사용자 폴더 안에 NEWLEC 사용자가 잘 생성된 것을 확인할 수 있다.

NEWLEC 사용자

새로운 데이터베이스 생성 및 접속

NEWLEC이라는 사용자 계정을 추가했으니 새로운 계정을 가지고 새로운 데이터베이스에 접속해보자. 먼저 새로 만들기/데이터베이스 접속 선택에 들어가서 '사용자 이름'을  NEWLEC으로 입력한 후 '비밀번호'를 설정해주고 '롤'은 기본 값으로 변경해준다. 접속 정보는 아래 그림과 같이 입력해준다. 모두 입력하였으면 테스트 버튼을 눌러 '성공'을 확인한 뒤 '접속' 버튼을 누르면 NEWLEC 계정으로 접속할 수 있는 데이터베이스에 연결된 것이다. 이제 이 데이터베이스를 이용해서 테이블을 만들어보자.

새로운 데이터베이스 설정

SQL의 구분

우리는 데이터베이스와 해당 데이터베이스를 사용할 수 있는 사용자 계정 NEWLEC을 생성했다. 이제 SQL을 작성할 준비가 되었다. 본격적으로 테이블을 생성하기 전에 기본 SQL 명령어에 대해 알아보자.

우리가 먼저 사용할 명령어는 DDL(Data Definition Language)이다. DDL 명령어에는 CREATE, ALTER, DROP이 포함된다. 이 명령어들은 데이터베이스의 구조를 정의하고 수정하고 삭제하는 데 사용된다. 데이터 구조를 정의하는 것이 CREATE 명령어이다. 데이터 구조를 수정할 때는 ALTER를, 더 이상 필요하지 않을 때는 DROP을 사용하여 삭제한다. CREATE를 사용하여 테이블 구조를 정의하고 나면, 이후에는 데이터를 조작하는 작업을 진행할 수 있다. 예를 들어, 학생이나 부서 등의 테이블에 데이터를 삽입하거나, 수정하거나, 삭제하는 작업을 수행할 수 있다.

이러한 데이터 조작 작업에는 DML(Data Manipulation Language) 명령어가 사용된다. DML 명령어에는 INSERT, SELECT, UPDATE, DELETE가 있으며, SQL에서 가장 많이 사용되는 부분이다. 따라서 데이터 조작 명령어에 대해 잘 익숙해지는 것이 중요하다.

DCL(Data Control Language) 명령어는 데이터베이스 사용자의 권한을 관리하는 데 사용된다. 예를 들어, 새로운 사용자를 생성하고 권한을 부여할 때 GRANT 명령어를, 권한을 회수할 때는 REVOKE 명령어를 사용한다. 우리가 이전에 사용자에게 권한을 부여할 때 체크박스로 선택해 권한을 할당했듯이, 명령어를 통해서도 이러한 작업을 수행할 수 있다. 사용자에게 권한을 부여하거나 회수하는 것은 데이터베이스 관리에서 중요한 부분이다.

SQL의 구분

 

데이터를 다루기 위해서 먼저 데이터를 정의하는 작업부터 시작해 보자. 이번에 정의할 데이터는 멤버 데이터이다. 우리의 멤버 데이터는 개념화된 데이터로, 여러 속성을 가지고 있는 데이터의 집합이다. 이러한 데이터를 데이터베이스 관리 시스템(DBMS)에 알려줘야 한다.

이러한 개념화된 데이터는 데이터베이스뿐만 아니라 C++, Java 등 다양한 프로그래밍 언어에서도 사용되는 개념이다. 예를 들어, Java나 C++에서 "클래스"라는 개념을 사용하여 데이터를 정의하듯이, 데이터베이스에서는 "테이블"이라는 키워드를 사용해 데이터를 정의한다.

 

프로그래밍에서는 클래스의 속성에 대해 자료형을 앞에 명시하지만, 데이터베이스에서는 자료형을 속성 이름 뒤에 명시한다. 그리고 데이터베이스에서는 각 시스템에 맞는 고유의 데이터 타입을 사용한다. 예를 들어, String, int, Date 같은 일반적인 자료형 대신, 오라클 데이터베이스에서는 오라클 고유의 데이터 타입을 사용해야 한다. 따라서, 개념화된 데이터를 테이블로 정의하려면, 프로그래밍 언어에서 사용하던 자료형을 오라클 데이터 타입으로 바꾸고, 이를 테이블로 정의해주면 된다.

클래스와 테이블

 

테이블 생성

테이블을 생성하는 SQL 구문을 입력해 보자. 아래 그림에서 보시다시피, 여러 컬럼을 갖는 "멤버"라는 이름의 테이블을 정의하려고 한다. 데이터베이스에서는 이러한 데이터 구조를 형식이라고 부르지 않고 테이블이라고 한다. 테이블을 생성할 때 사용하는 SQL 구문은 CREATE TABLE로 시작하며, 테이블의 구조를 정의하는 컬럼 이름과 데이터 타입을 지정해야한다.

테이블 생성 명령어를 입력하고 Ctrl+Enter를 눌러 실행하면, 왼쪽의 테이블 목록에 생성된 테이블이 나타난다. 목록에 보이지 않는 경우, 아래 그림의 4번의 새로고침을 하면 확인할 수 있다.

참고자료
[1] 유튜브 채널 뉴렉처 - 수업용 사용자와 데이터베이스 생성하기