MySQL user 생성/권한부여/삭제

mysql user 생성/권한 부여/삭제

* 권한 설정 오류 시 : user 테이블의 Grant_priv 권한 확인.

1. user 생성

>> create user '아이디'@'localhost' identified by '비밀번호';
     localhost or % ( : 원격 접속가능)

2.  user 권한 부여
>> grant select, insert, update, delete, create, drop, alter on DB명.* to '아이디'@'localhost';
     all privileges : 모든 권한 / *.* : 모든DB / with grant option : 권한위임가능

3. user 권한 보기
>> show grants for '아이디'@'localhost(또는%)';

+----------------------------------------------------------------------------------------------------------------------------------+

| Grants for user@1.111.111.1                                                                                                               |

+----------------------------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'user'@'1.111.111.1' IDENTIFIED BY PASSWORD '****'                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON `pravs`.* TO 'user'@'1.111.111.1'  |
+--------------------------------------------------------------------------------------------------------------------+

4. 권한 적용
>> flush privileges;

5. 모든 user 보기 , mysql DB의 user table
>> use mysql;
>> select user from user;
>> select Host,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Grant_priv,Process_priv from user;

6. user 삭제
>> drop user '아이디'@'접속영역(localhost or %)';