일반/데이터베이스

권한 및 역할

readme.md 2022. 6. 27. 15:00

데이터베이스 권한 관리

DBMS는 데이터베이스의 보안을 유지하기 위해 접근 제어 기능을 제공한다. DBMS는 데이터베이스 관리자를 통해 새로운 사용자의 계정과 암호를 생성하고, 기존 사용자의 계정을 변경 및 제거할 수 있다.

로그인을 하더라도 데이터베이스 내부의 모든 데이터를 사용할 수 있는 것은 아니다. DBMS에서 보안을 적용할 수 있는 단위는 데이터베이스 전체부터 특정 테이블의 특정 행과 열 위치에 있는 특정 데이터 값에 이르기까지 매우 다양하다.

데이터베이스 내부의 테이블이나 뷰는 기본적으로 해당 객체를 생성한 사용자만 사용 권한을 가진다. 따라서 데이터베이스 관리자로부터 데이터베이스에 대한 접근 권한을 부여 받아도 자신이 생성하지 않은 테이블, 뷰는 사용할 수 없는 것이 기본 설정이라 할 수 있다. 하지만 데이터베이스는 여러 사용자가 데이터를 공유해서 사용할 목적으로 만들어진 것이므로 필요에 따라 다른 사용자가 생성한 객체에도 접근할 수 있어야 한다. 그래서 해당 객체를 생성한 소유자는 SQL문을 사용하여 다른 사용자에게 객체에 대한 접근 권한을 부여하거나 취소하면 된다.

권한 부여

객체의 소유자가 다른 사용자에게 객체에 대한 사용 권한을 부여하기 위해 필요한 SQL 명령어는 GRANT이다.

GRANT 권한 ON 객체 TO 사용자 [WITH GRANT OPTION];

권한을 부여할 수 있는 단위는 앞서 설명한 것과 같이 특정 행, 열부터 데이터베이스 전체까지 가능하지만 테이블 단위로 권한을 부여하는 경우가 가장 많다. 테이블과 관련한여 다른 사용자에게 부여할 수 있는 주요 권한으로는 INSERT, DELETE, UPDATE, SELECT, REFERENCES가 있다.

REFERENCES는 외래키 제약조건을 정의할 수 있는 권한이다. REFERENCES 권한을 부여 받은 사용자는 권한 부여 대상인 테이블의 기본키를 참조하는 외래키를 자신이 생성하는 테이블에 포함할 수 있다.

GRANT는 기본적으로 테이블을 구성하는 모든 속성에 권한을 부여한다. UPDATE, SELECT는 테이블을 구성하는 일부 속성만 수정 또는 검색하는 권한을 부여할 수도 있다. 일부 속성만 권한을 부여하려면 권한과 함께 권한을 부여하고 싶은 속성의 이름을 ()안에 나열하면 된다. 모든 사용자에게 똑같은 권한을 부여하고 싶으면 특정 사용자를 지정하는 대신 PUBLIC 키워드를 이용하여 GRANT 문을 작성하면 된다.

GRANT 명령어로 부여받은 권한은 다른 사용자에게 부여할 수 없다. 하지만 WITH GRANT OPTION을 포함하면 권한을 부여받은 사용자가 자신이 부여받은 권한을 다른 사용자에게도 부여할 수 있다.

GRANT - TABLE 예제

  • 고객 테이블에 대한 검색 권한을 사용자 Hong에게 부여한다.

    GRANT SELECT ON 고객 TO Hong;

  • 고객 테이블에 대한 삽입과 삭제 권한을 모든 사용자에게 부여한다.

    GRANT INSERT, DELETE ON 고객 TO PUBLIC;

  • 고객 테이블을 구성하는 속성 중 등급과 적립금 속성에 대한 수정 권한을 사용자 Park에게 부여한다.

    GRANT UPDATE ON 고객(등급, 적립금) TO Park;

  • 고객 테이블에 대한 검색 권한을 WITH GRANT OPTION을 포함하여 사용자 Lee에게 부여한다.

    GRANT SELECT ON 고객 TO Lee WITH GRANT OPTION

테이블에 대한 권한은 테이블을 생성한 사용자가 부여할 수 있지만, 테이블이나 뷰를 만드는 DDL에 대한 권한은 데이터베이스 관리자가 부여할 수 있다. 이러한 종류의 권한은 시스템 권한이라고 하는데, 테이블에 권한은 부여할 때와 동일하게 GRANT를 사용하면 된다.

GRANT - 시스템 권한 예제

  • 테이블을 생성할 수 있는 시스템 권한을 사용자 Song에게 부여한다.

    GRANT CREATE TABLE TO Song;

  • 뷰를 생성할 수 있는 시스템 권한을 사용자 Shin에게 부여한다.

    GRANT CREATE VIEW TO Shin;

권한 취소

GRANT 명령으로 권한을 부여 했다면 REVOKE 명령으로 부여한 권한을 취소할 수 있다.

REVOKE 권한 ON 객체 FROM 사용자 CASCADE | RESTRICT;

WITH GRANT OPTION을 포함하여 GRANT 명령을 실행하면 권한을 부여받은 사용자는 다른 사용자에게 권한을 부여할 수 있다. 이 경우 사용자의 권한이 취소된 경우, 권한이 사라진 사용자가 이전에 다른 사용자에게 부여한 권한을 처리하는 방법이 중요한 문제로 남는다. 그래서 REVOKE에서는 CASCADE, RESTRICT 옵션을 사용하여 다른 사용자에게 부여한 권한을 연쇄적으로 취소하거나 취소되지 않도록 설정할 수 있다.

REVOKE 예제

고객 테이블에 대해서 다음과 같은 상황이 놓여있다.

-- Kim
CREATE TABLE 고객(
    고객아이디 int,
  고객이름 varchar(20),
  고객주소 varchar(50),
  PRIMARY KEY 고객아이디
);
GRANT SELECT ON 고객 TO Hong WITH GRANT OPTION;

-- Hong
GRANT SELECT ON 고객 TO Park;

Kim이라는 사용자가 고객 테이블을 만들고 Hong이라는 사용자에게 조회할 수 있는 권한을 WITH GRANT OPTION과 함께 부여했다. 그래서 Hong은 Park에게 고객 테이블에 대한 조회 권한을 주었다.

  • Kim이 Hong에게 부여한 고객 테이블에 대한 검색 권한을 취소하면서 Hong이 다른 사용자에게 부여한 고객 테이블에 대한 검색 권한도 함께 취소한다.

    REVOKE SELECT ON 고객 FROM Hong CASCADE;

  • Hong이 다른 사용자에게 권한을 부여한 적이 없는 경우에만 Kim이 Hong에게 부여한 고객 테이블에 대한 검색 권한을 취소한다.

    REVOKE SELECT ON 고객 FROM Hong RESTRICT;

CASCADE 옵션을 사용하면 연관된 다른 사용자들의 권한도 함께 취소되기 때문에 항상 신중하게 판단을 하고 사용할 필요가 있다. 그래서 테이블의 소유주는 사용자들에게 각 테이블에 대해 어떤 권한을 부여했는지, 그리고 WITH GRANT OPTION을 포함하여 권한을 부여 했는지 등의 여부도 기록해둘 필요가 있다. 필요하다면 각 사용자별로 테이블에 부여된 권한 목록을 작성해두고 관리하는 것이 좋다.

REVOKE - 시스템 권한 예제

데이터베이스 관리자가 다른 사용자에게 부여한 시스템 권한(DDL)도 REVOKE 명령을 사용해서 취소할 수 있다.

  • Hong에게 부여한 테이블 생성 권한을 취소한다.

    REVOKE CREATE TABLE FROM Hong;

역할의 부여와 취소

GRANT 명령을 사용하면 특정 사용자에게 데이터베이스 객체에 대한 접근 권한을 부여할 수 있다. 하지만 동일한 작업을 여러 사용자에게 하는 것은 번거로울 뿐만 아니라 어떤 사용자에게 어떤 권한을 부여했는지 확인하고 있어야 하기 때문에 데이터베이스 관리에 불편함을 초래하게 된다.

이러한 문제를 해결하기 위해 역할을 사용할 수 있다. 

-- GRANT로 각 사용자에게 역할 부여하기
GRANT SELECT ON 고객 TO Hong;
GRANT INSERT ON 고객 TO Hong;
GRANT UPDATE ON 고객 TO Hong;
-- GRANTH SELECT, INSERT, UPDATE ON 고객 TO Hong

GRANT SELECT ON 고객 TO Park;
GRANT INSERT ON 고객 TO Park;
GRANT UPDATE ON 고객 TO Park;

GRANT SELECT ON 고객 TO Lee;
GRANT INSERT ON 고객 TO Lee;
GRANT UPDATE ON 고객 TO Lee;

역할을 사용하지 않고 Hong, Park, Lee에게 조회, 삽입, 갱신 권한을 부여하기 위해선 이와 같이 여러 번의 SQL을 사용해야 하고, 부여한 권한들을 관리하기 위해 추가적인 리소스가 더 많이 필요하게 될 것이다.

이렇게 번거로운 작업을 하는 것보다 역할을 사용해서 사용자들에게 부여하고 싶은 권한을 모아서 하나의 역할로 만들고, 그 역할에 대한 권한을 사용자에게 부여하면 된다. 역할의 생성은 데이터베이스 관리자가 담당하고, 역할을 생성하기 위해 필요한 SQL은 CREATE ROLE이다.

CREATE ROLE 롤이름;

역할에 필요한 권한은 GRANT를 사용해서 부여하게 된다. 역할에게 부여된 객체 접근 권한은 객체의 소유자가 담당하게 된다.

GRANT 권한 ON 객체 TO 롤이름;

이렇게 만들어진 역할을 사용자에게 부여할 때에도 마찬가지로 GRANT를 사용한다.

GRANT 롤이름 TO 사용자;

역할 부여 예제

  • role_1이라는 이름의 역할을 생성한다.

    CREATE ROLE role_1;

  • 고객 테이블에 대한 검색, 삽입, 삭제 권한을 role_1 역할에 부여한다.(고객 테이블에 대한 역할 부여는 고객 테이블의 소유자가 진행해야 함)

    GRANT SELECT, INSERT, DELETE ON 고객 TO role_1;

  • 고객 테이블에 대한 검색, 삽입, 삭제 권한을 포함하고 있는 role_1 역할을 사용자 Hong에게 부여한다.

    GRANT role_1 TO Hong;

이렇게 역할을 만들어 각 사용자에게 부여하면 SQL을 작성하는 작업이 줄어들게 되며, 만들어진 역할을 중심으로 데이터베이스에 접근하는 권한을 관리하기 때문에 특정 객체에 대한 접근 권한을 변경을 각 사용자에 대하여 일일이 진행할 필요가 없어져 관리하기도 편해진다.

예제에서 만든 role_1에 UPDATE 권한을 추가하고 싶으면 GRANT UPDATE ON 고객 TO role_1;을 실행하면 되고, 이를 통해 role_1의 권한을 부여받은 다른 사용자들 역시 고객 테이블에 대한 UPDATE 권한을 얻을 수 있게 된다.

역할 취소 예제

이렇게 만들어진 역할은 REVOKE를 사용하여 취소를 할 수 있다.

  • 사용자 Hong에게 부여한 role_1 역할을 취소한다.

    REVOKE role_1 FROM Hong;

  • role_1 역할을 제거한다.

    DROP ROLE role_1;