역할(Role)

역할이란

• MySQL 8.0 버전부터 권한을 묶어서 역할(Role)을 사용할 수 있게 됐다.
• 실제 MySQL 서버 내부적으로 역할은 계정과 똑같은 모습을 하고 있다.
  • MySQL 서버 내부적으로 역할과 계정은 동일한 객체로 취급된다.
  • 단지 하나의 사용자 계정에 다른 사용자 계정이 가진 권한을 병합해서 권한 제어가 가능해졌을 뿐이다.

mysql DB의 user 테이블

• mysql DB의 user 테이블을 살펴보면 실제 권한과 사용자 계정이 구분 없이 저장된 것을 확인할 수 있다.

  • 역할과 계정의 차이는 account_locked 칼럼의 값이 다를 뿐 아무런 차이가 없다.

    SELECT user, host, acount_locked FROM mysql.user;

• MySQL 서버는 역할과 계정을 구분할 필요가 없다.
  • 하나의 계정에 다른 계정의 권한을 병합하기만 하면 되기 때문이다.
  • 만약 역할과 계정을 명확히 구분하고자 한다면 데이터베이스 관리자가 식별할 수 있는 프리픽스( 예를 들어 role_ 등)나 키워드를 추가해 역할의 이름을 선택하는 방법을 권장한다.

역할 생성 및 사용

역할 생성과 권한 부여

• CREATE ROLE 명령으로 역할을 정의한다.

  • 이렇게 만들어진 역할은 빈 껍데기만 있는 역할이다.

  • 호스트 부분을 별도로 명시하지 않으면 자동으로 ‘모든 호스트(%)’가 자동으로 추가된다.

    -- role_emp_read와 role_emp_write라는 이름의 역할을 정의
    CREATE ROLE role_emp_read, role_emp_write;
    
    -- 위의 쿼리와 동일한 계정이 생성된다.
    CREATE ROLE role_emp_read@'%', role_emp_write@'%';

• GRANT 명령으로 각 역할에 대해 실질적인 권한을 부여한다.

  -- role_emp_read 역할에 employees DB의 모든 객체에 대해 SELECT 권한 부여
  GRANT SELECT ON employees.* TO role_emp_read;
  
  -- role_emp_write 역할에 employees DB의 모든 객체에 대해 데이터 변경 권한 부여
  GRANT INSERT, UPDATE, DELETE ON employees.* TO role_emp_write;

• 기본적으로 역할은 그 자체로 사용될 수 없고 계정에 부여해야 한다.

  • CREATE USER 명령으로 계정을 생성(아무런 권한 없음) 후 GRANT 명령으로 역할을 부여한다.

    -- 계정 생성
    CREATE USER reader@'127.0.0.1' INDENTIFIED BY 'qwerty'
    CREATE USER writer@'127.0.0.1' INDENTIFIED BY 'qwerty'
    
    -- 계정에 역할 부여
    GRANT role_emp_read TO reader@'127.0.0.1'
    GRANT role_emp_write TO writer@'127.0.0.1'

• SHOW GRANTS 명령을 계정이 가진 권한을 확인할 수 있다.

역할 활성화

• 위에서 적은 대로 역할을 생성 후 권한을 부여했음에도 권한이 없다는 에러를 만나게 될 것이다.

  • 실제 역할은 부여되어 있지만 계정의 활성화된 역할을 조회해 보면 해당 역할이 없음을 확인할 수 있다.

    -- 계정의 활성화된 역할 조회
    SELECT current_role();

• 계정이 역할을 사용할 수 있게 하려면 SET ROLE 명령을 실행해 해당 역할을 활성화해야 한다.

  • 일단 역할이 활성화되면 그 역할이 가진 권한은 사용할 수 있는 상태가 된다.

  • 계정이 로그아웃됐다가 다시 로그인하면 역할이 활성화되지 않은 상태로 초기화돼 버린다.

    -- 계정 활성화
    SET ROLE 'role_emp_read';

• 사용자가 MySQL 서버에 로그인할 때 역할을 자동으로 활성화할지 여부를 activate_all_roles_on_login 시스템 변수로 설정할 수 있다.

  • MySQL 서버의 역할은 자동으로 활성화되지 않게 설정돼 있다.

  • activate_all_roles_on_login 시스템 변수를 ON으로 설정하면 SET ROLE 명령으로 역할을 활성화하지 않아도 로그인과 동시에 부여된 역할이 자동으로 활성화된다.

    -- 자동으로 역할 활성화
    SET GLOBAL activate_all_roles_on_login=ON;

역할의 호스트

• 역할의 호스트 부분은 아무런 영향이 없다.

  -- 역할 생성
  CREATE ROLE role_emp_local_read@localhost;
  
  -- 계정 생성
  CREATE USER reader@'127.0.0.1' IDENTIFIED BY 'qwerty';
  
  -- 역할에 권한 부여
  GRANT SELECT ON employees.* TO role_emp_local_read@'localhost';
  
  -- 계정에 역할 부여
  -- 호스트가 달라서 호환되지 않음에도 SELECT 권한이 부여된다.
  GRANT role_emp_local_read@'localhost' TO reader@'127.0.0.1';

• 역할을 다른 계정에 부여하지 않고 직접 로그인하는 용도로 사용한다면(실제 계정처럼 사용한다면) 그때는 역할의 호스트 부분이 중요해진다.

역할과 계정의 생성을 분리한 이유

• 역할과 계정은 내외부적으로 동일한 객체다.
• 그럼에도 생성 명령을 구분해서 지원하는 이유는, 데이터베이스 관리의 직무를 분리할 수 있게 해서 보안을 강화하는 용도로 사용될 수 있게 하기 위해서다.
  • 역할은 CREATE ROLE 명령으로 생성하고, 계정은 CREATE USER 명령으로 생성
  • CREATE USER 명령에 대해서는 권한이 없지만 CREATE ROLE 명령만 실행 가능한 사용자는 역할을 생성할 수 있다.
• 이렇게 생성된 역할은 계정과 동일한 객체를 생성하지만, 실제 이 역할은 account_locked 칼럼의 값이 ‘Y’로 설정돼 있어서 로그인 용도로 사용할 수가 없게 된다.

역할 그래프 관계

• 계정의 기본 역할 또는 역할에 부여된 역할 그래프 관계는 다음 두 가지 방법으로 확인할 수 있다.

  • SHOW GRANTS 명령을 사용

  • mysql DB의 권한 관련 테이블 참조 (표 형태로 깔끔하게 볼 수 있다)

    저장소 테이블	설명
    mysql.default_roles	계정별 기본 역할
    mysql.role_edges	역할에 부여된 역할 관계 그래프

Reference

참고 서적

📔 Real MySQL 8.0