관계형 데이터베이스: 권한(Authorization)

DBMS는 사용자가 작성한 query를 수행할 수 있는지 체크한다. 수행할 수 있는 권한이 없다면 그 query는 거부된다. 권한은 query에 포함되어 있는 relation이나 view에 대해서 사용자의 권한이 없음을 의미한다. 권한이 없다면 처리하면 안된다. DBA는 모든 권한을 가지고 있어 다른 사용자에게 권한을 부여/회수할 수 있다.

 

데이터베이스 시스템과 관련있는 권한으로는

• Read authorization
• Insert authorization
• Update authorization
• Delete authorization

이 있다. 데이터베이스 스키마에 대한 권한으로는

• Index authorization: 스키마가 있을 때 인덱스를 생성/삭제할 수 있는 권한
• Resource authorization: 새로운 relation을 생성할 수 있는 권한
• Alteration authorization: relation의 schema를 변경하고, column을 추가/삭제할 수 있는 권한
• Drop authorization: relation을 삭제할 수 있는 권한

SQL 에서는 어떠한 권한이 존재하는지 살펴보자.

• select: relation에 접근해 데이터를 읽어올수 있는 권한이다. 뷰도 마찬가지
• insert: 튜플을 삽입할 수 있는 권한
• update: 튜플을 업데이트할 수 있는 권한
• delete: 튜플을 삭제할 수 있는 권한
• references: foreign key와 관련있는 권한이다. FK를 지정하게 되면, cascade, set null, set default와 같은 옵션을 붙일 수 있는데, 이로인해 기존에 존재하는 테이블의 튜플이 삭제, 변경될 수 있어 특정한 권한을 부여받은 사람만 FK를 지정할 수 있도록 해주는 것이다.
• usage: 특정 도메인을 사용할 수 잇는 권한
• all privileges: 모든 권한을 한꺼번에 표현한다.

---

Grant Statement

SQL에서는 권한을 부여하기 위해서 GRANT명령을 사용한다.

GRANT <권한 리스트> on <relation, view이름> to <user_list> [with grant option]

어떠한 relation, view에 대한 권한을 <user_list>에 등록된 유저에게 부여한다는 것이다.

<user_list>에는 user-id를 쓰거나 role을 지정한다. public 키워드를 사용하면 '모든 유저'를 의미한다.

with grant option는 <user_list>의 user들이 다른 user에게 권한을 부여할 수 있는 권한을 주고 싶을 때 사용한다.

 

예시들을 살펴보자.

GRANT select on professor to U1, U2, U3;
GRANT select on professor to U4 with grant option;

professor 테이블에 대한 select권한을 U1, U2, U3에게 주고 있고, U4에게는 professor테이블의 select권한을 다른 테이블에 줄 수 있는 권한도 부여하고 있다.

 

GRANT references (deptName) on department to Lee;

Lee 에게 department테이블의 deptName속성을 참조할 수 있는 권한을 준다. 그러면 Lee는 department테이블의 deptName을 참조하는 다른 relation을 만들 수 있다. relation을 만들 면서 FK로 지정할 수 있다.

---

Revoke Statement

권한을 부여하기 위한 명령이 GRANT라면, 권한을 회수하고 싶을 때는 REVOKE명령을 사용한다.

REVOKE <권한 리스트> on <relation, view이름> FROM <user_list> [restrict|cascade];

<user_list>의 유저에게서 relation이나 view에 대한 <권한 리스트>의 권한을 회수한다.

restrict옵션은 연쇄적으로 권한이 회수되는 것을 방지하기 위해서 연쇄적으로 권한을 회수할 일이 없을 때 명령어가 정상적으로 동작한다.

cascade옵션은 <user_list>에 있는 유저들이 다른 유저에게 준 권한도 연쇄적으로 회수할 때 사용한다.

기본값은 cascade이다.

 

몇가지 예시를 살펴보자.

REVOKE SELECT on professor FROM U1, U2, U3 cascade;
REVOKE SELECT on professor FROM U1, U2, U3 restrict;

첫번째 문장은 U1, U2, U3의 professor테이블에 대한 select 권한을 연쇄적으로 회수하겠다는 의미이다. 

두번째 문장도 권한을 회수한다는 의미이지만, 연쇄적으로 회수될일이 있다면 명령어가 실패한다.

 

만약 grant option을 회수하고 싶다면 다음과 같이 작성한다.

REVOKE grant option for SELECT on professor FROM U5;

---

Authorization Graph

권한을 관리할 때는 graph를 사용한다. 방향그래프 이고, U1이 U2에게 권한을 부여한다면 그래프 상에 Edge를 만든다.

여기서 DBA가 U2의 권한을 연쇄적으로 회수한다면 U2가 다른 유저에게 준 권한도 회수하므로 다음과 같다.

---

Authorization on view

뷰를 생성하기 위해서는 Base Table을 기반으로 뷰를 정의 했었다. 따라서 user가 view를 생성하기 위해서는 Base Table에 대한 최소한의 권한(select 권한)이 있어야 한다. select 권한을 가진다고 해서 insert/update/delete권한을 가지는 것이 아니다.

• 최소한 Base Table에 대한 select 권한을 가지고 있어야 view를 생성할 수 있다.
• 테이블을 생성할 때는 resource권한이 필요하지만, view를 생성할 때는 필요없다.
• select/update/insert/delete 등의 권한은 view를 정의한 Base Relation에 대해 user가 어떤 권한을 가지고 있느냐에 따라 다르다.

예를 들어, Base Relation에서 update권한을 가지고 있어야 view를 통해서 업데이트를 수행할 수 있다.

 

---

ROLE

user에 대해 class 를 만들고 그룹화하여 공통된 권한을 부여하고 싶을 때 사용한다.

CREATE ROLE teller;
CREATE ROLE manager;

GRANT select on branch to teller;
GRANT update(balance) on accout to teller;
GRANT all privileges on account to manager;

GRANT teller to manager;

GRANT teller to Kim, Park;
GRANT manager to Lee;

teller와 manager 라는 role을 만들어 권한을 부여하고, role을 이용해 유저에게 권한을 한꺼번에 부여하고 있다.

 

SQL의 권한 부여에 대해서 알아보았지만, SQL의 권한 부여는 tuple level에 대해 권한을 지원하지 않고 테이블에 대한 권한만 줄 수 있다는 단점이 있다.