(1)创建用户
mysql> insert into mysql.user(Host,User,Password) values("%","username",password("www1001"));
%可以远程访问(localhost本地访问)
注意事项 MySQL5.7 mysql.user表没有password字段改 authentication_string
mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'password';
mysql> CREATE USER 'long'@'localhost' IDENTIFIED BY '123456';
%可以远程访问(localhost本地访问)
(2)给username这个用户赋予testDB数据库的所有权限
(1)赋予某个用户的某个数据库所有权限
mysql> grant all privileges on 数据库名.* to '用户'@'%' identified by '密码';
列子:
赋予username用户的testDB数据库所有权限
mysql> grant all privileges on testDB.* to 'username'@'%' identified by 'www1001';
(2)赋予某个用户的所有数据库所有权限
mysql> grant all privileges on *.* to 'username'@'%' identified by 'www1001';
(3)赋予某些权限(多个使用,分割)
mysql> grant select,update,delete,insert on 数据库名.* to 用户名;
(3)刷新系统权限表(注意所有操作记得刷新)
mysql> flush privileges;
(4)如果想指定部分权限给一用户
mysql> grant select,update on testDB.* to username@'%' identified by 'www1001';
(select,delete,update,create,drop)
(5)授权用户拥有所有数据库的某些权限
mysql> grant select,delete,update,create,drop on *.* to username@"%" identified by "www1001";
(6)删除用户
mysql> Delete FROM user Where User='username' and Host='localhost';
删除账户及权限:mysql> drop user 用户名@'%';
mysql> drop user 用户名@ localhost;