[DB] MySQL 계정관리

1. 등록된 계정확인 

SELECT user, host, plugin FROM user;

 

 

2. 계정생성

CREATE USER 'yjh'@'%' IDENTIFIED by '1234';

= 어디서든 접속가능

 

CREATE USER 'yjh'@'localhost' IDENTIFIED by '1234';

= 로컬에서 접속가능

 

CREATE USER 'yjh'@'192.168.0.1' IDENTIFIED WITH mysql_native_password by '1234';

= 192.168.0.1 IP 에서만 접속가능

 

 

3. 패스워드 변경

ALTER USER 'yjh'@'localhost' IDENTIFIED by 'test1234';

 

 

4. 계정삭제

DROP USER 'yjh'@'localhost';

 

 

5. 계정권한부여

GRANT ALL PRIVILEGES on db.* to 'yjh'@'localhost' identified by 'test1234';

= SELECT, INSERT, UPDATE, DELETE 권한 부여

 

GRANT select,insert on db.* to 'yjh'@'localhost' identified by 'test1234';

= SELECT, INSERT 권한 부여

 

 

6. 계정권한삭제

REVOKE ALL on practice1.* from 'yjh'@'localhost' ;

 

 

7. 권한부여 적용

FLUSH PRIVILEGES;