Real MySQL 8.0 3장 - 사용자 및 권한

1. 사용자 식별

MySQL 에서는 사용자의 계정 뿐만 아니라 사용자의 “접속 지점(IP, 호스트)” 까지도 계정의 일부가 된다.

'user'@'127.0.0.1'
'user'@'%' <- % 는 모든 IP 또는 호스트를 의미한다.

중첩 계정 문제 - 접속 지점만 다른 경우

'user'@'192.168.0.10' 비밀번호 1234
'user'@'%' 비밀번호 5678

이렇게 계정 호스트만 다른 경우 IP가 ‘192.168.0.10’ 인 PC에서 비밀번호 5678로 접속하면 인증 실패한다.

⇒ 두 계정 정보 중 범위가 좁은걸 먼저 선택하기 때문

 

2. 시스템 계정과 일반 계정

시스템 계정을 두는 이유 :

데이터베이스의 중요한 작업들을 수행 할수있는 권한(SYSTEM_USER 권한)을 별도로 두어 일반 사용자와 구분하기 위함.

시스템 계정 :

• SYSTEM_USER 권한을 가진 계정
• DBA(데이터베이스 관리자)를 위한 계정
• 다른 계정을 관리하거나, 다른 세션에서 사용중인 쿼리를 강제 종료 가능, 스토어드 프로그램 생성시 DEINER 를 다른 사용자로 지정 가능

내장된 시스템 계정

• mysql.% 로 시작되는 계정들
• MySQL 내부에서 사용하는 계정이므로 삭제하지 않도록 주의하자 내장된 시스템 계정들은 처음부터 잠겨있는 상태(acoount_locked: Y)로 생성되기 때문에 접속하려고 하면 에러가 나서 악의 적인 용도로 이용될 수 없기 때문에 보안 걱정을 하지 않아도 된다.

내장된 시스템 계정 확인

mysql> SELECT user, host, account_locked FROM mysql.user WHERE user LIKE 'mysql.%';';
+------------------+-----------+----------------+
| user             | host      | account_locked |
+------------------+-----------+----------------+
| mysql.infoschema | localhost | Y              |
| mysql.session    | localhost | Y              |
| mysql.sys        | localhost | Y              |
+------------------+-----------+----------------+
3 rows in set (0.01 sec)

 

3. 계정 생성 및 조회하기

MySQL 5.7 버전 까지는 GRANT 명령 만으로 권한 부여와 동시에 계정 생성까지 가능했다.

// 계정 생성과 동시에 권한 부여하기
GRANT ALL PRIVILEGES ON *.* TO 'test_user'@'%' IDENTIFIED BY '12345678';

// 생성된 계정 확인
SELECT * FROM mysql.user ;

 

MySQL 8.0 버전 부터는 GRANT 명령으로 권한 부여와 동시에 계정 생성이 못하게 바뀌었다.

계정 생성은 CREATE USER 명령으로 해야한다.

// GRANT ALL 로 IDENTIFIED BY 를 함께 쓸수 없다.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test_user'@'%' IDENTIFIED BY '12345678';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY '12345678'' at line 1

mysql> CREATE USER 'test_user'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'password';
Query OK, 0 rows affected (0.07 sec)

mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| test_user        | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.01 sec)

IDENTIFIED WITH : 비밀번호 인증 방식의 변화

mysql 8.0 버전 부터 기본 인증방식이 Native Authentication 방식에서 Caching SHA-2 Authentication 방식으로 바뀌었다.

Caching SHA-2 Authentication 방식은 기존 Native 방식과 달리 SSL/TLS 또는 RSA 키페어를 필요로 하기 때문에 기존 5.7 까지의 연결방식과 다른 방식으로 접근해야 한다.

SET GLOBAL 명령을 통해 기본 인증방식을 정할 수 있다. ⇒ read only variable 이라 수정 안되는 문제 발생

mysql> SET GLOBAL default_authentication_plugin='mysql_native_password';
ERROR 1238 (HY000): Variable 'default_authentication_plugin' is a read only variable

// 기본 인증방식 확인
mysql> show global variables where Variable_name like 'default_auth%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.01 sec)

default_authentication_plugin 변수의 경우 read only 변수라 런타임에서 변경이 안되는 문제

개개 유저의 IDENTIFIED WITH 방식을 지정하는 방법이 있다.

The error message "Variable 'default_authentication_plugin' is a read only variable" is encountered when you try to change the value of the default_authentication_plugin system variable in the MySQL database. This error occurs because the default_authentication_plugin variable is a read-only variable and its value cannot be changed at runtime.

This variable is used to specify the default authentication plugin that will be used to authenticate new user accounts. The default value is mysql_native_password, which is the traditional method of authentication used by MySQL.

If you want to change the authentication method used by your MySQL database, you'll need to modify the plugin used by each individual user account, rather than changing the value of the default_authentication_plugin variable.

For example, you can use the following SQL statement to change the authentication plugin for a user:

ALTER USER 'user_name'@'host_name' IDENTIFIED WITH plugin_name;

 

4. 권한

정적 권한 (MySQL 서버 소스코드에 명시되어있는 권한)

• 글로벌 권한 : CREATE USER와 같이 데이터베이스나 테이블 이외의 객체에 적용되는 권한
• 객체 권한 : 데이터베이스나 테이블에 적용되는 권한, CREATE, DROP 등
• ALL(또는 ALL PRIVILEGE) 권한 : 권한 범위에서 가질 수 있는 모든 권한을 포함한다.

동적 권한 (MySQL 서버가 시작되면서 플러그인이나 컴포넌트가 등록되면 그때 동적으로 생성하는 권한)

⇒ MySQL 5.7 버전의 SUPER 라는 권한이 잘개 쪼개져 동적 권한으로 분산되었다.

권한 부여하기 - GRANT

GRANT 명령으로 유저에 권한을 부여 할 수 있다.

GRANT 권한종류 ON 권한범위 TO 유저

권한 범위 지정

글로벌 권한 부여하기

글로벌 권한을 부여할때는 항상 ON 뒤에 권한 부여 범위를 *.*로 지정해주어야 한다.

GRANT 권한명 ON *.* TO 유저

ex)
GRANT SUPER ON *.* TO 'user'@'localhost';

DB 권한 부여하기

DB 권한을 부여할때는 ON 뒤에 권한 부여 범위에 DB까지만 명세 하면 된다.

GRANT EVENT ON *.* TO 'user'@'localhost';
GRANT EVENT ON employees.* TO 'user'@'localhost';

테이블 권한 부여하기

테이블 권한을 부여할때는 ON 뒤에 권한 부여 범위에 테이블 명 까지 전부 명세 하면 된다.

GRANT SELECT ON employees.department TO 'user'@'localhost';

 

5. 역할 (mysql 8.0 부터)

mysql 8.0 부터 추가된 기능

여러 권한을 그룹으로 만들어서 관리하고 싶을때 사용

내부적으로 사용자계정과 동일한 객체로 취급된다.

빈 역할 생성하기 - CREATE ROLE

CREATE ROLE role_emp_local_read@localhost;

⇒ 호스트 부분을 명시하지 않은 경우 기본적으로 모든 호스트 (%) 로 설정 된다.

생성된 역할 확인하기 - mysql.user 테이블에서 SELECT로 조회

mysql> select user, host, account_locked from mysql.user;
+---------------------+-----------+----------------+
| user                | host      | account_locked |
+---------------------+-----------+----------------+
| root                | %         | N              |
| test_user           | %         | N              |
| mysql.infoschema    | localhost | Y              |
| mysql.session       | localhost | Y              |
| mysql.sys           | localhost | Y              |
| role_emp_local_read | localhost | Y              |
| root                | localhost | N              |
+---------------------+-----------+----------------+
7 rows in set (0.02 sec)

⇒ 계정과 역할은 처음 생성시 account_locked가 다르다는 것 빼고는 같은 오브젝트이다.

⇒ 역할은 애초에 권한을 모아둔 유령의 유저인것이다. 다른 계정에 어떤 역할을 부여할 때 그 역할이 가진 권한들을 역할을 부여하려는 유저가 이미 가진 권한에 병합한다고 생각하면 되기 때문에 MySQL은 계정과 역할을 시스템적으로 구분 할 필요가 없다.

역할에 권한 추가하기 - GRANT

⇒ 유저에 권한 추가하는 문법과 동일

GRANT SELECT ON employees.* TO role_emp_local_read@'localhost';

유저에 역할 부여하기 - GRANT

GRANT 역할 TO 유저;

ex)
GRANT role_emp_local_read@localhost TO reader@'127.0.0.1';