添加用户
1. 允许本地访问的用户（127.0.0.1）
create user test1@localhost identified by '123456';
2. 允许外网IP访问的用户
create user 'test1'@'%' identified by '123456';
用户分配权限
授予用户在本地服务器对该数据库的全部权限
grant all privileges on dbname.* to test1@localhost;
授予用户通过外网IP对于该数据库的全部权限
grant all privileges on dbname.* to 'test1'@'%';
刷新权限
flush privileges;
查询用户的权限
mysql> show grants for test1;
+---------------------------------------------------+
| Grants for test1@% |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `test1`@`%` |
| GRANT ALL PRIVILEGES ON `MyDB`.* TO `test1`@`%` |
| GRANT ALL PRIVILEGES ON `common`.* TO `test1`@`%` |
| GRANT ALL PRIVILEGES ON `course`.* TO `test1`@`%` |
| GRANT ALL PRIVILEGES ON `dbname`.* TO `test1`@`%` |
+---------------------------------------------------+
5 rows in set (0.00 sec)
给用户全部权限
mysql> grant all privileges on *.* to 'test1'@'%' ;
Query OK, 0 rows affected (0.04 sec)
撤销用户的全部授权
mysql> revoke all privileges on *.* from 'test1'@'%' ;
Query OK, 0 rows affected (0.09 sec)
(上述权限移除, 如果登录的用户没有重新的登录, 原有的权限不会丢失, 刷新权限也没有用, 这个权限必须重新登录才见到效果)
仅仅给用户某个数据库的查询权限
mysql> grant select on common.* to 'test1'@'%';
Query OK, 0 rows affected (0.04 sec)
mysql> show grants for test1;
+-------------------------------------------+
| Grants for test1@% |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `test1`@`%` |
| GRANT SELECT ON `common`.* TO `test1`@`%` |
+-------------------------------------------+
2 rows in set (0.00 sec)