책너두 (Real MySQL 8.0 1권) 5일차 (~75p)

요약

• 사용자 식별에 아이디 뿐만 아니라 호스트도 함께 명시해야 함.
• 여러 시스템 내장 계정의 존재를 알게 됨.
• 유저 생성시 여러 옵션으로 권한, 정책들을 사용할 수 있음을 알게 됨.
• 이중 비밀번호를 통해 응용 서비스의 다운 타임 없이 비밀번호를 새롭게 변경할 수 있는 방식을 알게됨.

발췌

• 하나의 계정에 다른 계정의 권한을 병합하기만 하면 되므로 MySQL 서버는 역할과 계정을 구분할 필요가 없는 것이다.

메모

권한 (Privilege)

• 5.7 버전까지는 글로벌 권한과 객체 단위 권한 으로 구분됐다.
  • 글로벌 권한 : 데이터베이스나 테이블 이외의 객체에 적용되는 권한
  • 객체 권한 : 데이터베이스나 테이블을 제어하는데 필요한 권한
• 객체 권한은 GRANT 명령으로 권한을 부여할 떄 반드시 특정 객체를 명시해야 함.
• 글로벌 권한은 GRANT 명령에서 특정 객체를 명시하지 말아야 함.
• 예외적으로 ALL (or ALL PRIVILEGES)은 글로벌과 객체 권한 두 가지 용도로 사용 될 수 있음.
  • 특정 객체에 ALL 권한이 부여되면 해당 객체에 적용할 수 있는 모든 객체 권한이 부여됨.
  • 글로벌로 ALL 이 사용되면 글로벌 수준에서 가능한 모든 권한을 부여함.
• 8.0 부터 동적 권한이 추가됨.
• 5.7 버전에서 사용한 권한은 정적 권한이라고 함.
  • MySQL 서버의 소스코드에 고정적으로 명시돼 있는 권한
• 동적 권한은 MySQL 서버가 시작되면서 동적으로 생성되는 권한임.
  • ex) 컴포넌트, 플러그인이 서버에 설치되면 그때 등록되는 권한을 동적 권한이라 함.
• 5.7 버전까지는 SUPER 라는 권한이 데이터베이스 관리를 위해 꼭 필요한 권한이었음.
• 8.0 부터는 SUPER 권한이 잘게 쪼개어 동적 권한으로 분산됨.
  • 8.0 부터 백업 관리자와 복제 관리자 개별로 꼭 필요한 권한만 부여할 수 있게 됨
• 사용자에게 권한 부여할 때 GRANT 명령을 사용한다.
• 권한의 특성(범위)에 따라 GRANT 명령의 ON 절에 명시되는 오브젝트 (DB or 테이블) 의 내용이 바뀌어야 함.

mysql> GRANT privilege_list ON db.table TO 'user'@'host';

• privileges_list 에 구분자(,) 를 사용하여 여러 권한을 동시에 명시할 수 있음.
• TO 키워드 뒤에는 권한을 부여할 대상 사용자를 명시한다.
• ON 키워드 뒤에는 어떤 DB 의 어떤 오브젝트에 권한을 부여할지 결정한다.

## 글로벌 권한
mysql> GRANT SUPER ON *.* TO 'user'@'localhost';

• 글로벌 권한은 특정 DB 나 테이블에 부여될 수 없기 때문에 GRANT 명령의 ON 절에는 항상 *.* 를 사용한다.
• *.* 는 모든 DB 의 모든 오브젝트 (테이블과 스토어드 프로시저나 함수 등) 를 포함해서 MySQL 서버 전체를 의미함.

## DB 권한
mysql> GRANT EVENT ON *.* TO 'user'@'localhost';
mysql> GRANT EVENT ON employees.* TO 'user'@'localhost';

• DB 권한은 특정 DB 에 대해서만 권한을 부여하거나, 서버에 존재하는 모든 DB 에 대해 권한을 부여할 수 있다.
• 여기서 말하는 DB 는 DB 내부에 존재하는 테이블 뿐만 아니라 스토어드 프로그램들도 모두 포함된다.
• DB 권한만 부여하는 경우, 테이블에 대해 부여할 수 없기에 employees, department 와 같은 테이블 까지 명시할 수 없음.
• DB 권한은 서버의 모든 DB 에 적용할 수 있으므로 *.* 을 사용할 수 있음.
• 특정 DB 에 대해서만 권한을 부여하는 것도 가능하므로 db.* 로 대상을 설정하는 것도 가능
  • db.table 로 오브젝트(테이블) 까지 명시할 수 없음.

## 테이블 권한
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'user'@'localhost';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON employees.* TO 'user'@'localhost';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON employees.department TO 'user'@'localhost';

• 첫번째 예시 처럼 테이블 권한은 서버의 모든 DB 에 대한 권한 부여가 가능함.
• 두번째 예시 처럼 특정 DB 의 오브젝트에 대해 권한을 부여할 수 있음.
• 세번째 예시 처럼 특정 DB의 특정 테이블에 대해 권한 부여하는게 가능

mysql> GRANT SELECT,INSERT,UPDATE(dept_name) ON employees.department TO 'user'@'localhost';

• 컬럼에 부여할 수 있는 권한은 DELETE 를 제외한 INSERT, UPDATE, SELECT 로 3가지 임.
• 각 권한 뒤에 컬럼을 명시하는 형태로 부여함.
• 위 예시의 경우, SELECT, INSERT 는 employees db의 department 테이블에서 사용 가능.
• UPDATE 의 경우 employees db의 department 테이블에서 dept_name 칼럼에 대해서만 수행 가능.
• 테이블이나 칼럼 단위의 권한은 사실 잘 사용하지 않음.
  • 칼럼 단위의 권한이 하나라도 설정되면 나머지 모든 테이블의 모든 칼럼에 대해서 권한 체크를 하기 때문에 전체적인 성능에 영향을 미칠 수 있음.
  • 칼럼 단위의 접근 권한이 꼭 필요하다면 GRANT 대신 해당 컬럼만으로 별도의 뷰(VIEW) 를 만들어서 사용하는 방법을 생각할 수 있다.
  • 뷰도 하나의 테이블로 인식되므로 뷰르 만들면 해당 뷰의 칼럼에 대한 권한 체크 없이 뷰 자체에 대한 권한만 체크하면 된다.
• 각 계정이나 권한에 부여된 권한 혹은 역할을 확인하려면 SHOW GRANTS 명령을 사용해도 되고, 표 형태로 깔끔하게 보고 싶다면 mysql DB 권한 관련 테이블을 참조하면 됨.

역할 (Role)

• 8.0 부터 권한을 묶어서 역할(Role) 을 사용할 수 있게 됨.
• 실제 MySQL 서버 내부적으로 역할(Role)은 계정 과 똑같은 모습을 하고 있음.

ex)

mysql> CREATE ROLE role_emp_read, role_emp_write;

• CREATE ROLE 명령은 빈 껍데기만 있는 역할을 정의한 것임.

mysql> GRANT SELECT ON employees.* TO role_emp_read;
mysql> GRANT INSERT, UPDATE, DELETE ON employees.* TO role_emp_write;

• role_emp_read 역할에는 employees db 의 모든 객체에 대해 읽기 권한만 부여함
• role_emp_write 역할에는 employees db 의 모든 객체에 대해 데이터 변경 (insert, update, delete) 권한을 부여함.
• 역할은 그 자체로 사용할 수 없음. 권한은 계정에 부여해야 함.

mysql> CREATE USER reader@'127.0.0.1' IDENTIFIED BY 'qwerty';
mysql> CREATE USER writer@'127.0.0.1' IDENTIFIED BY 'qwerty';
mysql> GRANT role_emp_read TO reader@'127.0.0.1';
mysql> GRANT role_emp_read, role_emp_write TO writer@'127.0.0.1';

• reader 계정에 role_emp_read 역할만 부여함
• writer 계정에 role_emp_read 와 role_emp_write 역할을 부여함
• SHOW GRANTS 명령으로 계정이 가진 권한을 확인할 수 있음.
• 하지만 위 상황만 실행하면, 권한이 없다는 에러가 발생함.
  • 실제 역할은 부여됐지만 계정의 활성화된 역할을 조회하면 role_emp_read 역할이 없음을 확인할 수 있음.
  • mysql> SELETE current_role();
  • reader 계정이 role_emp_read 역할을 사용할 수 있게 하려면 SET ROLE 명령을 실행해서 해당 역할을 활성화 해야함.
  • mysql> SET ROLE 'role_emp_read'; mysql> SELECT current_role(); mysql> SELECT COUNT(*) FROM employees.employees; ## 성공적으로 수행됨.
  • 로그아웃후 로그인 하게 되면 다시 역할이 활성화되지 않은 상태로 초기화 됨.
    • MySQL 서버의 역할이 자동으로 활성화 되지 않게 설정돼 있기 때문
      • activate_all_roles_on_login 시스템 변수로 설정 가능.

      mysql> SET GLOBAL activate_all_roles_on_login=ON;

• 8.0 에서 MySQL 서버 내부적으로 역할과 계정은 동일한 객체로 취급한다.
• 하나의 계정에 다른 계정의 권한을 병합하기만 하면 되기 때문에 MySQL 서버는 역할과 계정을 구분할 필요가 없다.
• 위 예시에서 CREATE ROLE 을 사용했을 때, 호스트 부분을 명시하지 않았다.
  • 이는 자동으로 ‘모든 호스트(%)’ 가 자동으로 추가됐음을 의미한다.
• 역할인지 구별하기 위해 ‘role_’ 라는 프리픽스를 붙여 역할의 이름을 정하는 것을 권장한다.

ex)

mysql> CREATE ROLE role_emp_local_read@localhost;
mysql> CREATE USER reader@'127.0.0.1' IDENTIFIED BY 'qwerty';
mysql> GRANT SELECT ON employees.* TO role_emp_local_read@'localhost'
mysql> GRANT role_emp_local_read@'localhost' TO reader@'127.0.0.1'

• 위 예시는 역할과 계정과 호스트 부분이 서로 달라 호환되지 않는 상태임.
• 위 예제를 실행하면 사용자 계정에 employees DB 의 객체들에 대한 SELECT 권한이 부여된다.
  • 즉 역할의 호스트 부분은 아무런 영향이 없음.
  • 만약 역을 다른 계정에 부여하지 않고 직접 로그인 하는 용도로 사용한다면 그떄는 호스트 부분이 중요해짐.
• MySQL 서버에서 역할과 계정을 생성할때 CREATE ROLE, CREATE USER 명령으로 구분한 이유
  • 데이터베이스 관리의 직무를 분리할 수 있게 해서 보안을 강화하는 용도로 사용될 수 있게 하기 위함이다.
  • ex) CREATE USER 명령에 대해 권한이 없지만 CREATE ROLE 명령으로 실행 가능한 사용자 역할을 생성할 수 있다.
    • 이렇게 생성된 역할은 계정과 동일한 객체를 생성하지만 실제로 account_locked 컬럼 값이 ‘Y’ 로 설정되어 있기 떄문에 로그인 용도로 사용할 수 없다.