一、 MySQL的权限类型简介
MySQL数据库提供了3种不同层次的权限类型。
1） 管理权限。此类权限用来管理数据库服务器，这些权限是全局的，不单独针对特定的数据库。
2） 数据库级别权限。此类权限作用于某个指定数据库或者所有数据库及其内的所有对象。
3） 对象级别权限。此类权限仅对数据库内的对象级别，如表、视图、索引及存储过程等。
下表展示了MySQL中的各种权限，及其在权限表中的列名称和权限类型。
| Privilege | Column | Context |
| CREATE | Create_priv | databases, tables, or indexes |
| DROP | Drop_priv | databases, tables, or views |
| GRANT OPTION | Grant_priv | databases, tables, or stored routines |
| LOCK TABLES | Lock_tables_priv | databases |
| REFERENCES | References_priv | databases or tables |
| EVENT | Event_priv | databases |
| ALTER | Alter_priv | tables |
| DELETE | Delete_priv | tables |
| INDEX | Index_priv | tables |
| INSERT | Insert_priv | tables or columns |
| SELECT | Select_priv | tables or columns |
| UPDATE | Update_priv | tables or columns |
| CREATE TEMPORARY TABLES | Create_tmp_table_priv | tables |
| TRIGGER | Trigger_priv | tables |
| CREATE VIEW | Create_view_priv | views |
| SHOW VIEW | Show_view_priv | views |
| ALTER ROUTINE | Alter_routine_priv | stored routines |
| CREATE ROUTINE | Create_routine_priv | stored routines |
| EXECUTE | Execute_priv | stored routines |
| FILE | File_priv | file access on server host |
| CREATE TABLESPACE | Create_tablespace_priv | server administration |
| CREATE USER | Create_user_priv | server administration |
| PROCESS | Process_priv | server administration |
| PROXY | see proxies_priv table | server administration |
| RELOAD | Reload_priv | server administration |
| REPLICATION CLIENT | Repl_client_priv | server administration |
| REPLICATION SLAVE | Repl_slave_priv | server administration |
| SHOW DATABASES | Show_db_priv | server administration |
| SHUTDOWN | Shutdown_priv | server administration |
| SUPER | Super_priv | server administration |
| ALL [PRIVILEGES] |
| server administration |
| USAGE |
| server administration |
二、 MySQL的权限控制原理
2.1 MySQL帐号简介
MySQL中，帐号名称包含用户名和主机名（或者IP地址）2个部分，中间用@隔开，格式为'user_name'@'host_name'。
在连接认证阶段， MySQL 通过用户名和 主机名联合进行确认，例如 MySQL 安装后默认创建的账户 root@localhost 表示用户 root 只能从本地 （localhost） 进行连接才可以通过认证，此用户从其他任何主机对数据库进行的连接都将被拒绝。 也就是说， 同样的一个用户名， 如果来自不同的 IP 地址，则 MySQL 将其视为不同的用户。
2.2 MySQL权限表
在权限存取的过程中，系统会用到“mysql”数据库中 user、 db 、tables_priv column、spriv和procs_priv这 5个最重要的权限表，这几个表中，最重要的是user表，表结构定义如下所示。
| 表名 | user | db |
| 用户列 | Host | Host |
|
| User | Db |
|
| Password | User |
| 权限列 | Select_priv | Select_priv |
|
| Insert_priv | Insert_priv |
|
| Update_priv | Update_priv |
|
| Delete_priv | Delete_priv |
|
| Index_priv | Index_priv |
|
| Alter_priv | Alter_priv |
|
| Create_priv | Create_priv |
|
| Drop_priv | Drop_priv |
|
| Grant_priv | Grant_priv |
|
| Create_view_priv | Create_view_priv |
|
| Show_view_priv | Show_view_priv |
|
| Create_routine_priv | Create_routine_priv |
|
| Alter_routine_priv | Alter_routine_priv |
|
| Execute_priv | Execute_priv |
|
| Trigger_priv | Trigger_priv |
|
| Event_priv | Event_priv |
|
| Create_tmp_table_priv | Create_tmp_table_priv |
|
| Lock_tables_priv | Lock_tables_priv |
|
| References_priv | References_priv |
|
| Reload_priv |
|
|
| Shutdown_priv |
|
|
| Process_priv |
|
|
| File_priv |
|
|
| Show_db_priv |
|
|
| Super_priv |
|
|
| Repl_slave_priv |
|
|
| Repl_client_priv |
|
|
| Create_user_priv |
|
|
| Create_tablespace_priv |
|
| 安全列 | ssl_type |
|
|
| ssl_cipher |
|
|
| x509_issuer |
|
|
| x509_subject |
|
|
| plugin |
|
|
| authentication_string |
|
|
| password_expired |
|
| 资源控制列 | max_questions |
|
|
| max_updates |
|
|
| max_connections |
|
|
| max_user_connections |
|
User中的列主要分为 4 个部分：用户列、权限列、安全列和资源控制列。通常用得最多的是用户列和权限列， 其中权限列又分为普通权限和管理权限。 普通权限主要用于数据库的操作，比如 select_priv、create_priv 等；而管理权限主要用来对数据库进行管理的操作，比如 process_priv、super_priv 等。
2.3 MySQL权限系统的认证过程
MySQL 权限系统通过下面两个阶段进行认证：
（1）对连接的用户进行身份认证，合法的用户通过认证，不合法的用户拒绝连接；
（2）对通过认证的合法用户赋予相应的权限，用户可以在这些权限范围内对数据库做相应的操作。
当用户进行连接的时候，权限表的存取过程有以下两个阶段。先从 user 表中的 host、user 和 password 这 3 个字段中判断连接的 IP、用户名和密码是
否存在于表中，如果存在，则通过身份验证，否则拒绝连接。如 果 通 过 身 份 验 证 ， 则 按 照 以 下 权 限 表 的 顺 序 得 到 数 据 库 权 限 ：user?db?tables_priv?columns_priv。
在这几个权限表中，权限范围依次递减，全局权限覆盖局部权限。下面以一个例子来说明这个权限范围的问题。
1）创建帐号xiang@localhost，并赋予所有数据库上的所以表的查询权限。
mysql> grant select on *.* to xiang@localhost identified by '123';
Query OK, 0 rows affected (0.01 sec)
mysql> select * from user where user='xiang' and host='localhost'\G;
*************************** 1. row ***************************
Host: localhost
User: xiang
Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
1 row in set (0.03 sec)
mysql>
2）再看看db表
mysql> select * from db where user='xiang'\G;
Empty set (0.00 sec)
可以看到，user 表的的 select_priv 列是“Y” ，而 db 表中并没有记录，也就是说，对所有数据库都具有相同权限的用户记录并不需要记入 db 表，而仅仅需要将 user 表中的select_priv 改为“Y”即可。换句话说，user 表中的每个权限都代表了对所有数据库都有的权限。
3）将xiang@localhost上的权限改为只对kevin数据库上的所有表的查询权限。
mysql> revoke select on *.* from xiang@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> grant select on kevin.* to xiang@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where user='xiang' and host='localhost'\G;
*************************** 1. row ***************************
Host: localhost
User: xiang
Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
1 row in set (0.00 sec)
mysql> select * from db where user='xiang'\G;
*************************** 1. row ***************************
Host: localhost
Db: kevin
User: xiang
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
1 row in set (0.00 sec)
可以看到，user 表中的 select_priv 变为“N” ，而 db 表中则增加了 db 为 kevin 的一条记录。也就是说，当只授予部分数据库某些权限时，user 表中的相应权限列保持“N” ，而将具体的数据库权限写入 db 表。同样的，table 和 column 的权限机制和 db 类似。
从 上 面 例 子 可 以 看 出 ， 当 用 户 通 过 权 限 认 证 ， 进 行 权 限 分 配 时 ， 将 按 照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.4 MySQL的帐号管理
（1） 创建帐号。
有2种操作方法可以创建帐号：使用grant语法创建或者直接操作授权表。推荐使用第一种方式。
Grant语法很简单：
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH {GRANT OPTION | resource_option} ...]
GRANT PROXY ON user_specification
TO user_specification [, user_specification] ...
[WITH GRANT OPTION]
object_type: {
TABLE
| FUNCTION
| PROCEDURE
}
示例如下：
例1：
mysql> grant all privileges on *.* to xiang@localhost identified by '123';
Query OK, 0 rows affected (0.00 sec)
例2：授予 SUPER、PROCESS、FILE 权限给用户 xiang@localhost。
mysql> grant super,process,file on kevin.* to xiang@localhost;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
因为这几个权限都属于管理权限， 因此不能够指定某个数据库， on 后面必须跟 “*.*”，否则会报上面的错误。
mysql> grant super, process, file on *.* to xiang@localhost;
Query OK, 0 rows affected (0.00 sec)
例3：只授予登录权限给 xiang@localhost。
mysql> grant usage on *.* to xiang@localhost;
Query OK, 0 rows affected (0.00 sec)
（2） 查看和更改帐号权限。
查看帐号权限，可以用如下命令：
mysql> show grants for xiang@localhost\G;
*************************** 1. row ***************************
Grants for xiang@localhost: GRANT USAGE ON *.* TO 'xiang'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
1 row in set (0.00 sec)
更改权限：
权限可以添加和回收。和帐号创建一样，变更权限也有2种方式：使用grant和revoke命令方式，或者直接修改权限表。
和创建账号语法完全一样，grant 可以直接用来对账号进行增加。其实 grant 语句在执行的时候，如果权限表中不存在目标账号，则创建账号；如果已经存在，则执行权限的新增。这里就不给示例了。
Revoke语句可以回收已经赋予的权限。示例如下：
mysql> revoke select on *.* from xiang@localhost;
Query OK, 0 rows affected (0.00 sec)
注意，usage 权限不能被回收，也就是说，REVOKE 用户并不能删除用户。
（3） 删除帐号。
删除帐号同样也有2种方式：drop user命令和直接修改权限表
Drop user命令很简单，举例如下：
mysql> show grants for xiang@localhost\G;
*************************** 1. row ***************************
Grants for xiang@localhost: GRANT USAGE ON *.* TO 'xiang'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
*************************** 2. row ***************************
Grants for xiang@localhost: GRANT SELECT ON `kevin`.* TO 'xiang'@'localhost'
2 rows in set (0.00 sec)
mysql> drop user xiang@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for xiang@localhost;
ERROR 1141 (42000): There is no such grant defined for user 'xiang' on host 'localhost'
mysql>
修改权限表方法，只要把相关权限表中的用户记录删除即可。
（4） 修改的权限何时生效
当MySQL启动时，会将权限表在数据载入内存，当帐号通过身份认证后，就在内存中进行相应权限的存取。
当我们使用grant、revoke、set password或者rename user等命令修改用户权限时，mysql会捕获到这些权限变化并重新加载更新后的权限表。
但是 ，当我们使用insert、update、delete等sql语句直接操作权限表修改帐号权限时，服务器是不会自动重新加载更新后的权限的。这时，我们需要通过如下这些命令老告诉服务器去主动加载新的权限表。
FLUSH PRIVILEGES
mysqladmin flush-privileges
mysqladmin reload
示例如下：
先给帐号xiang@localhost添加对kevin数据库内所有表的查询权限
mysql> grant select on kevin.* to xiang@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for xiang@localhost\G;
*************************** 1. row ***************************
Grants for xiang@localhost: GRANT USAGE ON *.* TO 'xiang'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
*************************** 2. row ***************************
Grants for xiang@localhost: GRANT SELECT ON `kevin`.* TO 'xiang'@'localhost'
2 rows in set (0.00 sec)
通过直接修改权限表的方式回收帐号xiang@localhost对kevin数据库内所有表的查询权限
mysql> delete from db where user='xiang';
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for xiang@localhost\G;
*************************** 1. row ***************************
Grants for xiang@localhost: GRANT USAGE ON *.* TO 'xiang'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
*************************** 2. row ***************************
Grants for xiang@localhost: GRANT SELECT ON `kevin`.* TO 'xiang'@'localhost'
2 rows in set (0.01 sec)
可以看到，这个时候虽然我们已经将db表中的权限手动删除掉了，但是内存中的权限表没有及时更新，因此依然可以查到相关的权限。
刷新权限列表，再次查询
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for xiang@localhost\G;
*************************** 1. row ***************************
Grants for xiang@localhost: GRANT USAGE ON *.* TO 'xiang'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
1 row in set (0.00 sec)
这时内存中的权限表重新加载过，权限已经更新了。
（5） 修改用户密码
方法 1：可以用 mysqladmin 命令在命令行指定密码。
shell> mysqladmin -u xiang -h localhost password "123"
方法 2：执行 SET PASSWORD 语句。下例中将账号xiang@localhost的密码改为'123'。
mysql> SET PASSWORD FOR xiang@localhost = PASSWORD('123');
如果是更改自己的密码，可以省略 for 语句：
mysql> SET PASSWORD = PASSWORD('123');
方法 3：还可以在全局级别使用 GRANT USAGE 语句(在*.*)来指定某个账户的密码而
不影响账户当前的权限。
mysql> GRANT USAGE ON *.* TO xiang@localhost IDENTIFIED BY '123';
方法 4：直接更改数据库的 user 表。
mysql> UPDATE user SET Password = PASSWORD('123') WHERE user = 'xiang' and host='localhost';
mysql> FLUSH PRIVILEGES;
注意：更改密码时候一定要使用 PASSWORD 函数（mysqladmin 和 GRANT 两种方式不用写，会自动加上）。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22213086/viewspace-1570989/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/22213086/viewspace-1570989/
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
