mysql 用户和权限控制

  1. 添加用户
//ip连接
create user '新用户名'@'localhost' identified by '密码';
//ip连接%
create user '新用户名'@'%' identified by '密码';

  1. 为新用户授权
//
grant all privileges on . to '新用户名'@'指定ip' identified by '新用户密码' ;
//
//访
grant all privileges on *.* to '新用户名'@'指定ip' identified by '新用户密码' ;
//
grant all privileges on test.test to '新用户名'@'指定ip' identified by '新用户密码' ;
  1. 设置用户操作权限
//
grant all privileges on *.* to '新用户名'@'指定ip' identified by '新用户密码' WITH GRANT OPTION;
//
grant select on *.* to '新用户名'@'指定ip' identified by '新用户密码' WITH GRANT OPTION;
//,select查询 insert插入 delete删除 update修改
//
grant select,insert on *.* to '新用户名'@'指定ip' identified by '新用户密码' WITH GRANT OPTION;
//
REVOKE select ON what FROM '新用户名';
  1. 删除用户
DROP USER username@localhost;
  1. 刷新权限
FLUSH PRIVILEGES;

备注: information_schema 表的任意用户可见

mysql> show  grants for jkhl;
+------------------------------------------------------------+
| Grants for jkhl@%                                          |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jkhl'@'%'                           |
| GRANT SELECT ON `jkhldj`.* TO 'jkhl'@'%' WITH GRANT OPTION |
+------------------------------------------------------------+
2 rows in set (0.00 sec)