mysql] user생성, 권한부여

1.접속

mysql -u root -p

2. 조회

mysql> use mysql;

mysql> select host, user, password from user;

* host

- localhost : 내부접근

- % : 외부접근

3. 사용자 추가

create user [userid];

create user [userid]@[host] identified by '[password]';

* host : localhost / '%'

ex) create user 'my_user_id'@'%' identified by 'mypassword';

* 사용자 제거

drop user '[userid]';

delete from user where user = '[userid]';

4. 권한 부여

grant all privileges on [dbname].[table명] to [userid]@[host] identified by '[password]';

(모든 테이블에 권한을 주려면)

grant all privileges on [dbname] to [userid]@[host] identified by '[password]';

* host

- host : 192.168.X.X로 시작되는 모든 IP의 원격접속을 허용한다는 의미

- 혹은 '192.168.%' 와 같이 쓸 수 있음

ex) grant all privileges on my_db.table to my_user_id@host identified by 'mypassword';

ex) grant all privileges on my_db.table to my_user_id@'192.168.%' identified by 'mypassword';

5. 변경된 권한 적용

flush privileges;

* 권한 삭제

revoke all on [dbname].table from [userid]@host;

* 권한 확인

show grants for [userid]@host;

show grants for '[userid]'@'%';