我叫张贺，贪财好色。一名合格的LINUX运维工程师，专注于LINUX的学习和研究，曾负责某中型企业的网站运维工作，爱好佛学和跑步。
个人博客：传送阵
笔者微信：zhanghe15069028807
MYSQL运行时，应该遵循以下准则：
- 不要给开发配置超级用户权限
- 不要在数据库当中存储明文密码
- 不要用不满足复杂性要求的密码
- 不允许非信任主机的扫描（需安全设备配合）
1、用户账户管理
1.登录和退出 MySQL , 使⽤ mysql -u root -p 可以连接数据库, 但这只是本地连接数据库的⽅式,
在⽣产很多情况下都是连接⽹络中某⼀个主机上的数据库
-P //指定连接远程数据库端⼝[默认3306]
-h //指定连接远程数据库地址[默认localhost]
-u //指定连接远程数据库账户[默认root]
-p //指定连接远程数据库密码[默认密码为空]
-e //执⾏mysql数据库sql指令,非交互操作
上面几个选项最后一个-e我们会经常在脚本里面使用，举个例子:
mysql -u root -p'abc-123' -e "show databases;" | sed 1d | egrep -v 'information_schema|mysql|test|performance_schema'
bgx
bgx_edu
2、创建用户
//先用create创建用户，然后再用grant进行授权
create user zhanghe@'localhost' identified by 'cba-123';
grant all on *.* to zhanghe@'localhost';
//创建用户和授权可以放到一起
grant all on *.* to zhangjia@'localhost' identified by 'cba-123';
3、删除用户
//用`drop user`删除
drop user zhanghe@'localhost';
//delete语句删除
delete from mysql.user where user='zhangjia';
4、修改root用户密码
//用shell方式修改
mysqladmin -uroot -p'abc-123' password 'cba-123';
//用updata直接修改数据表，别忘记刷新
update mysql.user set password=password("cba-123") where user='root' and host='localhost';
//在当前用户下直接修改密码
set password=password("cba-123");
5、修改其它用户密码
//用`set password`指令修改
create user zhangsan@'localhost' identified by 'cba-123';
set password for zhangsan@'localhost'=password('NEW-passwd');
//用`updata`直接修改`mysql.user`表，别忘记刷新
update mysql.user set password=password("cba-123") where user='zhangsan' and host='localhost';
2、访问权限管理
1、权限表
mysql.user全局授权
mysql.db数据库级
mysql.tables_priv表级别
mysql.columns_priv列级
权限应用顺序：user>db>tables>columns
3、例子
//例一、下面这个是全局级别的授权
grant ALL ON *.* to zhanghe@'%' identified by 'cba-123';
select * from mysql.userG #通过此条命令可以看到权限
//例二、全局级别，只不过比例一多一个`GRANT`权限而已。
grant ALL ON *.* to zhanghe@'%' identified by 'cba-123' with grant option;
//例三：库级别
rant ALL on bgx.* to admin02@'%' identified by 'cba-123';
select * from mysql.userG #通过这个表发现admin02没有任何权限，因为根本没有存放在此
select * from mysql.db; #在这
//例四：表级别
grant ALL on bgx.t5 to admin01@'%' identified by 'cba-123';
select * from mysql.tables_privG
//例五，列级别
grant select(id),insert(name) on bgx.t5 to lisi@'%' identified by 'cba-123';
select * from columns_privG
3、访问权限回收
1、查看用户的权限
//查看root用户的权限
MariaDB [mysql]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*D1BE934B99C7CFFE2843BDE71DB34BBB0894B2DD' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
//查看其它用户的权限
MariaDB [mysql]> show grants for lisi@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for lisi@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lisi'@'%' IDENTIFIED BY PASSWORD '*D1BE934B99C7CFFE2843BDE71DB34BBB0894B2DD' |
| GRANT SELECT (id), INSERT (name) ON `bgx`.`t5` TO 'lisi'@'%' |
+-----------------------------------------------------------------------------------------------------+
2、权限回收
//回收用户的DELETE权限
revoke DELETE on *.* from zhanghe@'%';
//回收用户所有的权限
revoke ALL on *.* from zhanghe@'%';
//回收grant权限
revoke GRANT OPTION on *.* from zhanghe@'%';