[MySQL] 사용자 계정 생성 및 권한 관리 방법

사진: Unsplash 의 Steve Johnson

실행 환경

• MySQL 8.0
• Ubuntu 20.04

MySQL 접속하기

• localhost에서 root 계정으로 접속합니다.

# password 기반 인증을 사용하는 경우
mysql -u root -p

# auth_socket 인증을 사용하는 경우 localhost에서 비밀번호 없이 접속
sudo mysql

• 계정 인증 방식 확인하기 (plugin)
  • auth_socket: sudo mysql로 비밀번호 없이 접속 가능
  • mysql_native_password, caching_sha2_password: mysql -u root -p로 비밀번호 입력 후 접속 가능

SELECT user, host, plugin FROM mysql.user WHERE user = 'root';

+------+-----------+-----------------------+
| user | host      | plugin                |
+------+-----------+-----------------------+
| root | localhost | auth_socket           |
+------+-----------+-----------------------+

계정 관리

• 신규 사용자 생성

CREATE USER 'user_name'@'host' IDENTIFIED BY 'password';

# 모든 IP 접속 허용
CREATE USER 'user_name'@'%' IDENTIFIED BY 'password';

# 특정 IP 접속 허용
CREATE USER 'user_name'@'12.10.10.2' IDENTIFIED BY 'password';

• 사용자 계정 조회

SELECT user, host FROM mysql.user;

 

• 사용자 계정 삭제

DROP USER 'user_name'@'host';

계정 권한 관리

• 권한 조회

SHOW GRANTS FOR 'user_name'@'host';

SHOW GRANTS FOR 'myuser'@'%';

• 권한 부여

GRANT 권한 ON 데이터베이스.테이블 TO '계정이름'@'호스트';

# 모든 데이터베이스, 테이블에 대해 모든 권한 부여
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'%';

# 모든 데이터베이스, 테이블에 대한 권한과 다른 사용자에게 권한을 부여하는 권한도 설정
GRANT ALL PRIVILEGES ON *.* TO 'adminuser'@'%' WITH GRANT OPTION;

# 특정 데이터베이스의 모든 권한 부여
GRANT ALL PRIVILEGES ON mydatabase.* TO 'appuser'@'%';

# 특정 데이터베이스의 읽기 전용 권한 부여
GRANT SELECT ON mydatabase.* TO 'readonlyuser'@'%';

# 삭제를 제외한 권한 부여
GRANT SELECT, INSERT, UPDATE ON sales_db.orders TO 'salesuser'@'%';

# 특정 테이블에서 지정된 컬럼만 조회할 수 있도록 권한 부여
GRANT SELECT (name, department) ON company_db.employees TO 'analyticsuser'@'%';

# 특정 테이블의 입력 권한만 부여
GRANT INSERT ON logs_db.event_logs TO 'loggeruser'@'%';

# 특정 프로시저만 실행 가능한 권한 부여
GRANT EXECUTE ON PROCEDURE mydatabase.calculate_bonus TO 'procuser'@'%';

# 특정 데이터베이스에서만 사용자 계정을 관리할 수 있는 권한 부여
GRANT CREATE USER, GRANT OPTION ON project_db.* TO 'dbadmin'@'%';

# 모든 권한 제거 후 읽기 권한만 부여
REVOKE ALL PRIVILEGES ON *.* FROM 'testuser'@'%';
GRANT SELECT ON mydatabase.* TO 'testuser'@'%';

# 권한 변경 사항 적용 (MySQL 8.0 이상에서는 자동 적용됨)
FLUSH PRIVILEGES;

• 권한 삭제

REVOKE 권한 ON 데이터베이스.테이블 FROM '계정이름'@'호스트';

# 사용자의 모든 데이터베이스, 테이블에 대한 모든 권한 삭제
REVOKE ALL PRIVILEGES ON *.* FROM 'newuser'@'%';