MySQL 데이터베이스 및 사용자 관리

데이터베이스 관리

# 데이터베이스 생성
CREATE DATABASE 데이터베이스명;
CREATE DATABASE 데이터베이스명 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS 데이터베이스명;

# 데이터베이스 목록 확인
SHOW DATABASES;
SHOW DATABASES LIKE '패턴';

# 데이터베이스 선택
USE 데이터베이스명;

# 어느 데이터베이스에 있는지 알아보기
SELECT DATABASE();

# 데이터베이스 삭제
DROP DATABASE 데이터베이스명;
DROP DATABASE IF EXISTS 데이터베이스명;

사용자 관리

유저는 localhost용과 원격에서 접속하는 유저를 따로 생성한다. localhost에서 접속하는 유저는 서버

# 사용자 목록 확인
SELECT user, host FROM mysql.user;
SELECT user, host FROM mysql.user WHERE user = '사용자명';

# 사용자 생성
CREATE USER '사용자명'@'localhost' IDENTIFIED BY '비밀번호';
CREATE USER '사용자명'@'192.168.1.100' IDENTIFIED BY '비밀번호';  # 특정 IP
CREATE USER '사용자명'@'%' IDENTIFIED BY '비밀번호';  # 모든 IP
CREATE USER '사용자명'@'호스트명' IDENTIFIED BY '비밀번호';


# 사용자 비밀번호 변경
SET PASSWORD FOR '사용자명'@'localhost' = PASSWORD('새비밀번호');  # MySQL 5.7
ALTER USER '사용자명'@'localhost' IDENTIFIED BY '새비밀번호';  # MySQL 8.0 이상
FLUSH PRIVILEGES;

# 사용자 삭제
DROP USER '사용자명'@'localhost';
DROP USER '사용자명'@'localhost', '사용자명'@'%';
DROP USER IF EXISTS '사용자명'@'localhost';

권한 관리

주요 권한 종류

  • ALL PRIVILEGES: 모든 권한
  • SELECT: 데이터 조회
  • INSERT: 데이터 삽입
  • UPDATE: 데이터 수정
  • DELETE: 데이터 삭제
  • CREATE: 테이블/데이터베이스 생성
  • DROP: 테이블/데이터베이스 삭제
  • ALTER: 테이블 구조 변경
  • INDEX: 인덱스 생성/삭제
  • GRANT OPTION: 다른 사용자에게 권한 부여 가능
# 권한 확인
SHOW GRANTS;
SHOW GRANTS FOR '사용자명'@'localhost';
SELECT * FROM mysql.user WHERE user = '사용자명' AND host = 'localhost';
SELECT * FROM mysql.db WHERE user = '사용자명' AND host = 'localhost';
SELECT * FROM mysql.tables_priv WHERE user = '사용자명' AND host = 'localhost';

# 권한 부여
GRANT ALL PRIVILEGES ON 데이터베이스명.* TO '사용자명'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO '사용자명'@'localhost';
GRANT SELECT, INSERT, UPDATE ON 데이터베이스명.테이블명 TO '사용자명'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON 데이터베이스명.* TO '사용자명'@'localhost';
GRANT ALL PRIVILEGES ON 데이터베이스명.* TO '사용자명'@'%';  # 외부 접속
FLUSH PRIVILEGES;

# 권한 삭제
REVOKE ALL PRIVILEGES ON 데이터베이스명.* FROM '사용자명'@'localhost';
REVOKE ALL PRIVILEGES ON *.* FROM '사용자명'@'localhost';
REVOKE SELECT, INSERT ON 데이터베이스명.* FROM '사용자명'@'localhost';
REVOKE GRANT OPTION ON 데이터베이스명.* FROM '사용자명'@'localhost';
FLUSH PRIVILEGES;

실전 예제

# 웹 애플리케이션용 데이터베이스 및 사용자 생성
CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'myapp_user'@'%' IDENTIFIED BY '강력한비밀번호';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'myapp_user'@'%';

# 읽기 전용 사용자 생성
CREATE USER 'readonly_user'@'%' IDENTIFIED BY '비밀번호';
GRANT SELECT ON 데이터베이스명.* TO 'readonly_user'@'%';
FLUSH PRIVILEGES;

# 특정 IP에서만 접속 가능한 사용자 생성
CREATE USER 'admin_user'@'192.168.1.100' IDENTIFIED BY '비밀번호';
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'192.168.1.100';
FLUSH PRIVILEGES;

주의사항

  1. FLUSH PRIVILEGES: 권한 변경 후 반드시 실행하여 변경사항 즉시 적용
  2. 보안: 프로덕션 환경에서는 % 사용을 최소화하고 특정 IP만 허용
  3. 비밀번호: 강력한 비밀번호 사용 필수
  4. 최소 권한 원칙: 필요한 최소한의 권한만 부여
  5. MySQL 8.0: PASSWORD() 함수가 제거되어 ALTER USER 사용
  6. 호스트 지정: localhost는 로컬 접속만, %는 모든 IP에서 접속 가능
LinkedIn