User Identification

Privilege

Role

User Identification

Mysql User Format: 'username'@'hostname'

username: 사용자 아이디

hostname: 사용자 접속 지점(클라이언트가 실행된 호스트명, 도메인 또는 IP 주소)

로컬 호스트에서만 접속 가능한 id

'lorens_id'@'127.0.0.1'

모든 호스트에서 접속 가능한 id

'lorens_id'@'%': %는 모든 IP 또는 모든 호스트명을 의미

동일한 id가 있는 경우

좁은 범위의 host를 가진 id가 먼저 적용됨

'lorens_id'@'192.168.0.2', 'lorens_id'@'%' : 192.168.0.2 호스트에서 접속 시 'lorens_id'@'192.168.0.2'가 먼저 선택됨

User Management

User Table

MySQL 내부적인 데이터는 MySQL의 기본 스키마인 mysql에 존재함

MySQL의 계정은 mysql.user 테이블에서 확인 가능

SYSTEM USER, NORMAL USER

시스템 계정: SYSTEM_USER 권한 보유 (DBA용)

일반 계정: SYSTEM_USER 권한 미보유 (개발자용)

SYSTEM_USER 권한

기본 내장 계정

CREATE USER

계정 생성 관련 옵션

CREATE USER 'lorens'@'%'
    IDENTIFIED WITH 'mysql_native_password' BY 'password'
    REQUIRE NONE
    PASSWORD EXPIRE INTERVAL 30 DAY
    ACCOUNT UNLOCK 
    PASSWORD HISTORY DEFAULT 
    PASSWORD REUSE INTERVAL DEFAULT 
    PASSWORD REQUIRE CURRENT DEFAULT;

IDENTIFIED WITH

default authentication plugin

REQUIRE

PASSWORD EXPIRE

PASSWORD HISTORY

PASSWORD REUSE INTERVAL

PASSWORD REQUIRE

ACCOUNT LOCK/UNLOCK

Privilege

Types of Privileges

정적 권한

동적 권한

GRANT

사용자에게 권한을 부여할 때 GRANT 명령 사용

Format

GRANT privilege_list # 권한 (여러 개 명시 가능)
    ON db.table # 권한의 특성(범위)에 따라 명시되는 객체(DB, 테이블) 
    TO 'user'@'host' # 권한 부여 대상
    ;

글로벌 권한 부여

글로벌 권한을 부여할 때는 특정 DB나 테이블에 부여될 수 없으므로 ON 절에는 항상 .을 사용함

*.*: 모든 DB의 모든 객체를 포함한 MySQL 서버 전체를 의미

# 'user'@'localhost' 계정에 모든 DB에 대한 SUPER 권한 부여
GRANT SUPER ON *.* TO 'user'@'localhost';

DB 권한 부여

DB 권한은 ON 절에 모든 DB 또는 특정 DB를 명시할 수 있음

단, 특정 DB의 특정 테이블까지 명시할 수는 없음

# 'user'@'localhost' 계정에 모든 DB에 대한 EVENT 권한 부여
GRANT EVENT ON *.* TO 'user'@'localhost';

# 'user'@'localhost' 계정에 test DB에 한 EVENT 권한 부여
GRANT EVENT ON test.* TO 'user'@'localhost';

테이블 권한

테이블 권한은 모든 DB, 특정 DB, 특정 DB의 특정 테이블까지 명시 가능

특정 컬럼을 지정해서 권한을 부여할 수 있는데, 해당 권한 뒤에 컬럼을 명시해야 됨

다만 테이블/컬럼 단위의 권한을 사용하게 되면 세세한 권한 체크를 하기 때문에 전체적인 성능에 영향을 미칠 가능성이 있음

따라서 테이블에서 권한을 허용하고 컬럼에 대한 별도의 뷰를 만들어 사용하는 대체 방법이 있음

GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON test.* TO 'user'@'localhost';

# 'user'@'localhost' 계정에 test.account 테이블에 대한 SELECT, INSERT, UPDATE, DELETE 권한 부여
GRANT SELECT, INSERT, UPDATE, DELETE ON test.account TO 'user'@'localhost';

# 'user'@'localhost' 계정에 test.account 테이블에 대한 SELECT, INSERT, UPDATE 권한 부여 및 nickname 대한 DELETE 권한 부여
GRANT SELECT, INSERT, UPDATE, DELETE(nickname) ON test.account TO 'user'@'localhost';

Privilege Check

각 권한에 대한 관련 테이블

mysql.user: 계정 정보, 계정/역할에 부여된 글로벌 권한

mysql.db: 계정/역할에 DB 단위로 부여된 권한

mysql.tables_priv: 계정/역할에 테이블 단위로 부여된 권한

mysql.columns_priv: 계정/역할에 컬럼 단위로 부여된 권한

mysql.procs_priv: 계정/역할에 스토어드 프로그램 단위로 부여된 권한

mysql.global_grants: 계정/역할에 부여된 동적 글로벌 권한

Role

권한을 묶어 놓은 것으로 역할의 이름으로 용도를 나타낼 수 있고 재사용될 수 있음

기본 역할, 역할 그래프 조회

mysql.default_roles: 계정 별 기본 역할

mysql.role_edges: 역할에 부여된 역할 관계 그래프

역할 생성

CREATE ROLE role_test_read, role_test_write; # 생성할 역할 이름(여러 개 가능)

역할에 권한 부여

GRANT SELECT ON test.* TO role_test_read;

GRANT INSERT, UPDATE, DELETE ON test.* TO role_test_write;

계정에 역할 부여

GRANT role_test_read TO test_reader@'127.0.0.1';

GRANT role_test_write TO test_writer@'127.0.0.1';

현재 로그인한 계정의 역할 확인 및 역할 자동 부여 옵션 설정

MySQL은 역할을 자동적으로 부여해주지 않음

따라서 수동으로 부여를 하거나 로그인 시 자동으로 역할을 부여하는 옵션을 설정하면 됨

mysql> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

# 수동 역할 부여
SET ROLE 'role_test_read';

# 자동 역할 부여
SET GLOBAL activate_all_roles_on_login=ON;

mysql> SELECT current_role();
+----------------------+
| current_role()       |
+----------------------+
| `role_test_read`@`%` |
+----------------------+
1 row in set (0.00 sec)

재밌는 사실

MySQL은 ROLE과 USER는 동일한 것으로 취급함

보안을 강화하는 측면에서 둘을 구분하여 사용할 수 있음

계정 정보가 담겨 있는 mysql.user 테이블을 조회해보면 역할도 같이 출력됨

역할도 host를 가지고 있는데, 역할로써 사용한다면 호스트 부분은 어떻든 상관이 없음

mysql> select user, host, account_locked from mysql.user;
+------------------+-----------+----------------+
| user             | host      | account_locked |
+------------------+-----------+----------------+
| role_test_write  | %         | Y              |
| role_test_read   | %         | Y              |
| reader           | 127.0.0.1 | N              |
| writer           | 127.0.0.1 | N              |
| mysql.infoschema | localhost | Y              |
| mysql.session    | localhost | Y              |
| mysql.sys        | localhost | Y              |
| root             | localhost | N              |
+------------------+-----------+----------------+
8 rows in set (0.00 sec)