mysql 유저 관리

이번 포스팅은 

MySQL 접속 계정 관리 와

계정 별 권한설정 방법에 대해 알아보겠습니다.

본문에서 다루는 MySQL 버전은 5.6 버전이며 

5.7 버전부터 일부 변경 된 사항이 있으므로 참고바랍니다!

mysql -u root -p 를 입력하여 MySQL 에 접속합니다.

그 다음, 스키마를 mysql 로 변경하겠습니다.

use mysql;  를 입력합니다. 

지금부턴 SQL 질의문 이기 때문에 마지막에 세미콜론(;) 을 붙여주셔야 합니다.

mysql 스키마에 테이블 구성을 알아보겠습니다.

show tables; 를 입력합니다.

여기서 계정에 관련 된 정보를 관리하는 테이블은 

user 테이블 입니다.

select host, user, password from user; 를 입력하여 

계정정보를 조회 해보겠습니다.

-------

※ 확인사항: MySQL 5.7 부터 user 테이블에 password 컬럼이 삭제되고 

authentication_string 컬럼으로 변경.

MySQL 5.7 Reference 참고 

https://dev.mysql.com/doc/refman/5.7/en/grant-tables.html 

현재는 root 계정밖에 존재하지 않습니다.

그럼 새로운 계정을 하나 추가 해보겠습니다.

create user '계정아이디'@localhost identified by '비밀번호';  를 입력합니다.

Query OK, 문구가 뜨면 이상없이 계정추가가 되었음 을 말합니다.

제대로 추가되었는지 

select host, user, password from user; 로 조회 해보겠습니다.

정상적으로 추가되었습니다.

헌데 방금 만든 계정은 localhost(로컬) 에서 만 접속할 수 있습니다.

즉, 외부IP 에서는 접속할 수 없습니다.

이 경우 host 를 '%'로 해주면 외부에서의 접속을 허용한다는 설정이 됩니다.

방금 전 create user '계정아이디'@localhost identified by '비밀번호';  에서

 localhost 부분을 지우고 '%'로 변경합니다.

create user '계정아이디'@'%' identified by '비밀번호'; 

host 를 '%' 로 주면 모든 외부 IP에서 접속할 수 있습니다.

만약 특정 IP 대역에서만 접속하게 설정하려면 'IP대역.%'  로 주면 됩니다.

예) '172.168.%'  -> IP 가 172.168.xxx.xxx  에서만 접속 가능

이번엔 계정을 삭제하는 방법에 대해

알아보겠습니다.

빨간 테두리가 계정 삭제를 하는 SQL 질의 입니다.

delete from user where user='dubbingLee';

계정ID가 dubbingLee 인 아이디를 찾아서 삭제.

정상적으로 삭제되었습니다.

아까는 특정IP 일 경우 접속가능하게 제한하는 설정만 해봤는데

해당 계정이 특정 스키마에 만 접근할 수 있게 권한을 부여할 수도 있습니다.

스키마 권한 제어에는 SQL 명령어 중 DCL (Data Control Language) 이 사용됩니다.

*DCL 종류에는 GRANT 와 REVOKE 이 있습니다.

create schema testDB; 를 입력하여

testDB 스키마를 만든 뒤, use testDB; 를 입력하여

사용 스키마 변경을 하겠습니다.

그 다음, grant all privileges on '스키마명'.'테이블명' to '계정명'@'호스트' identified by '계정비밀번호' with grant option; 을

입력하여 특정 스키마의 테이블에만 접근할 수 있는 계정을 만들겠습니다.

위 질의문은 dubbingLee 계정을 만들고, 외부 IP에서 접속가능하며,

 해당 계정권한을 testDB 스키마에 모든 테이블에서 select, insert, update, delete 할 수 있게 한다.

입니다.

*만약 계정에 select(조회) 권한만 주고 싶다면 다음과 같이 입력하면 됩니다.

grant select privileges on 'DB명'.'테이블명' to '계정명'@'%' identified by '계정비밀번호' with grant option;

flush privileges; 를 입력하여 권한 적용을 합니다.

권한이 제대로 부여됐는지 확인 해보겠습니다.

show grants for '계정명'@'호스트';  를 입력합니다.

조회한 계정의 권한이 나왔습니다. 

방금 위에서 설정한 권한이 제대로 적용된 걸 확인하였습니다.

계정에 부여된 권한을 제거하는 방법에 대해 알아보겠습니다.

revoke all on '스키마명'.'테이블명' from '계정명'@'호스트'; 를 입력합니다.

 

권한이 제대로 제거됐는지 확인 해보겠습니다.

show grants for '계정명'@'호스트';  를 입력합니다.

처음에 설정한 권한이 빠진 것을 확인하였습니다.

# 잘못된 내용이 있으면 댓글 남겨주세요~

출처: https://2dubbing.tistory.com/13 [비실이의 개발공간]