오라클 아키텍처의 계정과 스키마

계정과 스키마

오라클은 계정의 ‘인증 정보’와 ‘권한 정보’를 독자적으로 관리한다. 오라클은 이용할 수 있는 계정명과 패스워드 목록을 ‘데이터 딕셔너리’에 저장한다.

 

• 데이터 사전

오라클의 사용자 정보
오라클 권한과 롤 정보
계정은 ‘오브젝트 소유자의 역할’ 이다.

 

• 오브젝트

테이블, 뷰, 인덱스 등등 등 모든 논리적인 저장구조
모든 오브젝트에는 소유자가 있고, 오브젝트가 해당 계정의 스키마에 위치한다.

 

 

스키마란 무엇인가 ?

스키마란, 계정이 소유하는 테이블이나 인덱스 등의 오브젝트를 포함하는 논리적인 컨테이너이다. 다른 스키마 안에 있는 오브젝트는 이름이 같아도 다른 오브젝트로 취급한다.

 

• SCOTT 계정

SCOTT 계정 <-> SCOTT 스키마 (EMP 테이블 / JOBS 테이블)

 

• HR 계정

HR 계정 <-> HR 스키마 (EMP 테이블 / JOBS 테이블)

 

SCOTT 스키마의 EMP 테이블과 JOBS 테이블은 HR 스키마의 EMP 테이블과 JOBS 테이블이 같지않다. 다른 계정이 갖고있는 오브젝트를 사용하기 위해서는, ‘SCOTT.EMP’ 또는 ‘HR.EMP’ 처럼 스키마명을 지정해야한다. 스키마는 오라클에 접속할때 지정한 계정명과 같다.

 

 

계정

계정 정보는 ‘데이터 딕셔너리’ 에 저장되므로, 일반 데이터를 백업하는 것처럼 백업할 수 없다. 계정을 생성할때 사용했던 DDL문을 백업해두거나 DDL문을 추출해야한다.

 

• DDL

TABLE CREATE, ALTER 등등

 

오라클의 계정은 여러가지 속성들이 있다.

DEFAULT 테이블 스페이스	오브젝트를 생성할때 저장할 테이블 스페이스를 지정하지 않았을때 저장되는 테이블 스페이스이다. 계정 생성시, DEFAULT TABLESPACE 절로 지정한다.
DEFAULT 임시 테이블 스페이스	SQL 실행시 임시 세그먼트가 필요할때 임시 세그먼트를 확보하기 위한 테이블 스페이스이다. 계정 생성시, TEMPORARY TABLESPACE 절로 지장한다.

계정을 생성할때 위 2개의 테이블 스페이스를 지정하지않으면 데이터베이스에 설정된 기본 테이블 스페이스가 지정된다.

 

 

스페이스/세그먼트

• 테이블 스페이스

데이터 파일을 그룹화한 논리적인 저장 공간

SYSTEM 테이블 스페이스가 대신 지정된다. 하지만 SYSTEM 테이블 스페이스는 데이터베이스가 동작하는데 필요한 필수 관리 정보가 저장되는 특수한 테이블 스페이스므로 계정의 오브젝트나 임시 세그먼트를 저장하는 것은 바람직하지 않다.

 

• 임시 세그먼트

처리에 필요한 일시적인 작업 영역을 메모리 상에 확보할 수 없을때 할당되는 작업용 디스크 공간

따라서 계정을 생성할때는 각각의 테이블 스페이스에 SYSTEM 테이블 스페이스 이외의 테이블 스페이스를 명시적으로 지정해야한다.

 

 

스페이스 명령어

• 계정에 지정된 DEFAULT 테이블 스페이스 확인

DBA_USERS 뷰 > DEFUAULT_TABLESPACE 칼럼

 

• 계정에 지정된 DEFAULT 임시 테이블 스페이스 확인

DBA_USERS 뷰 > TEMPORARY_TABLESPACE 칼럼

 

 

개념정리

권한	특정한 조작을 수행할 수 있는 권리
QUOTA	테이블 스페이스의 할당 제한 DBA_TS_QUOTAS 뷰에서 확인할 수 있다.
계정 프로파일	계정이 사용할 수 있는 자원을 제한하거나 패스워드의 관리 정책을 규정
자원 프로파일	계정이 사용할 수 있는 '각종 자원에 제한을 거는 방식'과 '패스워드의 관리 정책을 규정' 할 수 있는 두가지로 구분된다. 자원을 제한하기 위해서는 초기화 파라미터 RESOURCE_LIMIT = 'TURE' 여야 한다.

 

 

계정확인

계정명, 상태, 기본 테이블 스페이스와 같은 오라클 계정에 관련된 정보는 DBA_USERS 뷰에서 확인할 수 있다.

SELECT USERNAME, -- 계정명
       ACCOUNT_STATUS ACCOUNT, -- 계정 상태
       DEFAULT_TABLESPACE DEFAULT_TS, -- 계정의 DEFAULT 테이블 스페이스명
       TEMPORARY_TABLESPACE TMPE_TS, -- 계정의 DEFAULT 임시 테이블 스페이스명
       PROFILE -- 계정에 설정된 계정 프로파일명
FROM DBA_USERS;

위 계정 상태의 종류에는 4가지가 있다.

값	내용
OPEN	잠겨있지않으며 로그인이 가능한 상태
EXPIRED	패스워드의 사용기한이 끝난 상태 (로그인 후 변경)
LOCKED	계정이 잠겨있으며, 로그인이 불가능한 상태
EXPIRED & LOCKED	패스워드의 사용 기한이 끝나 잠긴상태

 

 

QUOTA 확인

QUOTA 정보를 확인해보자.

SELECT tablespace_name, -- 테이블 스페이스명
       user_name, -- QUOTA가 할당된 계정명
       max_bytes -- QUOTA의 크기 (무제한일때: -1)
FROM DBA_TS_QUOTAS
WHERE username = '<계정명>';

 

 

계정 프로파일 확인

데이터베이스 전체의 계정 프로파일 정보를 확인해보자.

SELECT profile, -- 계정 프로파일명
       resource_name, -- 자원 프로파일명
       resource_type, -- 자원을 제한: 'KERNEL' / 패스워드 운용 정책: 'PASSWORD'
       limit -- 자원 프로파일의 제한 값 (DEFULAT일때 DEFAULT 프로파일의 같은 이름을 가진 자원 프로파일의 제한 값을 사용)
FROM DBA_PROFILES;

 

 

 

오브젝트 권한

특정 오브젝트에 관한 권한을 말한다.

 

• 모든 오브젝트의 권한 확인

SELECT grantee, -- 오브젝트 권한이 부여된 계정명 또는 롤명
       owner, 
       table_name, -- 오브젝트명
       grantor, -- 오브젝트 권한을 부여한 계정명
       privilege, -- 오브젝트 권한명
       grantable -- 권한이 WITH GRANT 옵션과 함께 부여되면 'YES', 아니면 'NO'
FROM DBA_TAB_PRIVS;

 

• 계정의 오브젝트 권한 확인

SELECT grantee, -- 오브젝트 권한이 부여된 계정명 또는 롤명
       owner, 
       table_name, -- 오브젝트명
       grantor, -- 오브젝트 권한을 부여한 계정명
       privilege, -- 오브젝트 권한명
       grantable -- 권한이 WITH GRANT 옵션과 함께 부여되면 'YES', 아니면 'NO'
FROM USER_TAB_PRIVS;

 

 

시스템 권한

시스템에 대한 권한이다.

 

• 모든 시스템 권한 확인

SELECT grantee, -- 시스템 권한이 부여된 계정명
       privilege, -- 시스템 권한명
       admin_option -- 권한이 WITH GRANT 옵션과 함께 부여되면 'YES', 아니면 'NO'
FROM DBA_SYS_PRIVS;

 

• 계정에 직접 할당되어있는 시스템 권한 확인

SELECT username, -- 계정명
       privilege, -- 시스템 권한명
       admin_option -- 권한이 WITH GRANT 옵션과 함께 부여되면 'YES', 아니면 'NO'
FROM USER_SYS_PRIVS;

 

• 계정에 할당된 모든 시스템 권한 확인

SELECT privilege -- 시스템 권한명
FROM SESSION_PRIVS;

 

 

특수한 시스템 권한

‘SYSDBA 권한’ 과 ‘SYSTPER 권한’ 2가지 특별한 시스템 권한이 있다. 오라클에는 기본적으로 SYSDBA 권한을 가진 SYS 계정이 존재한다. 따라서 별도의 절차없이 SYS 계정으로 접속하여 SYSDBA 권한이 필요한 인스턴스의 기동/정지 등의 작업을 수행할 수 있다.

	SYSDBA 권한	SYSOPER 권한
STARTUP / SHUTDOWN	O	O
ALTER DATABASE OPEN/MOUNT/BACKUP	O	O
ALTER DATABASE CHARACTER SET	O	X
CREATE/DROP DATABASE	O	X
CREATE SPFILE	O	O
ALTER DATABASE ARCHIBELOG	O	O
완전 복구를 수행하기 위한 ALTER DATABASE RECOVER	O	O
불완전 복구를 수행하기 위한 ALTER DATABASE RECOVER	O	X
RESTICE 모드로 기동한 인스턴스에 접속 가능 여부 (RESTRICTED SESSION 권한 포함)	O	O