MySQL 사용자 관리 및 권한 설정

1. 사용자 생성

사용자 생성 (모든 IP에서 접속 가능)

-- 새로운 사용자 생성 (모든 IP에서 접속 가능)
CREATE USER 'username'@'%' IDENTIFIED BY 'password';

-- 예시
CREATE USER 'app_user'@'%' IDENTIFIED BY 'securepassword123';

💡 '@'%' 의미: 모든 호스트(IP)에서 접속 가능. RDS, Lambda, CloudShell 등 어디서나 접속할 수 있습니다.

사용자 목록 확인

-- 모든 사용자 조회
SELECT User, Host FROM mysql.user;

-- 특정 사용자 조회
SELECT * FROM mysql.user WHERE User = 'app_user'\G

출력 예시:

+----------+---------------+
| User     | Host          |
+----------+---------------+
| admin    | %             |
| app_user | localhost     |
| lambda_user | %          |
| root     | localhost     |
+----------+---------------+

---

2. 권한 부여

권한 종류

권한	설명
SELECT	데이터 조회
INSERT	데이터 삽입
UPDATE	데이터 수정
DELETE	데이터 삭제
CREATE	DB/테이블 생성
ALTER	DB/테이블 수정
DROP	DB/테이블 삭제
EXECUTE	저장 프로시저 실행
GRANT OPTION	다른 사용자에게 권한 부여 권한
ALL PRIVILEGES	모든 권한

특정 데이터베이스의 모든 권한

-- 특정 DB의 모든 권한 부여
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'%';

-- 예시
GRANT ALL PRIVILEGES ON myapp_db.* TO 'app_user'@'%';

전체 관리자 권한 부여 (최고 권한)

-- 모든 DB의 모든 권한 + 다른 사용자에게 권한 부여 가능
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

-- 반영
FLUSH PRIVILEGES;

-- 확인
SHOW GRANTS FOR 'username'@'%';

⚠️ 주의: 관리자 권한은 매우 위험합니다. 필요한 경우에만 신중하게 부여하세요.

특정 데이터베이스의 기본 권한 (권장)

-- SELECT, INSERT, UPDATE, DELETE만 가능 (가장 안전한 설정)
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'username'@'%';

-- 예시
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'app_user'@'%';

특정 테이블만 권한 부여

-- 특정 테이블의 권한만 부여
GRANT SELECT, INSERT, UPDATE ON database_name.table_name TO 'username'@'%';

-- 예시
GRANT SELECT, INSERT ON myapp_db.users TO 'app_user'@'%';

특정 컬럼만 권한 부여

-- 특정 컬럼에만 권한 제한
GRANT SELECT(id, name, email) ON database_name.table_name TO 'username'@'%';

-- 예시: users 테이블의 id, name, email 컬럼만 조회 가능 (password 컬럼은 안 됨)
GRANT SELECT(id, name, email) ON myapp_db.users TO 'app_user'@'%';

모든 DB의 권한 부여

-- 전체 DB의 권한 부여 (매우 제한적으로만 사용)
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'username'@'%';

권한 부여 후 반영

-- 권한 변경사항을 메모리에 반영
FLUSH PRIVILEGES;

---

3. 권한 확인

특정 사용자의 권한 조회

-- 사용자의 모든 권한 확인
SHOW GRANTS FOR 'username'@'host';

-- 예시
SHOW GRANTS FOR 'app_user'@'localhost';

-- 현재 접속한 사용자의 권한
SHOW GRANTS FOR CURRENT_USER;

출력 예시:

GRANT USAGE ON *.* TO 'app_user'@'localhost' IDENTIFIED BY PASSWORD 'xxxxx';
GRANT ALL PRIVILEGES ON `myapp_db`.* TO 'app_user'@'localhost';

권한 상세 확인

-- mysql.user 테이블에서 직접 확인
SELECT User, Host, Select_priv, Insert_priv, Update_priv, Delete_priv, 
       Create_priv, Drop_priv, Grant_priv FROM mysql.user 
WHERE User = 'app_user'\G

-- 결과
User              : app_user
Host              : localhost
Select_priv       : Y
Insert_priv       : Y
Update_priv       : Y
Delete_priv       : Y
Create_priv       : N
Drop_priv         : N
Grant_priv        : N

---

4. 권한 회수 (권한 제거)

특정 권한 회수

-- 특정 권한만 제거
REVOKE INSERT, UPDATE, DELETE ON database_name.* FROM 'username'@'%';

-- 예시: app_user에서 INSERT, UPDATE, DELETE 권한 제거 (SELECT만 남음)
REVOKE INSERT, UPDATE, DELETE ON myapp_db.* FROM 'app_user'@'%';

-- 반영
FLUSH PRIVILEGES;

모든 권한 회수

-- 특정 DB의 모든 권한 회수
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'%';

-- 예시
REVOKE ALL PRIVILEGES ON myapp_db.* FROM 'app_user'@'%';

-- 전체 권한 회수 (권한 부여 권한도 포함)
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'%';

-- 반영
FLUSH PRIVILEGES;

---

5. 실전 시나리오

시나리오 1: 웹 애플리케이션용 사용자 (읽기/쓰기)

CREATE USER 'webapp'@'%' IDENTIFIED BY 'webapp_password_123';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'webapp'@'%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'webapp'@'%';

시나리오 2: 읽기 전용 사용자 (Analytics용)

CREATE USER 'analytics'@'%' IDENTIFIED BY 'analytics_password_123';
GRANT SELECT ON myapp_db.* TO 'analytics'@'%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'analytics'@'%';

시나리오 3: Lambda 함수용 사용자

CREATE USER 'lambda_user'@'%' IDENTIFIED BY 'lambda_password_123';
GRANT SELECT, INSERT, UPDATE, DELETE ON lambda_db.* TO 'lambda_user'@'%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'lambda_user'@'%';

시나리오 4: 권한 축소하기

-- 현재 권한 확인
SHOW GRANTS FOR 'app_user'@'%';

-- UPDATE, DELETE 권한만 제거 (SELECT, INSERT만 남음)
REVOKE UPDATE, DELETE ON myapp_db.* FROM 'app_user'@'%';

-- 변경사항 반영 및 확인
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'app_user'@'%';

시나리오 5: 관리자 권한 부여

-- 특정 DB의 모든 권한 부여
CREATE USER 'dbadmin'@'%' IDENTIFIED BY 'admin_password_123';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'dbadmin'@'%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'dbadmin'@'%';

시나리오 6: 최고 관리자 권한 부여 (모든 DB)

-- 모든 DB의 모든 권한 + 다른 사용자에게도 권한 부여 가능
CREATE USER 'root_admin'@'%' IDENTIFIED BY 'root_admin_password_123';
GRANT ALL PRIVILEGES ON *.* TO 'root_admin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'root_admin'@'%';

---

6. 사용자 삭제

사용자 삭제

-- 특정 사용자 삭제
DROP USER 'username'@'%';

-- 예시
DROP USER 'app_user'@'%';

-- 존재하지 않으면 에러 무시
DROP USER IF EXISTS 'app_user'@'%';

사용자 삭제 전 권한 확인

-- 삭제 전에 해당 사용자의 권한 확인
SHOW GRANTS FOR 'app_user'@'%';

-- 권한 회수 (선택)
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app_user'@'%';

-- 사용자 삭제
DROP USER 'app_user'@'%';

---

7. 비밀번호 변경

사용자의 비밀번호 변경

-- 다른 사용자의 비밀번호 변경 (관리자만 가능)
ALTER USER 'username'@'%' IDENTIFIED BY 'new_password';

-- 예시
ALTER USER 'app_user'@'%' IDENTIFIED BY 'new_secure_password_456';

-- 현재 사용자의 비밀번호 변경
SET PASSWORD = 'new_password';

-- 반영
FLUSH PRIVILEGES;

---

8. 권한 설정 모범 사례

✅ 권장사항

-- 1. 웹 애플리케이션: SELECT, INSERT, UPDATE, DELETE만 부여
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'webapp'@'%';

-- 2. 데이터 분석: SELECT만 부여
GRANT SELECT ON myapp_db.* TO 'analytics'@'%';

-- 3. Lambda 함수: 필요한 DB만 권한 부여
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'lambda_user'@'%';

-- 4. 특정 테이블만 필요한 경우
GRANT SELECT, INSERT ON myapp_db.transactions TO 'app'@'%';

-- 5. 항상 FLUSH PRIVILEGES 실행
FLUSH PRIVILEGES;

❌ 피해야 할 것

-- 1. 일반 사용자에게 관리자 권한을 부여하지 말 것
-- GRANT ALL PRIVILEGES ON *.* TO 'webapp'@'%';  -- X

-- 2. 전체 DB 권한을 제한된 사용자에게 부여하지 말 것
-- GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'%';  -- X

-- 3. CREATE, ALTER, DROP 권한을 개발자에게 주지 말 것
-- GRANT CREATE, ALTER, DROP ON myapp_db.* TO 'dev'@'%';  -- X

-- 4. 약한 비밀번호 사용하지 말 것
-- CREATE USER 'app'@'%' IDENTIFIED BY '123456';  -- X
-- CREATE USER 'app'@'%' IDENTIFIED BY 'password';  -- X

-- 5. 보안 그룹과 함께 사용: @'%'은 DB 차원의 보안일 뿐
-- RDS 보안 그룹도 함께 설정해야 함

---

9. 빠른 참조

작업	명령어
사용자 생성	CREATE USER 'user'@'%' IDENTIFIED BY 'password';
사용자 목록	SELECT User, Host FROM mysql.user;
권한 부여	GRANT SELECT, INSERT ON db.* TO 'user'@'%';
권한 확인	SHOW GRANTS FOR 'user'@'%';
권한 회수	REVOKE INSERT ON db.* FROM 'user'@'%';
비밀번호 변경	ALTER USER 'user'@'%' IDENTIFIED BY 'new_password';
사용자 삭제	DROP USER 'user'@'%';
권한 반영	FLUSH PRIVILEGES;

---

10. CloudShell에서 사용자 관리

# RDS에 접속해서 사용자 생성
mysql -h RDS_ENDPOINT -u admin -p myapp_db

# 프롬프트에서:
# mysql> CREATE USER 'webapp'@'%' IDENTIFIED BY 'password';
# mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'webapp'@'%';
# mysql> FLUSH PRIVILEGES;
# mysql> SHOW GRANTS FOR 'webapp'@'%';
# mysql> exit;

스크립트로 자동화

# create_users.sql 파일 생성
cat > create_users.sql << 'EOF'
CREATE USER IF NOT EXISTS 'webapp'@'%' IDENTIFIED BY 'webapp_password_123';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'webapp'@'%';

CREATE USER IF NOT EXISTS 'analytics'@'%' IDENTIFIED BY 'analytics_password_123';
GRANT SELECT ON myapp_db.* TO 'analytics'@'%';

FLUSH PRIVILEGES;
EOF

# 스크립트 실행
mysql -h RDS_ENDPOINT -u admin -p myapp_db < create_users.sql