Mysql 用户管理

 

-- 1 创建用户
create user latiny@'%' identified by '123456';

create user latiny@'localhost' identified by '123456';

create user latiny@'192.168.201.1' identified by '123456';
flush privileges;

-- 2 查看用户
use mysql;
select    * 
from    user
where    user = 'latiny'
and    
(
    host = '%'
    or    host = 'localhost'
    or    host = '92.168.201.1'
)
;

-- 3 删除用户
drop user latiny@'192.168.201.1';

drop user latiny@'localhost';

drop user latiny@'%';


-- 4 创建数据库
create database if not exists test2 default charset utf8 collate utf8_general_ci;

-- 5 将数据库授权给用户

-- 全部授权
grant all on test2.* to latiny@'192.168.201.1';
flush privileges;

-- 部分授权
grant select, insert, delete, update, drop on test2.* to latiny@'192.168.201.1';
flush privileges;


-- 6 将数据库所有权限赋给用户，该用户相当于管理员
grant all on *.* to latiny@'%';
flush privileges;

-- 7 查询用户在某个域具有的权限
show grants for latiny@'192.168.201.1';

show grants for latiny@'%';

show grants for latiny@'localhost';

-- 8 回收权限
revoke all on test2.* from latiny@'192.168.201.1';
flush privileges;

revoke select, insert, delete, update, drop on test2.* from latiny@'192.168.201.1';

 

9 权限说明

　　

 

　　管理权限（如 super， process， file等）不能够指定某个数据库，on后面必须跟 *.*

　　truncate权限就是create+drop，这点需要注意。

 

10 修改用户密码

update user 
set password=password('123456') 
where user='root' 
and host in ('%', 'localhost', '92.168.201.1');

FLUSH PRIVILEGES;