http://mitny.tistory.com/212

http://2dubbing.tistory.com/13

http://link2me.tistory.com/431

---

MySQL 처음 설치 후 root 접속 안될 때

 

서버에 MySQL을 처음 설치하고 root로 접속을 하려고 했는데 다음과 같은 에러가 뜨면서 접속이 안됐다.

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

 

그래서 검색을 해봤는데 초기 비밀번호는 없으니 엔터쳐라 또는 초기 비밀번호는 root 다 등

여러가지 말들이 있어서 다 해봤지만 안됐다...

 

더 알아보니 초기 비밀번호 설정을 해줘야 한다는 것!

 

mysqladmin -u root -p password

 

 

 

 

root 계정의 패스워드로 쓸 패스워드를 입력하고 접속하면 된다!

 

 

 

 

---

 

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 '계정명'@'호스트';  를 입력합니다.

 

 

 

 

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

 

 

---

MySQL DB 생성/삭제 및 사용자 추가/삭제, 권한 부여

서버 관리를 하다보면 mysql 사용자 계정을 추가해 줄때가 있다.

 

MySQL 접속 및 데이터 베이스 추가

# mysql -u root -p

사용자 계정을 추가하기 전에 먼저 현재 생성된 사용자 계정을 확인한다.

mysql > use mysql;    // mysql database 선택
mysql > select host, user, password from user;    // user 테이블 살펴보기

 

사용자 추가 (권한추가)
mysql > create user 사용자ID;   // 사용자 추가

mysql > create user userid@localhost identified by '비밀번호';

// 사용자(user)를 추가하면서 패스워드까지 설정

기존에 사용하던 계정에 외부 접근 권한을 부여하려면, Host를 '%' 로 하여 똑같은 계정을 추가한다

mysql > create user 'userid'@'%' identified by '비밀번호';  // '%' 의 의미는 외부에서의 접근을 허용

 

 

다른 방법으로는

mysql > USE mysql;     // mysql database 선택
mysql > INSERT INTO user (Host, User, Password) VALUES ('localhost', '계정아이디', password('비밀번호'));
mysql > INSERT INTO user (Host, User, Password) VALUES ('%', '계정아이디', password('비밀번호'));
mysql > FLUSH privileges;

 

mysql > drop user '사용자ID'@localhost;    // 사용자 삭제

 

 

mysql > select * from user;    // 등록된 모든 사용자 ID 조회
mysql > delete from user where user = '사용자ID';      // 사용자 삭제
 

계정이 생성되었다면, 그 계정이 접근할 수 있는 데이터베이스를 생성하고 권한을 부여해야 한다

데이터베이스 생성

mysql > show databases;     // DB 목록 확인

 

 

msyql > create database DB명;    // 데이터베이스 생성

 

 

msyql > create schema DB명 default character set utf8; -- 둘중에 하나를 입력하면 DB 생성됨

mysql > create database DB명 default character set utf8;

mysql > create database DB명 default character set utf8 COLLATE utf8_general_ci;

mysql > CREATE DATABASE IF NOT EXISTS DB명 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

// default character set을 지정하지 않으면 한글이 깨져서 나오므로 주의해야 한다.

 

// default character set 변경

msyql > ALTER DATABASE DB명 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

 

msyql > drop database DB명;       // 데이터베이스 삭제

 

 

사용자에게 데이터베이스 사용권한 부여

MySQL은 사용자 이름, 비밀번호, 접속 호스트로 여러분을 인증한다. 

MySQL은 로그인을 시도하는 위치가 어디인가 하는 것도 인증의 일부로 간주한다.

MySQL 에서 사용자 계정을 추가하고 권한을 추가하거나 제거하는 데 GRANT 와 REVOKE 명령을 사용하기를 권장한다. 

사용자에게 허가된 것을 확인하려면 SHOW GRANTS 를 사용한다.

IDENTIFIED BY '비밀번호'; 는 권한부여를 하면서 비밀번호까지 변경하고자 하는 경우

이므로 비밀번호는 변경하지 않으려면 이 부분을 빼면 된다.

 

mysql > GRANT ALL PRIVILEGES ON DB명.테이블 TO 계정아이디@host IDENTIFIED BY '비밀번호';

// 계정이 이미 존재 하는데 'identified by '비밀번호' 부분을 추가하면 비밀번호가 변경된다

mysql> GRANT ALL privileges ON DB명.* TO 계정아이디@locahost IDENTIFIED BY '비밀번호';
mysql> GRANT ALL privileges ON DB명.* TO 계정아이디@'%' IDENTIFIED BY '비밀번호';

 

mysql > grant all privileges on DB명.* to userid@'%' identified by '비밀번호' ;  //모든 원격지에서 접속 권한 추가 

host에 '200.100.%' 로 하면 IP주소가 200.100.X.X 로 시작되는 모든 IP에서 원격 접속을 허용한다는 의미

host에 '200.100.100.50' 으로 하면 IP주소가 200.100.100.50 인 곳에서만 원격 접속을 허용한다는 의미

 

mysql > grant all privileges on test.* to userid@localhost identified by '비밀번호';

// user 에게 test 데이터베이스 모든 테이블에 대한 권한 부여 

 

mysql> grant select, insert, update on test.* to user@localhost identified by '비밀번호';

// user 에게 test 데이터베이스 모든 테이블에 select, insert, update 권한 부여

 

mysql> grant select, insert, update on test.* to user@localhost;   -- 패스워드는 변경없이 권한만 부여하는 경우

// user 에게 test 데이터베이스 모든 테이블에 select, insert, update 권한 부여

 

 

mysql> grant all privileges on *.* to user@localhost identified by '비밀번호' with grant option;

// user 에게 모든 데이터베이스 모든 테이블에 권한 부여

// 전역 권한은 모두 광범위한 보안문제가 수반되므로 권한을 허용하는 경우 신중해야 함

 

mysql > flush privileges;     // 변경된 내용을 반영(권한 적용)

 

 

사용자에게 부여된 권한 확인

mysql > SHOW GRANTS FOR test@localhost;  -- userid 와 host명까지 붙여서 검색해야 함

 

mysql > SHOW GRANTS FOR test@'%';

mysql > SHOW GRANTS FOR test@'200.100.100.50';

 

 

사용자에게 데이터베이스 사용권한 제거

revoke all on DB명.테이블명 from 사용자ID;     // 모든 권한을 삭제

 

이제 다시 show grants 로 정보를 확인해보면

user 정보는 남아 있는데, 권한부여 정보는 삭제되고 없는 것이 보인다.

 

 

 

사용자 계정마저 삭제하고 권한 설정 정보를 확인하려고 하면 Error 가 발생하는 걸 확인할 수 있다.

 

 

사용자 계정 삭제

mysql > drop user userid@'%';

mysql > drop user userid@localhost;

 

 

위에 있는 것과 비교해서 host 가 % 로 된 것이 삭제되었다.

 

비밀번호 보안

 

 

MySQL 비밀번호가 일반 텍스트로 저장되지 않는다고 해서 비밀번호를 간단하게 만들어서는 안된다. MySQL 서버에 접속 가능한 사람은 누구든지 무차별 대입공격(brute-force) 방식으로 비밀번호를 알아내려고 시도해 볼 수 있으며, MySQL 은 유닉스 비밀번호에서처럼 이런 공격을 감지하고 막아낼 방도가 없다.