[SQL] 보안, 권한 부여 grant, revoke, role

개념

db 관리자는 db에 대한 접근 권한을 갖지 못한 사용자로부터 데이터를 안전하게 보호할 의무가 있음

DBMS에서 제공하는 보안

- 허가받지 않는 사용자, 즉 권한이 없는 사용자로부터 데이터의 접근을 사전에 차단

 

db에서의 사용 권한)

사용자가 db 내의 특정 객체에 대해 특정 연산을 실행할 수 있는 권리

- 특정 객체 : 테이블, 필드, 뷰, 저장 프로시저 등 db의 구성 요소

 

권한 제어가 가능한 연산의 종류

- 데이터 접근 관련 연산 (DML)

예) SQL의 select, insert, delete, update 등

- 스키마 관련 연산 (DDL)

예) create table, alter table, drop table, create index 등

권한에 따른 사용자 분류

db 관리자 (DBA)

- db 내의 모든 객체에 대해 모든 권한을 가짐

- 객체에 대한 연산, 객체의 삭제와 변경 포함

- 사용자에게 해당 객체에 대한 권한을 부여하거나 회수 가능

 

객체 소유자(owner)

- 객체를 생성한 사용자

- 생성한 객체에 대해 모든 권한을 소유

- 해당 객체에 대한 권한의 부여나 회수도 포함

 

기타 사용자

- 기본적으로 다른 사용자의 객체에 대한 일체의 사용권한이 없음, 다만 db 관리자나 객체 소유자로부터 일부 또는 모든 권한을 별도로 부여받을 수 있음.

SQL에서의 권한 제어 - GRANT

권한을 부여하는 명령

형식 : grant <권한 리스트> on <객체명> to <사용자 리스트>

<권한 리스트> : select, insert, delete, update, references 중 한 개 이상
<사용자 리스트> : 권한을 부여받는 사용자들의 리스트

◽ <사용자 리스트>에게 <객체명>에 대한 <권한 리스트>를 실행할 권리를 부여한다는 의미
예) 사용자 kim에게 student 테이블에 대해 select 연산을 수행할 수 있는 권한을 부여

grant select on student to kim

 

예) kim에게 student 테이블에 대한 select와 delete 권한을 동시에 부여

grant select, delete on student to kim

 

references 권한

- 테이블을 외래키로 참조할 수 있는 권한

권한이 없으면 kim이 생성한 student 테이블은 department 테이블을 외래키로 참조할 수 없음

 

references 권한 부여 예

grant references (dept_id) on department to kim

 

- dept_id는 테이블 department의 기본키

- kim은 department 테이블을 참조하는 외래키를 포함하는 테이블의 생성이 가능

 

모든 사용자에게 권한을 부여

<사용자 리스트>에 public 사용

 

예) student에 대해 모든 사용자들에게 select 권한을 부여

grant select on student to public

 

모든 종류의 권한을 하나의 명령으로 부여하는 방법

- all privileges 키워드 사용

 

예) student 테이블에 대한 모든 권한을 lee에게 부여

grant all privileges on student to lee

 

WITH GRANT OPTION

부여받은 권한을 다른 사용자에게 전파할 수 있는 특별한 옵션

 

예) student 테이블에 대한 select 권한을 kim에게 부여함과 동시에 이 권한을 다른 사용자에게 다시 전파할 수 있는 자격까지 부여

grant select on student to kim with grant option

 

REVOKE

다른 사용자에게 부여한 권한을 회수하기 위한 명령

형식 : revoke <권한리스트> on <객체명> from <사용자 리스트>

<사용자 리스트> 에 속한 사용자들로부터 <객체명>에 대한 <권한리스트> 연산들의 실행 권한을 회수

 

예) kim에게 부여되었던 student 테이블에 대한 select 권한을 회수

revoke select on student from kim

 

ROLE

특정 테이블에 대한 권한을 부여해야 할 사용자가 많다면, 반복적으로 grant문이나 revoke을 실행해야하는 문제 발생, 단순 반복 작업을 줄이기 위해 권한별로 사용자 그룹을 만들어 그룹에 권한을 부여하는 방법이 필요

 

권한에 따른 사용자 그룹

롤은 db 관리자만이 생성 가능

형식 : create role <롤이름>

 

예) 사원가 임원에 대한 롤을 생성

create role employee
create role manager

 

롤에 사용자 배정

형식 : grant<롤리스트> to <사용자 리스트>

 

예) 사용자 lee와 kim은 사원, chang와 choi는 임원일 경우

grant employee to lee, kim
grant manager to chang, choi

 

롤에 권한을 부여

grant문과 형식은 동일

 

예) employee에게는 student 테이블에 대해 select 연산 허용하고, manager에게는 select와 insert 연산 허용

grant select on student to employee
grant select, insert on student to manager

 

롤에 부여된 권한을 회수

revoke문과 형식 동일

 

예) manager에게 부여된 insert 권한을 회수

revoke insert on student from manager

 

배정된 롤에서 사용자를 배제

형식 : revoke <롤리스트> from <사용자 리스트>

 

예) 사용자 choi를 manager로부터 배제

revoke manager from choi

 

롤 삭제

형식 : drop role <롤 이름>

 

뷰를 이용한 권한 제어

- 특정 테이블에서 일부 필드 혹은 일부 레코드들에 대해서만 접근을 허용할 경우 이 부분들을 뷰로 정의
- 정의된 뷰에 대해 접근 권한을 부여하고 실제 테이블에 대한 접근을 차단
- 사용자에게 감추고 싶은 부분에 대한 강력한 보안 수단 제공
- 필드뿐만 아니라 일부 레코드들에 대해서도 접근제어가 가능

 

https://nowes00.tistory.com/8