권한 부여 권한 제거

권한 부여 

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON `DB`.* TO 'ID'@'192.168.xx.xxx';  Query OK, 0 rows affected (0.01 sec) mysql> select Select_priv,Insert_priv,Update_priv,Delete_priv,Alter_priv from db where User='ID' and Db='DB' \G *************************** 1. row *************************** Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y  Alter_priv: N 1 row in set (0.00 sec)

현시점에 필요한 권한을 부여한다.

mysql> GRANT SELECT, ALTER ON `DB`.* TO 'ID'@'192.168.xx.xxx'; Query OK, 0 rows affected (0.00 sec) mysql> select Select_priv,Insert_priv,Update_priv,Delete_priv,Alter_priv from db where User='ID' and Db='DB' \G *************************** 1. row *************************** Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y  Alter_priv: Y 1 row in set (0.00 sec)

전체 부여된 권한중에 필요없는것은 제거한다.

DB 운영중일 경우 이렇게 함으로서 전체 제거후에 재 부여할때 발생하는 에러를 최소화 할수 있다.

mysql> REVOKE INSERT, UPDATE, DELETE, EXECUTE ON DB.* FROM 'ID'@'192.168.xx.xxx'; Query OK, 0 rows affected (0.00 sec) mysql> select Select_priv,Insert_priv,Update_priv,Delete_priv,Alter_priv from db where User='ID' and Db='DB' \G *************************** 1. row *************************** Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Alter_priv: Y   1 row in set (0.00 sec)

+ 팁

전체권한 제거

mysql> REVOKE ALL PRIVILEGES ON DB.* FROM 'ID'@'192.168.xx.xxx';

+ 또 팁

방화벽에서 막혔을때

ERROR NO : 2003  ERROR MESSAGE : Can't connect to MySQL server on '192.168.xx.xxx'

DB에서 계정 허용이 안되어 있을때

ERROR NO : 1130 ERROR MESSAGE : Host '192.168.xx.xxx' is not allowed to connect to this MySQL server