Chapter 11. 데이터베이스 주요 기능 (4): Grant, Revoke

MoonLight·2021년 12월 15일
0

데이터베이스

목록 보기
12/12

개요

사용자는 데이터베이스 연산을 하려면 연산에 필요한 권한을 가지고 있어야 하며, 만약 권한이 없는 연산은 시스템에 의하여 수행이 거부된다.

DBA는 모든 권한을 가지고 있으며, 특정 사용자에게 특정 권한을 부여할 수 있다.

권한

데이터베이스 시스템에 관련이 있는 권한은 여러 종류가 있다.

데이터베이스 인스턴스에 대한 권한으로는 읽기 권한, 입력 권한, 갱신 권한, 삭제 권한이 있으며,

데이터베이스 스키마에 대한 권한으로는 색인 생성/삭제 권한, 테이블 생성 권한, 테이블 속성 변경 권한, 테이블 삭제 권한이 있다.

SQL이 지원하는 권한

SQL 언어가 지원하는 권한들이다.

  • select/insert/update/delete

  • references는 외래키를 선언할 수 있는 권한

    • 외래키 선언은 단순히 해당 테이블에만 영향을 미치지 않고, 참조되는 테이블의 주 키(primary key) 값의 생성/삭제/변경에 영향을 미치기 때문에 이에 대한 권한 설정이 필요하다. 외래키가 참조하는 테이블의 주 키 값은 입력/삭제/변경시에 참조무결성에 의한 여러 제약이 존재한다.

  • usage는 도메인을 사용할 수 있는 권한

  • all privileges는 모든 권한 부여

Grant 문장

Grant 문장은 권한을 부여하는 기능이다. 구문은 다음과 같다:

Grant <privilege> on <테이블명> to <user list> [with grant option]

  • <user list>는 사용자 아이디의 나열이나 role(후에 나옴), public을 사용할 수 있다.

    • public은 키워드로서 모든 사용자를 의미한다.

  • Grant 문장에 [with grant option]을 사용하면, 권한을 받는 사용자가 부여 받은 권한을 다른 사용자에게 부여할 수 있다.(내가 받은걸 딴 애들한테도 물려줄 수 있다.)

Grant 문장 예제

Grant select on professor to U1, U2, U3;
  • 사용자 U1, U2, U3에게 professor 테이블에 대한 select 문장을 사용할 수 있는 권한을 부여한다. 
Grant select on professor to U4 with grant option;
  • 사용자 U4에게 professor 테이블에 대한 select 문장을 사용할 수 있는 권한을 부여하고 또한 U4에게 받은 권한을 다른 사용자에게 부여할 수 있는 권한을 함께 부여한다.
Grant references (deptName) on department to Lee;
  • Lee 사용자에게 department 테이블의 deptName 속성을 참조하는 외래키를 생성하는 권한을 부여한다.
  • 즉, Lee 사용자는 본인 소유의 테이블에서 department.deptName을 참조할 수 있다. 이러한 권한이 필요한 이유는 참조무결성 제약이 형성되면 department.deptName 속성값 변경에 제한을 줄 수 있기 때문이다.

Revoke 문장

Revoke 문장은 부여한 권한을 철회(회수)하는 기능이다. 구문은 다음과 같다:

Revoke <privilege> on <테이블명> from <user list> [restrict | cascade];
  • ex) Revoke select on professor from U1, U2, U3;

1) 만약에 사용자 U1이 U2에게 특정 권한을 주었고, 또 U2가 동일 권한을 사용자 U3에게 부여했을 때, 이 경우 사용자 U1이 U2에게 권한 취소를 한다면 어떻게 될까?

cascade 옵션은 권한 취소 시에 취소되는 권한으로 인하여 함께 취소가 되어야 하는 권한이 있으면 그 권한도 함께 취소하는 것이며,

  • 즉 U1이 U2에게 권한취소 명령을 내렸을 때, 거기에 딸린 놈 U3의 권한도 철회된다.
  • default

restrict 옵션은 취소하려는 권한으로 인하여 다른 권한이 함께 취소되어야 하는 경우에는 권한 취소 연산 자체가 수행되지 않는다.

restrict 옵션은 사용자가 본의 아니게 취소하는 권한을 방지하는 기능을 제공한다. 사용자가 restrict 옵션을 사용하여 revoke 문장을 실행하면, 사용자가 인지하지 못했던 권한에 대한 취소를 방지할 수 있다.

구문은 다음과 같다:

Revoke select on professor from U1, U2, U3 cascade;
  • U1, U2, U3는 물론이고 U1,U2,U3가 싸질러놓은 권한까지도 모두 철회하시오
Revoke select on professor from U1, U2, U3 restrict;
  • U1, U2, U3가 아무것도 싸질러놓지 않은 경우에만 얘들 권한을 모두 철회하시오.

2) 만약에 사용자 U1이 U2에게 특정권한을 주었는데, U3가 U2에게 동일권한을 부여하였다면 사용자 U1이 Revoke select on professor from U2; 라는 구문을 입력했을 때 U2의 select권한은 없어질까?

  • NO💢 사용자 U3도 Revoke select on professor from U2; 를 입력해야 U2는 professor에대한 select권한이 완전히 없어지게 된다.

3) 권한 취소를 받는 사용자가 public이면 모든 사용자에게서 권한을 취소하는 것이다. 그러나 만약 다른 사용자로부터 동일 권한을 이미 받았으면 그 권한까지 취소되지 않는다.

  • u1 > grant select on professor to public; // 모든사용자에게 권한부여
  • u1 > grant select on professor to U2; // U2에게만 권한부여
  • u3 > grant select on professor to U2; // U2에게만 권한부여
  • u1 > revoke select on professor from public; // 모든 사용자 권한 철회
  • 하지만 U2는 professor에 대한 select권한이 남아있음(By u3)

“grant option" 권한도 취소가 가능하다.

  • Admin > Revoke grant option for select on professor from U5;
    • U5로부터 grant option for select (select 물려주기권한)을 철회

권한 그래프

DBS는 임의의 사용자로부터 임의의 다른 사용자로 누가 권한을 부여하고 누가 권한을 부여받았는지에 대한 도식화를 사용한다.

  • 이를 Authorization Graph라고 한다.

Node는 사용자를 나타내고, 그래프의 root는 DBA이어야 하며, Edge는 권한 부여를 나타낸다.

모든 권한은 DBA로부터 나오게 되므로, 그래프의 모든 Edge는 DBA 로부터 접근이 가능하여야 한다 (즉, 길(path)이 있어야 한다).

권한 그래프 예제 (1)

상기는 professor 테이블 갱신에 대한 권한 그래프이다.

만약 DBA가 U2의 권한을 취소하면, U2가 U3, U4에게 부여한 권한이 함께 취소되어야 한다.

  • 왜냐하면, 권한 그래프 관점에서는 U2에서 U3로 가는 에지와 U2에서 U4로 가는 에지가 DBA 노드에서 접근이 불가능하므로 두 개 에지는 제거되어야 한다.

권한 그래프 예제 (2)

이 권한 그래프를 만드는 구문은 다음과 같다.

  • DBA > Grant select on professor to U7 with grant option;
  • U7 > Grant select on professor to U8 with grant option;
  • U8 > Grant select on professor to U7;

상기의 그래프에서 "DBA → U7" 에지가 취소되면, "U7 → U8" 및 "U8 → U7" 에지도 함께 취소되어야 한다. 그렇게 하기 위해서는 다음과 같이 구문을 작성하여야 한다.

  • DBA > Revoke select on professor from U7 cascade;

참고적으로 아래와 같이 restricted 옵션으로 권한 취소 연산을 수행하면 실행 오류가 발생한다.

DBA > Revoke select on professor from U7 restricted;


뷰 권한

뷰는 일반 테이블과 마찬가지로 권한 부여 대상이어서 뷰에 대한 검색/삭제/삽입/갱신 권한 등이 존재한다. 그러나 뷰에 대한 권한은 일반 테이블 권한과 다르게 적용되므로 주의가 필요하다.

베이스 테이블에 대한 최소한 SELECT 권한이 있어야 뷰 생성이 가능하며, 생성된 뷰에 대해서도 베이스 테이블에 대한 권한을 능가하는 권한을 가질 수 없다.

  • 예를들어, U1이 베이스 테이블에 대해 select권한만을 가지고 있고, 이 권한으로 view1을 만들었을 때 U1은 view1에 대해서도 select권한밖에 가지지 못한다.
  • 즉, view를 생성한 사용자가 base table에 대해 어떤 권한을 가지느냐에 종속된다.

뷰 생성자는 resource 권한이 필요 없다. 일반 테이블을 생성하는 것이 아니기 때문이다.

일반 테이블 생성자는 그 테이블에 대한 모든 권한을 가지는 반면에,

뷰 생성자는 뷰에 대한 모든 권한을 가지지 못한다.

베이스 테이블에 대한 select 권한이 있어야 뷰 생성이 가능하므로 뷰 생성자는 뷰에 대한 select 권한은 가지게 되나, 그 이상의 권한은 베이스 테이블에 대한 권한에 의존적이다.

  • 예를 들어, 본인이 생성한 뷰에 대해서도 갱신 권한을 가지지 못할 수 있는데, 이유는 뷰를 통한 갱신은 실제로는 베이스 테이블 갱신이기 때문이다.

다만 사용자가 베이스 테이블에 갱신 권한을 이미 가지고 있으면 뷰에 대한 갱신 권한도 당연히 가진다.

뷰 권한 예제

  • 조교가 교수이름과, 그 교수가 2015년도 가을학기에 가르친 강의명을 알아야한다고 가정하자.
  • 또한, 봉급을 볼 권한은 없다고 가정하자.
  • 위 조건을 만족하려면 뷰를 어떻게 생성해야 할까???
Create view myTeach as
select 	name, title
from 	professor, teaches, course
where teaches.pID=professor.pID and course.cID=teaches.cID
and semester=‘Fall’ and year=2015;
/* 위 구문은 professor, teaches, course에 대해 select 권한만을 가지고 있는
조교가 아닌 어떤 사용자가 만든 구문이다. */

Solution: 교수가 2015년 가을 학기에 강의하는 과목은 접근을 하고 교수 봉급을 접근하지 못하게 하려면, 상기와 같이 myTeach 뷰를 생성하여 조교에게 myTeach뷰에 대한 읽기 권한을 부여해 주면 된다.

  • professor 테이블에 대한 권한은 부여하지 않는다.

조교가 myTeach뷰를 보려고(select) 할 때 조교는 professor테이블에 대한 select권한이 없으므로 view에 대한 select도 불가능하지 않을까?? 왜냐면 view는 Base table에 기반해서 만들어졌으니까,,,

  • NO! view에 대한 어떠한 권한도 view 그 자체의 권한을 가지느냐 안가지느냐에 따라서지, Base table에 대한 권한과는 관련없다.

조교는 다음 쿼리에 대한 결과를 볼 권한이 있다 ㅎㅎ

  • select * from myTeach;

데이터베이스 시스템은 상기 뷰에 대한 질의가 들어오면 뷰 정의를 이용하여 뷰를 확장하게 되고, 이 경우 확장된 뷰는 베이스 테이블 professor, course, teaches를 가진다. 사용자는 베이스 테이블에 대한 권한이 전혀 없으므로, 뷰에 대한 접근 권한 검사는 뷰가 확장되기 전에 뷰에 대하여 수행하여야 한다.

■ 롤

롤은 사용자의 집합이다. 사용자 다수에게 동일한 권한을 부여하는 경우에, 다수 사용자를 동일한 롤로 정의한 후에, 롤에 권한을 부여하면, 롤에 속하는 모든 사용자에게 권한이 부여된다.

롤을 다른 롤에게도 부여할 수 있다. 이 기능을 이용하면, 사용자를 계층적으로 관리하는 것이 가능하다. 롤은 사용자 관리에 아주 유효한 기능이다.

■ 롤 예제

롤 예제이며, teller 롤을 manager 롤에게 부여하는 것은 teller가 가진 모든 권한을 manager에게 부여하는 것이다. 그 결과 manager 권한은 teller 권한을 다 가지게 된다. teller와 manager에게 궁극적으로 사용자를 부여함으로써 사용자를 계층적으로 관리하는 효과이다.

■ SQL 권한 관리의 제약

SQL 데이타베이스 시스템에서 터플 수준에서의 권한 관리는 불가능하다. 다만 갱신 연산인 경우 속성에 대한 권한 관리는 가능하다. 그 결과, 이름, 성적 등이 기록되어 있는 학생 테이블에 대하여 특정 학생이 본인 성적만을 접근하게 하는 기능을 DBMS가 제공하지 않는다.

데이터베이스 응용은 웹 환경에서 개발이 주로 이루어진다. 이 경우, 데이터베이스 시스템을 접근하는 응용 프로그램이 가지는 데이터베이스 시스템 총 사용자 아이디가 하나 일 수도 있다. 이 경우에는 데이터베이스 시스템 권한 관리를 사용하지 않고 응용 프로그램에서 사용자 관리 및 권한 관리를 수행하게 된다. 응용 프로그램에서는 여러 형태의 데이터 접근 제어가 가능하므로, 예를 들면 특정 학생이 본인 성적만을 접근하게 하는 기능 구현이 가능하다. 데이터 접근 제어를 응용 프로그램에서 구현하는 방식의 장단점은 상기와 같다.

profile
MoonLight
hello world :)
이전 포스트

Chapter 10. 데이터베이스 주요 기능 (3): Trigger

0개의 댓글