MySQL 添加用户 给用户添加权限

搬运出处: https://www.cnblogs.com/wuxunyan/p/9095016.html

1. 新建用户

格式:create user "username"@"host" identified by "password";

host="localhost" 允许本地登录，host="ip" 允许ip地址，host="%"，允许所以ip登录

mysql->create user 'test'@'localhost' identified by '123456';

mysql->create user 'test'@'192.168.1.11' identified by '123456'; 

mysql->create user 'test'@'%' identified by '123456';

2. 删除用户

格式：drop user 'username'@'host';

(5)授权

格式：grant privileges on databasename.tablename to 'username'@'host' IDENTIFIED BY 'PASSWORD';

　　/*授予用户通过外网IP对于该数据库的全部权限*/

　　grant all privileges on `test`.* to 'test'@'%' ;

　 /*授予用户在本地服务器对该数据库的全部权限*/

　　grant all privileges on `test`.* to 'test'@'localhost';   

   grant select on test.* to 'user1'@'localhost';  /*给予查询权限*/

   grant insert on test.* to 'user1'@'localhost'; /*添加插入权限*/

   grant delete on test.* to 'user1'@'localhost'; /*添加删除权限*/

   grant update on test.* to 'user1'@'localhost'; /*添加权限*/

　 flush privileges; /*刷新权限*/

 

/*授予用户通过外网IP对于该数据库的全部权限*/
grant all privileges on `test`.* to 'test'@'%' ;
/*授予用户在本地服务器对该数据库的全部权限*/
grant all privileges on `test`.* to 'test'@'localhost';   
grant select on test.* to 'user1'@'localhost';  /*给予查询权限*/
grant insert on test.* to 'user1'@'localhost'; /*添加插入权限*/
grant delete on test.* to 'user1'@'localhost'; /*添加删除权限*/
grant update on test.* to 'user1'@'localhost'; /*添加权限*/
flush privileges; /*刷新权限*/