[MariaDB] 사용자, 서비스, DBA 계정 생성 정리

🚀  들어가며...

우선 전제조건이 필요하다.

1. 172.31.128.177  : 개발자, DBA의 IP
2. 172.31.96.% : 실서버 IP 대역

위 정보를 토대로 만들어보자.

 

---

1. 사용자 계정 생성

###########################################
## 사용자 계정
###########################################
create user 'rojae'@'172.31.128.177' identified by 'password'; 
GRANT SELECT, INSERT, UPDATE, DELETE ON testdb.* TO 'rojae'@'172.31.128.177' IDENTIFIED BY 'password';
flush privileges;

개발자 PC에서 접속할 수 있는 계정입니다.

---

2. 서비스 계정 생성

서버에서 서비스를 위해서 사용하는 계정입니다.

###########################################
## 서비스 계정
###########################################

# [API, Batch] 서비스 계정
# client-ip 등록
create user 'APIACCOUNT'@'172.31.96.%' identified by 'password1234'; 
GRANT SELECT, INSERT, UPDATE, DELETE ON testdb.* TO 'APIACCOUNT'@'172.31.96.%' IDENTIFIED BY 'password1234';
flush privileges;

# [Admin] 관리자 서비스 계정
# client-ip 등록
create user 'ADMIN'@'172.31.96.%' identified by 'password1234'; 
GRANT SELECT, INSERT, UPDATE ON testdb.* TO 'ADMIN'@'172.31.96.%' IDENTIFIED BY 'password1234';
flush privileges;

 

이때 만약 Maxscale을 사용하고 있다면

반드시 host-ip와 client-ip를 모두 추가해야한다.

링크 : https://mariadb.com/kb/en/mariadb-maxscale-6-setting-up-mariadb-maxscale/

Maxscale 공식 가이드

 

 

아래 처럼 추가를 하자.

###########################################
## 서비스 계정 (Maxscale 사용)
###########################################

# [API, Batch] 서비스 계정
# host-ip 등록
create user 'ADMIN'@'host-ip' identified by 'password1234'; 
GRANT SELECT, INSERT, UPDATE ON testdb.* TO 'ADMIN'@'host-ip' IDENTIFIED BY 'password1234';
flush privileges;

# [Admin] 관리자 서비스 계정
# host-ip 등록
create user 'ADMIN'@'host-ip' identified by 'password1234'; 
GRANT SELECT, INSERT, UPDATE ON testdb.* TO 'ADMIN'@'host-ip' IDENTIFIED BY 'password1234';
flush privileges;


# [Maxscale] DB Proxy 계정
# client-ip 등록
create user 'maxscale'@'172.31.96.%' identified by 'maxscale1234'; 
GRANT SELECT ON mysql.user TO 'maxscale'@'172.31.96.%' IDENTIFIED BY 'maxscale1234';
GRANT SELECT ON mysql.db TO 'maxscale'@'172.31.96.%' IDENTIFIED BY 'maxscale1234';
GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'172.31.96.%' IDENTIFIED BY 'maxscale1234';
GRANT SELECT ON mysql.columns_priv TO 'maxscale'@'172.31.96.%' IDENTIFIED BY 'maxscale1234';
GRANT SELECT ON mysql.procs_priv TO 'maxscale'@'172.31.96.%' IDENTIFIED BY 'maxscale1234';
GRANT SELECT ON mysql.proxies_priv TO 'maxscale'@'172.31.96.%' IDENTIFIED BY 'maxscale1234';
GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'172.31.96.%' IDENTIFIED BY 'maxscale1234';
GRANT SHOW DATABASES ON *.* TO 'maxscale'@'172.31.96.%' IDENTIFIED BY 'maxscale1234';

# Maxscale DB Proxy 계정
# host-ip 등록
# 공식 홈페이지 참조 : https://mariadb.com/kb/en/mariadb-maxscale-6-setting-up-mariadb-maxscale/
create user 'maxscale'@'host-ip' identified by 'maxscale1234'; 
GRANT SELECT ON mysql.user TO 'maxscale'@'host-ip' IDENTIFIED BY 'maxscale1234';
GRANT SELECT ON mysql.db TO 'maxscale'@'host-ip' IDENTIFIED BY 'maxscale1234';
GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'host-ip' IDENTIFIED BY 'maxscale1234';
GRANT SELECT ON mysql.columns_priv TO 'maxscale'@'host-ip' IDENTIFIED BY 'maxscale1234';
GRANT SELECT ON mysql.procs_priv TO 'maxscale'@'host-ip' IDENTIFIED BY 'maxscale1234';
GRANT SELECT ON mysql.proxies_priv TO 'maxscale'@'host-ip' IDENTIFIED BY 'maxscale1234';
GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'host-ip' IDENTIFIED BY 'maxscale1234';
GRANT SHOW DATABASES ON *.* TO 'maxscale'@'host-ip' IDENTIFIED BY 'maxscale1234';

flush privileges;

 

---

3. DBA 계정

딱히 제한할게 없어, 모든 권한을 주었다. (DBA 계정은 maxscale 사용하지 않음)

###########################################
## DBA 계정
###########################################
# DBA 계정
create user 'dba'@'172.31.128.177' identified by 'password1234';
grant all privileges on *.* to 'dba'@'172.31.128.177' identified by 'password1234' with grant option;
flush privileges;

# DBA 로컬 계정 (MYSQL DUMP를 위함)
create user 'dba'@'localhost' identified by 'password1234';
grant all privileges on *.* to 'dba'@'localhost' identified by 'password1234' with grant option;
flush privileges;

 

---

4. 기타 명령어

###########################################
## 기타
###########################################
# 권한 조회
show grants for 'rojae'@'%';

# 계정 삭제
delete from mysql.user where User ='rojae';
flush privileges;

 

---

🙋🏻‍♂️ 후기

편의 상 정리한 글입니다.

Maxscale 사용의 경우, 약간의 주의가 필요합니다.