在mysql下mysql库中有6个权限表
mysql.user
用户字段，权限字段，安全字段，资源控制字段
mysql.db 、 mysql.host
用户字段，权限字段
mysql.tables_priv，mysql.columms_priv，mysql.procs_priv
一、用户管理
(1)创建用户的三种方法
.create user user1@'localhost' identified by '';
.insert into mysql.user(user,host,password,ssl_cipher,x509_issuer,x509_subject) values('user2','localhost',password(''),'','','');
.grant select on *.* to user3@'localhost' identified by '' //授select权所有库和所有表给user3，密码123
flush privileges
(2)删除用户
.drop user user1@'localhost'
.delete from mysql.user where user='user1' and host='localhost';
(3)root用户修改自己密码
.mysqladmin -uroot -proot password ''
.update mydql.user set password=password('new_password') where user='root' and host='localhost';
.set password=password('new_password')
flush privileges //刷新授权表
(4)root用户修改其他用户密码
.set password for user3@'localhost' =password('new_password');
flush privileges;
.updatae mysql.user set password=password('new_password') where user='user3' and host='localhost';
flush privileges;
.grant select on *.* to user3@'localhost' identified by 'pwd';
flush privileges;
(5)普通用户修改自己密码
set password=password('new_password');
(6)丢失root用户密码
vim /etc/my.cnf
skip-grant-tables//将这句话的注释去掉
service mysqld restart
mysql -uroot //然后就可以跳过权限表进入mysql
update mysql.user set password=password('new_password') where user='user3' and host='localhost';
flush privileges;
\q //退出mysql
vim /etc/my.cnf
#skip-grant-tables //将这句话再重新注释
二、权限管理
语法格式：grant 权限列表 on 库名.表名 to 用户名@'客户端' [identified by 'password' with grant option]
其中：with_option参数如下
grant option: 授权选项
max_queries_per_hour:定义每小时允许执行的查询数
max_updates_per_hou:定义每小时允许执行的更新数
max_connections_per-hour:定义每小时可以建立的连接数
max-user_connections:定义单个用户同是可以建立的连接数
授权示例
grant all on *.* to admin1@'%' identified by 'password';
grant all on *.* to admin2@'%' identified by 'pw' with grant option;
grant all on *.* bbs.* to admin3@'%' identified by 'pw';
grant all on bbs.user to admin4@'%' identified by 'pw';
grant select(col1),insert(col2,col3) on bbs.user to admin5@'%' identified by 'pw'; flush privileges
查看权限
show grants for admin@'%' \G;
回收权限 revoke 权限列表 on 数据库名 from 用户名@'客户端主机'
.revoke delete on *.* from admin@'%';//回收部分权限
.revoke all privileges on *.* from admin@'%';
revoke grant on *.* from admin@'%'; //回收全部权限(包括授权)
flush privileges; //刷新授权