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. 시스템 계정과 일반 계정

시스템 계정을 두는 이유:

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 변수의 경우 읽기 전용 변수이며 런타임에서 변경할 수 없는 문제

개별 사용자의 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 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';

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