mysql用户权限管理相关表

mysql中存在4个用户及权限相关的表，分别为user、db、tables_priv、columns_priv。
user表存放用户账号以及全局权限信息。
db表存放用户数据库级别的权限，例如哪些主机哪些用户可以访问哪个数据库。
tables_priv表存放表级别的权限，例如哪些主机哪些用户可以访问数据库的哪个表。
columns_priv表存放列级别的权限，例如哪些主机哪些用户可以访问数据库表的哪些字段。

添加用户

//创建用户名为xuxd，密码为xuxd123的用户
create user xuxd identified by 'xuxd123';

授权用户

//将db1库的所有操作权限授权给用户xuxd
//若是要授权所有库的权限 db1.* 改为 *.*
//若是要限制某个ip才能访问 xuxd@'%'改为 xuxd@'ip地址'，%代表允许所有的ip远程登录
grant all privileges on db1.* to xuxd@'%' identified by 'xuxd123';

查看所有用户情况

select * from mysql.user

修改密码

update mysql.user set authentication_string = password('xuxd123？') where user = 'xuxd' and host = '%';

删除用户

//删除用户以及对应的权限，执行命令后user表和db表的相应记录都会消失
drop user xuxd@''

更新权限

flush privileges;

事实上，直接用grant授权给某个用户的时候，如果该用户不存在，mysql会自动生成用户信息。

以上命令皆运行于5.7.14版本，其他版本未测试

sql注入攻击

说到安全性问题，首当其冲必定是sql注入问题.所谓的sql注入，就是攻击者在提交数据的时候，会在里面加入一些特殊字符，达到获取数据库访问权限或者破坏数据的目的。
例如，登录功能

sql = "select * from user_table where username=
' "+userName+" ' and password=' "+password+" '";

当用户输入的username = '’or 1 = 1 -- and password='’

sql语句变成
SELECT * FROM user_table WHERE username=
'’or 1 = 1 -- and password='’
条件后面username='' or 1=1 是必定成立的，--是注释后面的sql语句，所以攻击者就轻松绕过了密码验证

那么如何防御sql注入呢，简单的说，就是不要相信任何用户提交过来的数据，具体如下

1. 严格按照字段所需的格式进行格式化，比如数字就格式化为数字接受，字符串几格式化为字符串接收。
2. 使用addslashes函数来将单引号、双引号、反斜杠、null等字符前面加上斜杆，使其不具有特殊字符意义。
3. 使用htmlspecialchars方法来对用户提交的数据进行解析。
4. 绑定变量，使用预编译语句防止注入。

xss跨站攻击

跨站脚本攻击(Cross Site Scripting)，为不和层叠样式表(Cascading Style Sheets, CSS)的缩写混淆，故将跨站脚本攻击缩写为XSS。恶意攻击者往Web页面里插入恶意Script代码，当用户浏览该页之时，嵌入其中Web里面的Script代码会被执行，从而达到恶意攻击用户的特殊目的。
防范xss攻击也很简单，一样不要相信用户提交的任何数据，把用户所有的数据都加个htmlspecialchars处理。

csrf跨站伪造请求

跨站伪造请求是攻击者通过一些技术手段欺骗用户去访问曾经认证过的网站，并运行一些操作。可以理解为攻击者盗用了你的身份，以你的名义发送恶意请求。
主要步骤是

• 用户登录网站A，并在本地生成cookie。
• 在不登出网站A的情况下，访问危险网站B。

那么如何防御csrf攻击呢

• 最简单的方法应该是加入页面token了，即每个页面都生成一个随机的token，服务端接收操作指令的时候验证一下。
• 使用cookie hashing的方式，即每个表单都包含同一个随机数，然后在服务端进行hash验证。
• 增加隐藏字段验证，或者表单结构验证。

现实中很多项目的瓶颈最终都会落在数据库上。
刨除缓存和服务器配置差异，同样的配置，好的数据库设计能比劣质设计性能高出n倍，视觉上也会更有美感。

不讨论过多的细节，对于数据库优化主要有一下几个要点：

1、数据库结构优化：分库、分表、分区

• 分区是一种简单的水平拆分，只需要在表结构设计时加上分区参数，不需要代码管理。分区可以让单表的存储量更多，也可以提高检索的速度。
• 分表就是把一张大表，按照不同的规则分成多张小表，通常有垂直拆分和水平拆分两种，适合各个表之间关联度不高的场景。
• 分库就是把一个数据库按照不同的规则或者业务需求分成多个库，像游戏的不同服、saas平台的不同商家端。

2、表结构优化

• 使用可存下数据的最小数据类型
• 尽可能不用null，数组可以用0代替，字符串可以用''代替
• 尽量少用text,非用不可时尽量放在分表
• 单表不要有太多字段，建议20个以内
• 确定长度的字段用固定长度表，检索速度更快
• 同一模块的表尽可能使用相同的前缀，表名尽可能表达含义
• 所有字段名称都用小写字母，多词用"_"连接

3、索引优化

• 选择离散度大的列做索引
• 索引字段越小越好
• 适当建立联合索引，离散度大的列放在联合索引前面
• 索引不是越多越好，建立索引要适当

4、sql语句优化

• 查看慢查询日志
• 用explain分析sql的执行计划
• 减少大表之间的join
• 避免用select *，列出需要的查询字段即可
• 避免or连接词，可以的话用IN代替
• 避免在sql语句中进行列运算
• 避免使用左侧模糊查询

5、存储引擎优化

• 无特别要求存储引擎都使用innodb
• innodb为行锁插入性能更优
• 如果事务操作里面有包含mysiam引擎的表，会使事务失

另外就是一些比较琐碎的优化项了，比如，尽量不在循环中进行数据库操作，尽量减少查询数据库的次数、添加数据库连接池、必要的时候使用中间件解决异表间的搜索排序问题。

众所周知，操作系统的cache和buff机制极大的提升了系统运行效率，
cache是介于cpu和内存之间的缓冲区，主要用于读缓冲，
buff是介于硬盘和内存之间的缓存区，主要用于写缓存，
然鹅，如果cache和buff占用的内存过高也会影响系统运行速度，
我们可以通过以下命令查看缓存占用情况

free -mh

如下图，该操作系统总内存1.8G，缓存就占了1.4G

执行以下命令清理缓存空间

echo 3 > /proc/sys/vm/drop_caches

以下是清理完后的状况

缓存就降到了251M