MYSQL 사용자(user) 호스트 / 권한 개념 정리

MYSQL에서 사용자(user)를 생성하고 사용하는 건 은근히 복잡합니다. 생성하는 구문 자체는 어렵지 않습니다. 하지만 호스트 연결과 권한에 대한 개념이 존재하기 때문에 처음엔 이해가 되지 않을 수 있습니다. 사용자는 특정한 호스트에서만 연결될 수 있고, 특정 데이터베이스만 사용할 수도 있습니다. 심지어 데이터베이스 내에 있는 테이블 별로도 다르게 권한을 부여할 수 있습니다. 사용자의 호스트 연결과 권한 개념에 대해 정리해보겠습니다.

MYSQL USER

1. 사용자 생성

사용자를 생성할 땐 계정명, 호스트, 비밀번호를 설정합니다.

create user '계정명'@'호스트' identified by '비밀번호';

 

1. localhost 연결에 대한 사용자 생성

예를 들어, localhost 연결에 대해 test라는 계정명, test123이라는 비밀번호로 생성하려면 아래와 같이 명령합니다. 

create user 'test'@'localhost' identified by 'test123';

 

localhost 연결이라는 의미는 mysql 서버가 설치된 컴퓨터에서 mysql에 접속하겠다는 의미입니다. 원격으로 접속하면 접속이 불가합니다.

 

2. 127.0.0.1 연결에 대한 사용자 생성

접근 호스트의 IP를 명시할 수도 있습니다. 127.0.0.1 연결에 대한 사용자 연결은 아래와 같이 명령합니다. 다른 IP를 넣어도 유효합니다. 

create user 'test'@'127.0.0.1' identified by 'test123';

 

localhost 연결과 다를바 없어보이지만 차이는 있습니다. 127.0.0.1은 루프백이기 때문에 TCP/IP 네트워크 프로토콜을 통해 로컬 컴퓨터에 접속합니다. 반면 localhost는 TCP/IP 스택을 거치지 않습니다.

 

3. 모든 호스트 연결에 대한 사용자 생성

%는 와일드카드로서 어떤 호스트에서든 명시한 계정에 대해 연결을 가능하게 합니다. 보안상 권장되는 방식은 아닙니다만 어디서든 원격으로 연결을 붙일 수 있기 때문에 편하다는 장점이 있습니다.

create user 'test'@'%' identified by 'test123';

 

2. 생성한 사용자 확인

MYSQL에서 생성한 사용자 목록은 mysql 데이터베이스의 user 테이블에 있습니다. 그중에서 user, host 컬럼을 조회하면 어떤 계정이 어떤 호스트 연결로 생성됐는지 볼 수 있습니다.

SELECT User, Host FROM mysql.user;

 

 

참고로 user 테이블은 열이 47개나 될만큼 많은 컬럼이 있습니다. 비밀번호, select, insert, update, delete, create, drop 등 SQL에 대한 실행 권한도 N 또는 Y로 볼 수 있습니다. 다만 이 테이블에 있는 권한은 특정 데이터베이스나 테이블이 아니라 모든 데이터베이스와 테이블에 대한 권한을 의미합니다.

 

 

3. 유저 권한 확인

유저 권한을 확인하려면 mysql.user 테이블보다는 아래의 구문으로 보는 게 낫습니다.

SHOW GRANTS FOR '계정명'@'호스트';

예를 들어, localhost 연결로 생성한 test 계정에 대한 권한을 확인해보면 USAGE 권한이 있는 걸 볼 수 있습니다. USAGE 권한은 사실상 아무런 권한이 없음을 의미합니다. 생성하고 아무런 권한을 주지 않았기 때문에 그렇습니다.

 

 

4. 권한 부여

계정에 권한을 부여하려면 GRANT 문을 사용합니다.

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

 

이제 localhost 연결 test 계정이 test 테이터베이스의 모든 테이블을 SELECT, UPDATE 할 권한을 부여하겠습니다.

GRANT SELECT, UPDATE ON test.* TO 'test'@'localhost';

 

다시 확인해보면 권한이 추가된 게 보입니다.

 

 

참고로 그냥 ALL로 권한을 주는 경우도 많습니다. 모든 권한을 부여하는 건데 보안상 좋지 못하지만 편합니다.

GRANT ALL ON test.* TO 'test'@'localhost';