[Oracle] 계정 관리 테이블 스페이스 관리

이번 포스팅 주제는 " Simple Techniques for Oracle Database User and Tablespace Management"입니다. 유저 계정과 테이블스페이스 관리 명령어를 알아봅니다.

포스팅에서 Oracle Database 19c 설치를 다뤘는데 Database를 사용하기 위해서는 User와 Tablespace 생성이 필요합니다. Oracle Database 설치 방법에 대한 포스팅은 맨 아래에 링크가 있습니다.

 

1. DBA 계정으로 Oracle 접속하기

계정과 테이블스페이스 관리를 위해서는 sysdba로 접속이 필요합니다.

아래 명령어로 접속합니다.

sqlplus / as sysdba

 

2. Oracle 계정 및 테이블스페이스 관리

Oracle Database를 설치하면 사용자용 계정과 테이블 스페이스가 없는 상태입니다.

보통 tablespace를 먼저 생성하고 그다음 user를 생성합니다. user를 생성할 때 파라미터로 생성해 둔 tablespace를 user와 연결해 주고 tablespace 사용에 필요한 권한을 부여하는 방법을 알아보겠습니다.

예제 명령어들은 데이터 파일이 생성되는 위치를 "/oradata", SID는 "noyeora"로 가정하고 작성되었습니다.

테이블스페이스 생성 하기

tablespace 생성 문법은 아래와 같습니다. 저는 권장방법과 같이 데이터파일의 위치를 정확하게 지정 후 임시 테이블스페이스도 세트로 생성하는 것을 추천합니다. 

# 문법
create tablespace [테이블스페이스명] datafile [데이터파일명] size [사이즈];

# 예시. Oracle이 기본으로 제공하는 데이터 파일 그룹 사용
# 테이블스페이스명: TB_SAMPLE
# 데이터파일 사이즈: 1G
create tablespace tb_sample datafile '+DATA' size 1G;

# 예시. 데이터 파일 용량을 자동으로 확장되게 설정
create tablespace tb_sample datafile '+DATA' size 1G autoextend on;

# 권장방법. 데이터 파일 위치 지정하여 생성
# 임시 테이블스페이스도 생성하여 시스템 테이블스페이스 단편화 예방
create tablespace tb_sample datafile '/oradata/noyeora/tb_sample01.dbf' autoextend on;
create temporary tablespace tb_sample_tmp tempfile '/oradata/noyeora/tb_sample_tmp01.dbf' autoextend on;

데이터파일의 위치를 지정하면 관리 및 유지보수에 더 유리합니다.

임시 테이블스페이스는 데이터베이스에서 임시로 데이터를 저장하는 공간으로 정렬, 집계, 조인, 인덱스 생성 작업에 사용됩니다. 임시 테이블스페이스가 없으면 이러한 작업에 시스템 테이블스페이스를 사용하게 되는데 시스템 테이블스페이스는 데이터베이스의 중요한 정보가 저장되는 공간이기 때문에  이러한 공간을 임시 데이터 저장에 사용하면 단편화 발생 및 성능저하의 단점이 있습니다. 그러므로 운영환경이라면 임시 테이블스페이스도 같이 생성하여 사용하길 권장합니다.

제가 몸담고 있는 조직에서는 autoextend 옵션도 사용하는데 이 옵션도 권장합니다. 용량 관리는 어드민이 모니터링을 하거나 인프라 관리자가 스토리지 모니터링을 통해서 하면 되는 것이지 데이터파일에 용량을 제한 걸어 놓고 테이블스페이스 하나하나 신경 쓸 필요는 없는 것 같네요.

유저 계정 생성 하기

# 문법
create user [사용자계정명] identified by [사용자계정패스워드]
default tablespace [테이블스페이스명]
temporary tablespace [임시테이블스페이스명];

# 예시
create user noye identified by 123
default tablespace tb_sample
temporary tablespace tb_sample_tmp;

 

유저 계정 권한 속성 수정 하기

테이블스페이스를 생성하고 사용자 계정에 테이블스페이스를 지정했습니다. 이제 사용 가능할까요? 아닙니다.

아무런 권한이 없기 때문에 사용에 필요한 적절한 권한을 부여해야 합니다.

 

Oracle Database에서 권한은 크게 시스템 권한과 객체 권한이 있습니다.

시스템 권한은 데이터베이스 시스템을 관리하고 조작하는 데 필요한 권한으로 다음과 같이 구분됩니다.

• DBA 권한: 데이터베이스 시스템을 완전히 관리할 수 있는 권한입니다. 우리가 데이터베이스 관리자로 접속할 때 역할(ROLE) 선택을 "sysdba"로 선택 후 접속하는데 "sysdba"가 DBA 권한을 갖고 있습니다.
• System 권한: 데이터베이스 시스템을 부분적으로 관리할 수 있는 권한입니다.
• User 권한: 데이터베이스 사용자에게 부여되는 권한입니다.

객체 권한은 데이터베이스 객체에 대한 작업을 수행할 수 있는 권한으로 다음과 같이 구분됩니다.

• Data Manipulation Language(DML) 권한: 테이블, 인덱스, 시퀀스 등의 데이터 객체에 대한 데이터 조작을 수행할 수 있는 권한입니다.
• Data Definition Language(DDL) 권한: 테이블, 인덱스, 시퀀스 등의 데이터 객체를 생성, 수성, 삭제할 수 있는 권한입니다.
• Data Control Language(DCL) 권한: 사용자, 역할, 권한 등을 관리할 수 있는 권한입니다.
• System Privilege: 테이블, 인덱스, 시퀀스 등의 데이터 객체에 대한 데이터 조작을 수행할 수 있는 권한입니다.

User 계정에 부여할 수 있는 권한은 다음과 같습니다.

• connect: 데이터베이스에 연결할 수 있는 권한으로 이 권한이 없으면 데이터베이스에 연결할 수 없습니다.
• resource: 테이블, 인덱스, 시퀀스 등의 데이터 객체를 생성할 수 있느 권한입니다.
• unlimited tablespace: 모든 테이블스페이스에 데이터를 저장할 수 있는 권한입니다. 이 권한이 없으면 지정된 테이블스페이스에만 저장할 수 있습니다.
• create view: 뷰를 생성할 수 있는 권한입니다.
• create sequence: 시퀀스는 자동으로 증가하는 숫자를 생성하는 객체로서 시퀀스를 생성할 수 있는 권한입니다.
• create synonym: 다른 사용자의 객체에 대한 별칭을 생성할 수 있는 권한입니다.
• procedure: 프로시저는 데이터베이스 작업을 자동화하는 객체로서 프로시저를 생성할 수 있는 권한입니다.
• exp_full: exp 명령을 사용하여 데이터를 백업할 수 있는 권한입니다.
• imp_full: imp 명령을 사용하여 데이터를 복원할 수 있는 권한입니다.
• grant any: 다른 사용자에게 권한을 부여할 수 있는 권한입니다.
• revoke: 다른 사용자에게 부여된 권한을 취소할 수 있는 권한입니다.

부여할 수 있는 권한은 이외에도 더 있지만 권한을 부여할 때는 필요한 만큼만 부여해야 보안 위험을 줄일 수 있습니다.

권한을 부여하는 명령어 문법은 다음과 같습니다.

# 문법
grant 권한 [, 권한] ... to 사용자;

# 예시. 사용자 noye에 connect, resource 권한 부여
grant connect, resource to noye;

 

권한을 회수하는 명령어 문법은 다음과 같습니다.

# 문법
revoke 권한 [, 권한] ... from 사용자;

# 예시. 사용자 noye에 connect, resource 권한 회수
revoke connect, resource from noye;

 

위 예시를 모두 모아 테이블스페이스 생성, 사용자 계정생성, 계정 권한부여까지 한 번에 진행하는 예제는 아래와 같습니다.

# 테이블스페이스 생성
create tablespace tb_sample datafile '/oradata/noyeora/tb_sample01.dbf' autoextend on;
create temporary tablespace tb_sample_tmp tempfile '/oradata/noyeora/tb_sample_tmp01.dbf' autoextend on;

# 사용자 계정 생성
create user noye identified by 123
default tablespace tb_sample
temporary tablespace tb_sample_tmp;

# 사용자 계정 권한 부여
grant connect, resource to noye;
grant create database link to noye;
grant create view to noye;
grant create procedure to noye;
grant create sequence to noye;
grant create synonym to noye;
grant unlimited tablespace to noye;
grant exp_full_database to noye;
grant imp_full_database to noye;

 

계정/테이블스페이스 조회 방법

계정을 제대로 사용할 수 있는지, 어떤 권한을 가지고 있는지, 테이블 스페이스는 어떤 것들이 있는지 등을 알아보는 방법입니다.

# 사용자 계정 목록 조회
show user

# 계정-기본 테이블스페이스 조회

col username format a30
select username, default_tablespace from dba_users

# 계정 권한 확인하기
select grantee, privilege from dba_sys_privs where grantee='[계정명]';

# 로컬에서 계정 접속 확인하기
conn [사용자계정명]/[계정패스워드]

# 원격에서 계정 접속 확인하기
conn [사용자계정명]/[계정패스워드]@[SID명]

# 테이블스페이스와 데이터파일 조회

set line 120
col tablespace_name format a20
col file_name format a40
select tablespace_name, file_name, bytes/1024/1024, autoextensible from dba_data_files order by 1,2;

# 전반적인 계정 상태 체크

col username for a20
col machine for a30
col program for a30
set line 200
set pagesize 200
select inst_id,username,machine,program,count(*) from gv$session where username not in ('SYS', 'SYSTEM')
group by inst_id,username,machine,program
order by 1,2,3;

# 파라미터 링크 체크
SQL> show parameter link;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
allow_global_dblinks                 boolean     FALSE
open_links                           integer     4
open_links_per_instance              integer     4
outbound_dblink_protocols            string      ALL
standby_pdb_source_file_dblink       string

# 파라미터 오픈 체크
SQL> show parameter open;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300
open_links                           integer     4
open_links_per_instance              integer     4
read_only_open_delayed               boolean     FALSE
session_max_open_files               integer     10
SQL>

# database session 확인하기

col username for a20
col machine for a30
col program for a30
set line 200
set pagesize 200
select inst_id,username,machine,program,count(*) from gv$session
where username not in ('SYS', 'SYSTEM')
group by inst_id,username,machine,program
order by 1,2,3;

# dblink 제한 걸기
alter system set open_links=10 scope=spfile;
alter system set open_links_per_instance=10 scope=spfile;

 

반응형

계정/테이블스페이스 삭제 방법

사용자 계정 삭제는 다음 명령으로 가능합니다.

drop user [사용자계정명] [옵션];

 

테이블 삭제는 다음 명령으로 가능합니다.

drop tablespace [테이블스페이스명] including contents and datafiles;

 

사용자 계정을 삭제하면서 동시에 계정에 연결된 모든 테이블, 인덱스, 시퀀스 등의 데이터 객체를 삭제하는 방법은 "cascade" 옵션으로 가능합니다.

drop user [사용자계정명] cascade;

위 명령을 실행하면 다음과 같은 작업이 수행됩니다.

• 사용자 계정 및 사용자 암호 삭제
• 사용자 계정에 부여된 권한 취소
• 사용자 계정에 생성된 데이터 객체 삭제

테이블스페이스를 삭제하지 않으면서 "cascade" 옵션을 사용하지 않고 사용자 계정만 삭제하면 데이터 객체는 고아(orphaned) 상태로 남습니다.

 

참고로 Oracle 19c 설치 방법은 아래 링크의 포스팅을 참고해 주세요.

2023.12.06 - [Database/Oracle] - linux에 oracle 19c 설치

 

이 포스팅의 내용 정도만 숙지하시면 일반적인 범위 내에서 DB를 활용한 데이터 저장은 충분히 가능합니다. 

이상으로 Oracle Database에서 테이블스페이스와 사용자 계정을 관리하는 방법을 알아보았습니다.