[풀스택과정] 데이터베이스 14. 사용자 권한 제어

* 이 글은 제가 공부하기 위해 최대한 사실에 입각해 내용을 적으려고 하지만 일부 내용들이 정확하지 않을 수 있습니다.

   혹시나 잘못된 부분이 있으면 너그럽게 이해해 주시고 피드백 부탁드려요!

---

1. 데이터베이스 보안

데이터베이스 보안

• 다중 사용자 환경(multi-user environment)

  – 불법적인 접근 및 유출 방지를 위해 보안 대책 필요
  – 오라클에서는 사용자는 자신이 생성한 객체에 대해 소유권을 가지고 데이터에 대한 조작이나 조회 가능
  – 다른 사용자가 소유한 객체는 소유자로부터 접근 권한을 부여받지 않는 접근 불가
  – 다중 사용자 환경에서는 데이터베이스 관리자의 암호를 철저하게 관리

• 중앙 집중적인 데이터 관리

  – 분산적으로 관리되는 기존의 파일 시스템보다 보안이 취약할 수 있으므로 철저한 보안 대책 필요

 

오라클에서 지원하는 데이터베이스 보안 정책

• 시스템 보안

  – 시스템 관리 차원에서 데이터베이스 자체에 대한 접근 권한을 관리
  – 데이터베이스 관리자는 사용자 계정, 암호 관리, 사용자별 허용 가능한 디스크공간 할당
  – 예
    • 사용자의 접근 패턴을 감시하여 의심스러운 사용자 시스템 접속을 차단

    • 일정 주기로 암호를 변경하지 않는 사용자의 접속을 차단

• 데이터 보안

  − 사용자별로 객체를 조작하기 위한 동작 관리
  − 데이터베이스 객체에 대한 접근 권한을 관리
  – 예
    • scott에게 테이블, 뷰, 인덱스와 같은 객체를 생성할 수 있는 권한을 부여
    • scott가 만든 테이블을 다른 사용자가 접근할 수 없도록 접근

 

2. 권한

권한(Privilege)

• 권한이란?

  – 권한은 사용자가 데이터베이스 시스템을 관리하거나 객체를 이용할 수 있는 권리

 

• 권한의 종류

  1. 시스템 권한
    • 시스템 차원의 자원 관리나 사용자 스키마 객체 관리 등과 같은 데이터베이스 관리 작업을 할 수 있는 권한
    • 오라클에서는 테이블, 뷰, 롤백 세그먼트, 프로시저와 같은 객체를 생성, 삭제, 수정하는 작업과 관련된 140여 종류의

      시스템 권한을 지원

  2. 객체 권한

    • 테이블, 뷰, 시퀀스, 함수 등과 같은 객체를 조작할 수 있는 권한
    • 객체의 종류에 따라 서로 다른 유형의 객체 권한
    • 테이블의 칼럼별로 insert, update, references 등의 권한을 상세 부여

 

시스템 권한 – 데이터베이스 관리자

• 데이터베이스 관리자가 가지는 시스템 권한

  – 사용자 생성, 삭제, 사용자 계정에서 객체의 생성 또는 수정, 데이터베이스 백업 관리

 

<표 14-1> 데이터베이스 관리자가 가지는 시스템 권한의 예

데이터 사전을 조회해보면 무수히 많다.

 

시스템 권한 – 일반 사용자

• 일반 사용자가 가질 수 있는 시스템 권한

  – 사용자가 생성한 객체를 관리, 내장 프로시저를 관리

<표 14-2> 일반 사용자가 가지는 시스템 권한의 예

시스템 권한 부여

• 시스템 권한 부여

  – 시스템 권한은 특정 사용자나 모든 사용자에게 부여 가능
  – 롤에도 시스템 권한을 부여 가능
  – GRANT 명령문을 사용

 

☞ 사용법

• PUBLIC : 모든 사용자에게 해당 시스템 권한 부여
• WITH ADMIN OPTION : 해당 시스템 권한을 다른 사용자나 롤에 재부여 허용

 

☞ 사용 예

  – query rewrite 시스템 권한을 scott사용자와 모든 사용자에게 부여하여라. 
     query rewrite 권한은 함수 기반 인덱스를 생성하기 위해 필요한 권한이다.

현재 세션에 부여된 시스템 권한 조회

• SESSION_PRIVS

  – 현재 세션에서 사용자와 롤에 부여된 시스템 권한을 조회 가능

☞ 사 용 예

시스템 권한 철회

• 시스템 권한 철회

  – 데이터베이스 관리자나 권한을 부여한 사용자는 다른 사용자에게 부여한 시스템 권한을 철회 가능
  – REVOKE 명령문 사용

☞ 사 용 법

☞ 사 용 예

scott 사용자에게 부여한 query rewrite 시스템 권한을 철회하여라

 

객체 권한

• 객체 권한

  – 객체 권한은 테이블, 뷰 , 시퀀스, 함수 등과 같이 객체별로 사용할 수 있는 권한

<표 14-3> 객체 권한의 종류

객체 권한 부여

• 객체 권한 부여

  – 데이터베이스 관리자나 객체 소유자가 사용자와 롤에게 부여
  – GRANT 명령문을 사용해 권한 부여

 

☞ 사용법

• ALL : 모든 객체 권한을 해당 사용자에게 부여
• PUBLIC : 모든 사용자에게 해당 객체 권한 부여
• WITH ADMIN OPTION : 해당 시스템 권한을 다른 사용자나 롤에 재부여 허용

 

객체 권한 부여 예 – select 권한

☞ 사 용 예

Tiger 사용자에게 scott 소유의 테이블을 SELECT 할 수 있는 권한을 부여하라

객체 권한 생성 방법 - update 권한

☞ 사 용 예

Tiger 사용자에게 scott 가 소유한 student 테이블의 키와 몸무게를 수정할 수 있는 권한을 부여하여라

 

객체 권한 조회 1

☞ 사 용 예

tiger 사용자에게 부여된 사용자 객체, 칼럼에 부여된 객체 권한을 조회하여라

객체 권한 조회 2

객체 권한의 철회

• 객체 권한의 철회

  – REVOKE 명령문을 사용하여 철회

☞ 사 용 법

• CASCADE CONSTRAINTS : REFERENCES나 ALL권한 철회 시 관련 참조 무결성 제약조건도 함께 삭제

 

☞ 사 용 예

scott에 의해 tiger에게 부여된 student 테이블에 대한 SELECT, UPDATE 권한을 철회하라

 

3. 롤

롤(role)

• 롤 개념

  – 다수 사용자와 다양한 권한을 효과적으로 관리하기 위하여 서로 관련된 권한을 그룹화한 개념
  – 일반 사용자가 데이터베이스를 이용하기 위한 공통적인 권한(데이터베이스 접속권한, 테이블 생성, 수정, 삭제,

      조회 권한, 뷰 생성 권한)을 그룹화
  – 이러한 권한을 그룹화하여 롤로 정의해서 사용자에게 롤에 대한 접근 권한을 부여하면 권한 관리를 효과적으로 함

• 롤 부여

  – 활성화 또는 비활성화를 통한 일시적 권한 부여 철회 가능
  – 암호 부여 가능
  – 사용자 또는 다른 롤에 대한 접근 권한 부여 및 철회 가능
  – 자식에 대한 롤 부여나 순환적인 부여는 불가능
  – 롤은 특정 소유자나 특정 객체에 속하지 않음

 

사용자, 롤, 권한 간의 관계

롤 종류

• 사전에 정의된 롤

  – 사용자 접속, 자원 생성, DBA
  – 사전에 정의된 롤도 부여 철회 가능

• 사용자 정의 롤

<표 14-4> 사전 정의된 롤의 종류

사전에 정의된 롤

• CONNECT 롤

  – 사용자가 데이터베이스에 접속하여 세션을 생성할 수 있는 권한
  – 테이블 또는 뷰와 같은 객체를 생성할 수 있는 권한

• RESOURCE 롤

  – 사용자에게 자신의 테이블, 시퀀스, 프로시져, 트리거 객체 생성할 수 있는 권한
  – 사용자 생성 시 : CONNECT 롤과 RESOURCE 롤을 부여

• DBA 롤

  – 시스템 자원의 무제한적인 사용이나 시스템 관리에 필요한 모든 권한
  – DBA 권한을 다른 사람에게 부여할 수 있음
  – 모든 사용자 소유의 CONNECT, RESOURCE, DBA 권한을 포함한 모든 권한을 부여 및 철회 가능

 

롤 생성

• 롤 생성

  – CREATE ROLE 명령문으로 생성
  – 롤에 암호 부여 가능
  – 롤 이름은 사용자나 다른 롤과 중복될 수 없음

☞ 사 용 법

• NOT IDENTIFIED : 롤 활성화 시 암호에 의한 검증 과정 생략
• IDENTIFIED : 롤 활성화 시 암호에 의한 검증 과정 필요
• BY password : 롤 활성화 시 사용되는 암호 지정
• EXTERNALLY : 롤 활성화 시 운영체제 인증을 통한 사용자 검증

 

☞ 사 용 예

암호를 지정한 롤과 지정하지 않은 롤을 생성하여라

롤에 대한 부여

• 롤에 권한 또는 롤 부여

  – 롤에 시스템 권한이나 객체 권한 또는 다른 롤을 부여 가능
  – GRANT 명령문 사용

☞ 사 용 법

• PUBLIC : 모든 사용자에게 해당 롤 부여
• WITH ADMIN OPTION : 다른 사용자나 롤에 해당롤을 재부여할 수 있는 옵션

 

롤에 권한 부여

• 롤에 시스템 권한 부여

  – DBA 또는 GRANT ANY PRIVILEGE 권한을 가진 사용자는 롤에 시스템 권한을 부여 가능

 

☞ 사용예

hr_mgr 롤에 CREATE SESSION 시스템 권한을 부여하여라

 

• 롤에 객체 권한 부여

  – 사용자가 롤에 객체를 부여할 수 있는 경우

    • 사용자가 객체의 소유자인 경우

    • WITH GRANT OPTION 옵션과 함께 객체 권한을 부여받은 경우

☞ 사용예

soctt 사용자의 student 테이블의 모든 칼럼에 대한 select, insert, delete 객체 권한을 hr_clerk 롤에 부여하여라.

롤 부여

• 롤 부여

  – 롤은 사용자 또는 다른 롤에게 롤 부여
  – WITH ADMIN OPTION 

    • WITH ADMIN OPTION을 부여받은 롤은 사용자나 다른 롤에게 해당 롤을 재부여 가능

☞ 사용 예

hr_clerk 롤을 hr_mgr롤과 tirger 사용자에게 부여하라

롤 조회

• role_sys_privs

  – 롤에 부여한 시스템 권한 조회

☞ 사 용 예

데이터 딕셔너리를 이용하여 롤 정의를 조회하여라

• role_tab_privs, user_role_privs

  – 롤에 부여한 시스템 권한 조회

 

4. 동의어

동의어(synonym)

• 동의어의 개념

  – 데이터베이스 객체의 소유권은 해당 객체를 생성한 사용자가 가지므로 사용자가 소유한 객체에 접근하기 위해서는

    소유자로부터 접근 권한을 부여받아야 함
  – 다른 사용자가 소유한 객체를 조회할 때에는 소유자의 아이디를 객체 이름 앞에 첨부해야 함

    • 예를 들어, 학생 테이블의 소유자가 scott이고, 홍길동이 scott로부터 접근 권한을 부여받아 학생 테이블을

      조회하려면 scott.student 식으로 소유자 아이디를 테이블 이름 앞에 지정
  – 객체를 조회할 때마다 객체의 소유자를 일일이 지정하는 방법은 매우 번거로움
  – 동의어는 하나의 객체에 대해 다른 이름을 정의하는 방법

• 동의어와 별명(Alias) 차이점

  – 동의어는 데이터베이스 전체에서 사용
  – 별명은 해당 SQL 명령문에서만 사용

 

동의어의 종류

• 전용 동의어(private synonym) 

  – 객체에 대한 접근 권한을 부여받은 사용자가 정의한 동의어로 해당 사용자만 사용

• 공용 동의어(public sysnonym)

  – 권한을 주는 사용자가 정의한 동의어로 누구나 사용
  – DBA 권한을 가진 사용자만 생성 (예 : 데이터 딕셔너리)

☞ 사 용 법

• PUBLIC : 모든 사용자가 사용가능
• 기본 값은 전용 동의어

 

전용 동의어(private synonym) 생성 예

☞ 사 용 예

SYSTEM사용자 소유의 project 테이블에 my_project 전용 동의어를 생성하여라

동의어 생성 방법

공용 동의어 생성 예

☞ 사 용 예

System 사용자의 project 테이블에 대해 공용 동의어를 생성하여라

 

동의어 삭제

• 동의어 삭제

  – DROP SYNONYM 명령문 사용

☞ 사 용 법

☞ 사 용 예

my_project와 pub_project 동의어를 삭제하여라

권한부여 실무사례

• ROLE 생성 현황

• 운영자에게 부여된 권한(1인 1 계정) 

  - CONNECT ROLE
  - RESOURCE ROLE
  - SECURE_EXEC_ROLE : SELECT_ROLE + EXEC_ROLE
  - SELECT_CUST_ROLE : 고객정보 담당자에게만 부여(각파트 1명)
  - DML작업 시 : DML작업전용 DB 계정(DMLUSER)을 부여받아 사용

    • 테이블/뷰/시퀀스 생성 시 SELECT_ROLE을 부여

    • 테이블 생성 시 DML_ROLE을 부여
    • 프로시져/시퀀스/패키지 생성 시 EXEC_ROLE을 부여