MySQL 权限与安全

1、权限系统的工作原理
对于身份的认证，MySQL 是通过IP 地址和用户名联合进行确认的，例如MySQL 安装后默认创建的用户root@localhost 表示用户root 只能从本地（localhost）进行连接才可以通过认证，此用户从其他任何主机对数据库进行的连接都将被拒绝。也就是说，同样的一个用户名，如果来自不同的IP 地址，则MySQL 将其视为不同的用户。
对所有数据库都具有相同权限的用户记录并不需要记入db 表，而仅仅需要将user 表中的select_priv 改为“Y”即可。换句话说，user 表中的每个权限都代表了对所有数据库都有的权限。
当用户通过权限认证， 进行权限分配时， 将按照user?db?tables_priv?columns_priv 的顺序进行权限分配，即先检查全局权限表user，如果user 中对应权限为“Y”，则此用户对所有数据库的权限都为“Y”，将不再检查db、tables_priv和columns_priv；如果为“N”，则到db 表中检查此用户对应的具体数据库，并得到db 中为“Y”的权限；如果db 中相应权限为“N”，则检查tables_priv 中此数据库对应的具体表，取得表中为“Y”的权限；如果tables_priv 中相应权限为“N”，则检查columns_priv 中此表对应的具体列，取得列中为“Y”的权限。

2、账号管理
##创建账号
例1：创建用户z1，权限为可以在所有数据库上执行所有权限，只能从本地进行连接。
mysql> grant all privileges on *.* to z1@localhost;

例2：在例1 基础上，增加对z1 的grant 权限。
mysql> grant all privileges on *.* to z1@localhost with grant option;

例3：在例2 基础上，设置密码为“123”。
mysql> grant all privileges on *.* to z1@localhost identified by '123' with grant option;

例4：创建新用户z2，可以从任何IP 进行连接，权限为对test1 数据库里的所有表进行SELECT、UPDATE、INSERT 和DELETE 操作，初始密码为“123”。
mysql> grant select,insert,update,delete on test1.* to 'z2'@'%' identified by '123';

例5：授予SUPER、PROCESS、FILE 权限给用户z3@%。
mysql> grant super,process,file on *.* to 'z3'@'%';
因为这几个权限都属于管理权限，因此不能够指定某个数据库，on 后面必须跟“*.*”。
FILE 权限主要以下作用：
? 将数据库的信息通过SELECT …INTO OUTFILE…写到服务器上有写权限的目录下，作为文本格式存放。
? 可以将有读权限的文本文件通过LOAD DATA INFILE…命令写入数据库表。
PROCESS 权限能被用来执行“show processlist”命令，查看当前所有用户执行的查询的明文文本，包括设定或改变密码的查询。
SUPER 权限能执行kill 命令，终止其他用户进程。

kill用户的例子：
mysql> show processlist;
+----+------+-----------------+-------+---------+------+-------+------------------+
| Id | User | Host            | db    | Command | Time | State | Info             |
+----+------+-----------------+-------+---------+------+-------+------------------+
|  1 | root | localhost:45137 | mysql | Query   |    0 | init  | show processlist |
| 30 | z2   | localhost:45166 | test1 | Sleep   |    7 |       | NULL             |
+----+------+-----------------+-------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

mysql> kill 30;
Query OK, 0 rows affected (0.00 sec)
mysql> show full processlist;   ##显示完整SQL语句
+----+------+-----------------+------+---------+------+-------+-----------------------+
| Id | User | Host            | db   | Command | Time | State | Info                  |
+----+------+-----------------+------+---------+------+-------+-----------------------+
|  1 | root | localhost:60082 | NULL | Query   |    0 | init  | show full processlist |
+----+------+-----------------+------+---------+------+-------+-----------------------+
1 row in set (0.01 sec)

例6：只授予登录权限给z4@localhost。
mysql> grant usage on *.* to 'z4'@'localhost';
usage 权限只能用于数据库登录，不能执行任何操作。

##查看权限
账号创建好后，可以通过如下命令进行查看权限：show grants for user@host;
mysql> show grants for z1@localhost;
host 可以不写，默认是“%”。

对于MySQL 5.0 以后的版本，也可以利用新增的information_schema 数据库进行权限的查看：
mysql> select * from information_schema.SCHEMA_PRIVILEGES where grantee="'z1'@'localhost'";

##回收权限
收回z2@localhost 上的INSERT 和SELECT 权限：
mysql> revoke select,insert on *.* from z2@localhost;
usage 权限不能被回收，也就是说，REVOKE 用户并不能删除用户。

##修改密码
方法1：可以用mysqladmin 命令在命令行指定密码。
shell> mysqladmin -u user_name -h host_name password "newpwd"

方法2：执行SET PASSWORD 语句。下例中将账号'jeffrey'@'%'的密码改为'biscuit'。
mysql> SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');
如果是更改自己的密码，可以省略for 语句：mysql> SET PASSWORD = PASSWORD('biscuit');

方法3：还可以在全局级别使用GRANT USAGE 语句(在*.*)来指定某个账户的密码而不影响账户当前的权限。
mysql> GRANT USAGE ON *.* TO 'jeffrey'@'%' IDENTIFIED BY 'biscuit';

方法4：直接更改数据库的user 表。
UPDATE mysql.user SET Password = PASSWORD('bagel') WHERE Host = '%' AND User = 'francis';
注意：更改密码时候一定要使用PASSWORD 函数（mysqladmin 和GRANT 两种方式不用写，会自动加上）。

##删除账号
mysql> drop user z2@localhost;

3、MySQL修改root密码的多种方法
方法1： 用SET PASSWORD命令 
首先登录MySQL。 
格式：mysql> set password for '用户名'@'localhost' = password('新密码'); 
例子：
mysql> set password for 'root'@'localhost' = password('123'); 
mysql> set password for 'root'@'127.0.0.1' = password('123'); 
mysql> set password = password('123'); 

方法2：用mysqladmin 
格式：mysqladmin -u用户名 -p旧密码 password 新密码 
例子：
mysql> mysqladmin -uroot -proot password 'root' 
mysql> mysqladmin -uroot -proot -h 127.0.0.1 password '123456' -S /mydata/data/3306/mysql_3306.sock

方法3：用UPDATE直接编辑user表 
首先登录MySQL。 
mysql> use mysql; 
mysql> update user set password=password('root') where user='root' and host='localhost'; 
mysql> update user set password=password('root') where user='root';
mysql> flush privileges; 

方法4：在忘记root密码的时候，可以这样 
以windows为例： 
1. 关闭正在运行的MySQL服务。 
2. 打开DOS窗口，转到mysql\bin目录。 
3. 输入：mysqld_safe --skip-grant-tables --user=mysql & 回车。--skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。 
   多实例：mysqld_safe  --defaults-file=/mydata/data/3306/my.cnf --skip-grant-tables &
4. 再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysql\bin目录。 
   空密码登录：mysql -uroot -p -h 127.0.0.1 -P3306
5. 输入mysql回车，如果成功，将出现MySQL提示符 >。 
6. 连接权限数据库： use mysql; 。 
6. 改密码：update mysql.user set password=password("123") where user="root";（别忘了最后加分号） 。 
7. 刷新权限（必须步骤）：flush privileges;
8. 退出 quit。 
9. 注销系统，再进入，使用用户名root和刚才设置的新密码123登录。