계정과 스키마

계정과 스키마

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

 

데이터 사전

• 오라클의 사용자 정보
• 오라클 권한과 롤 정보

오브젝트 : 테이블, 뷰, 인덱스 등 모든 논리적인 저장구조

모든 오브젝트에는 소유자가 있고, 오브젝트가 해당 계정의 스키마에 위치한다.

 

스키마

스키마란, 계정이 소유하는 테이블이나 인덱스 등 오브젝트를 포함하는 논리적인 컨테이너다.

다른 스키마 안에 있는 오브젝트는 이름이 같아도 다른 오브젝트로 취급한다.

 

ex)

SCOTT 계정 ↔ SCOTT 스키마(EMP 테이블/ JOBS 테이블)

HR 계정 ↔ HR 스키마(EMP 테이블/ JOBS 테이블)

 

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

 

계정

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

 

오라클 계정 속성

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

계정을 생성할 때 위 2개의 테이블 스페이스를 지정하지 않으면 데이터베이스에 설정된 기본 테이블스페이스가 지정된다.(SYSTEM 테이블 스페이스) 하지만 SYSTEM 테이블스페이스는 데이터베이스가 동작하는데 필수 관리 정보가 저장되는 특수한 테이블 스페이스이므로 계정의 오브젝트나 임시 세그먼트를 저장하는 것은 바람직하지 않다.

 

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

 

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

 

• 계정에 지정된 DEFAULT 테이블 스페이스 확인 : DBA_USER 뷰 > DEFAULT_TABLESPACE 칼럼
• 계정에 지정된 DEFAULT 임시 테이블 스페이스 확인 : DBA_USER 뷰 > 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;

계정 상태 종류

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

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 -- 자원 프로파일의 제한 값(DEFUALT일 때 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 권한과 SYSOPER 권한 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