Ubuntu MySQL설치&설정 - 3. MySQL 어카운트 권한 및 외부 접속

✔ 작업환경

 AWS EC2, Ubuntu 18.04.4, MySQL 5.7.3

 

✔ 목표

 MySQL을 설치 했으니 실제로 운영을 위해 아이디 발행 및 권한과 외부 접속 설정을 해보자.

1. 새 어카운트 생성

2. 생성한 아이디에 권한을 부여

 

 

✔ Start!

  우선 서버내에서 MySQL에 접속하자

mysql -u root -p

  잘 접속된다.

  이번엔 서버내 접속이 아닌 SQL툴을 이용해서 외부 접속이 가능한지 확인해보자. (Toad를 이용해보았다. 어떤툴이던 상관없다.)

 

 

  호스트에 연결할 수 없다!!!!

  이유는 우선 root어카운트의 호스트 접속 권한이 localhost로 제한 되어있기 때문이다. 

  

  서버로 돌아와 root어카운트의 권한을 확인해보자.

  mysql로그인 상태에서

SELECT User, Host FROM mysql.user;

  

  root의 호스트는 localhost로 설정 되어 있다. 이는 mysql이 설치되어있는 로컬 환경에서만 mysql접속을 허가한다는 것을 뜻한다.

 

  root어카운트에 외부 접속 권한을 부여할 수도 있으나 보안을 위해서는 추천하는 방법은 아니다.

 

 

 1️⃣ 새 어카운트 생성

CREATE USER 'goodea'@'%' IDENTIFIED BY 'password';

 ID : goodea

 비밀번호 : password

 접속호스트 : % 

 ❕접속호스트 부분에서 해당 어카운트의 외부 접속 허가 등을 설정 해줄 수 있다.

 

SAMPLE

-- 외부 접속 허용
CREATE USER 'goodea'@'%' IDENTIFIED BY 'password';

-- 로컬호스트 접속만 허용
CREATE USER 'goodea'@'localhost' IDENTIFIED BY 'password';

-- 특정 IP만 접속 허용
CREATE USER 'goodea'@'123.456.789.012' IDENTIFIED BY 'password';

 

권한을 변경해주었으니 플러시를 한번 해준다.

FLUSH PRIVILEGES;

 

발행한 어카운트로 SQL툴을 통해 접속을 확인해본다.

동일하게 접속에 문제가 발생한다면, 

 

Ububtu MySQL설치&설정 - ② MySQL초기 설정
에서 방화벽에 3306 포트 허용 및 외부 접속을 위한 LISTEN IP대역 변경이 잘 이루어졌는지,

또는 AWS서버에 DB를 설치한 경우 보안그룹에 접속하는 IP를 추가해주었는지 확인 해보자.

 

 

 

 2️⃣ 생성한 아이디에 권한을 부여

아이디 생성을 하였고 외부 접속 권한을 부여하였으나 실제로 접속해보면,

어떠한 작업도 할 수 없는 상태이다. 

 

 생성한 어카운트의 권한을 확인해보자.

SHOW GRANTS FOR 'goodea';

생성한 goodea어카운트의 권한을 확인해보면

+------------------------------------+
| Grants for goodea@%                |
+------------------------------------+
| GRANT USAGE ON *.* TO 'goodea'@'%' |
+------------------------------------+
1 row in set (0.00 sec)

위와 같이 어떠한 권한도 가지고있지 않다.

 

모든 권한을 부여해보자.

GRANT ALL PRIVILEGES ON *.* TO 'goodea'@'%' WITH GRANT OPTION; 

이후 다시 권한을 확인해보면

mysql> SHOW GRANTS FOR 'goodea';
+---------------------------------------------------------------+
| Grants for goodea@%                                           |
+---------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'goodea'@'%' WITH GRANT OPTION |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

권한이 변경된 것을 확인 할 수 있다.

 

스크립트에 따른 권한 차이 SAMPLE

-- test_db라는 DB에 한해서 SELECT, INSERT, UPDATE 권한만 부여
GRANT SELECT, INSERT, UPDATE test_db.* to 'goodea'@'%' WITH GRANT OPTION; 

-- test_db에 있는 test_table라는 테이블에서 address라는 컬럼에 대해서만 업데이트 권한 부여
GRANT UPDATE(address) test_db.test_table to 'goodea'@'%' WITH GRANT OPTION; 

 

역시 권한을 변경해주었으니 플러시를 한번 해준다.

FLUSH PRIVILEGES;

 

이렇게 MySQL의 설치부터 기본설정, 어카운트 생성 및 권한부여까지 사용에 있어 최소한의 설정을 끝마쳤다.