[MySQL] 사용자 추가, 권한 부여하기, 원격접속 허용 설정하기

MySQL에서 사용자를 추가하고 권한을 부여하는 방법을 알아보자.

 

 

Linux에서 MySQL에 접속하기 위해서는 사용자 계정을 생성하고,

권한을 부여한 후 사용하는것을 권장하고있다.

 

일반적으로 root는 localhost로만 접속가능하도록 설정하고

특정 database에 특정한 사용자만 접속되도록 한다.

 

먼저 순서를 나열하자면 아래와 같다.

1. database 생성

2. 사용자 생성

3. 사용자 권한부여

4. 사용자 권한삭제

5. 비밀번호 변경

6. 프로시저 권한 부여

 

순서대로 진행해 보자.

 

MySQL을 root권한으로 접속한다.

$ sudo mysql -u root -p

mysql 사용자로 접속한다.

use mysql;

1. database 생성 

mysql > CREATE DATABASE devdb;

2. 사용자 생성

기본생성

mysql > CREATE USER 'userId' identified by 'password';

원격접속 허용 사용자 생성 모든원격지에서 접속가능하도록 할때는 '%'를 붙여준다.

또 /etc/mysql/my.cnf 에서 bind-address 설정을 127.0.0.0 또는 주석 처리해야 원격접속이 가능하다.

mysql > CREATE USER 'userId'@'%' identified by 'password';

특정 아이피 대역에서 접속가능하게 할때는 IP 대역 뒤로 %를 사용한다.

mysql > CREATE USER 'userId'@'192.168.%' identified by 'password';

특정 아이피만 접속가능하게 할때는 IP전체를 써주도록 한다.

mysql > CREATE USER 'userId'@'192.168.xxx.xxx' identified by 'password';

서버가 클라우드형태로 존재하거나 원격지에서 접속해야할 경우

서버 아이피, 원격지에서 접속할 고정IP 또는 public ip를 추가하면 된다.

mysql > CREATE USER 'userId'@'121.xxx.xxx.xxx' identified by 'password';
mysql > CREATE USER 'userid'@'서버의 로컬 ip' identified by 'password';

이렇게 하면 was에서 접속하거나 원격지에서 접속하여 개발이 가능하다.

public ip는 네이버나 구글에 공인 아이피를 검색하면 쉽게 얻을수 있다.

 

공인아이피는 내부/외부에 공개되어있으니 사용시 방화벽작업을 하도록 하고,

가능하면 고정형 private ip를 사용하도록 하자

 

3. 사용자 권한부여

사용자에게 권한을 부여할때는 grant 명령어를 사용하고

권한을 제거할때는 revoke 명령어를 사용한다.

먼저 생성된 사용자에게 권한을 부여하도록 하자.

mysql > GRANT ALL PRIVILEGES ON devdb.table TO 'userId'@'서버의 로컬 ip';

이렇게 로컬로 접속하는 경우의 ( WAS 접속시 ) 권한을 추가하도록하자.

devdb의 모든 테이블에 모든 권한을 추가할때는 db.* 를 사용하도록 하자.

mysql > GRANT ALL PRIVILEGES ON devdb.* TO 'userId'@'192.168.xxx.xxx';

원격지에서도 접속하고, WAS를 통해서도 접속할경우 두 계정에 권한을 부여해야한다.

테이블에서 계정을 검색해보면 아래와 같다

mysql > SELECT host, user FROM user;

Host	User
localhost	root
%	userId
192.168.xxx.xxx	userId
서버의 로컬 ip	userId

 

4. 사용자 권한삭제

권한을 제거하는 명령어는 revoke 명령어를 사용하면된다.

mysql > REVOKE ALL PRIVILEGES ON devdb.* TO 'userId'@'192.168.xxx.xxx';

 

사용자의 권한확인

mysql > SHOW GRANTS FOR 'userId'@'192.168.xxx.xxx';

 

5. 사용자 비밀번호 변경 (mysql 5.7 이상)

mysql > ALTER USER 'userId'@'192.168.xxx.xxx' IDENTIFIED BY 'password'; 

 

6. PROCEDURE 권한 부여 및 확인

* 프로시저 확인

mysql > SHOW PROCEDURE STATUS;

* 프로시저 생성 및 수정 권한 부여

mysql > GRANT CREATE, ALTER RUOTINE devdb.* TO 'userId'@'192.168.xxx.xxx'; 

 

* 사용자 테이블, 권한 변경시 FLUSH 명령어로 마침

mysql > FLUSH PRIVILEGES;