Mysql 权限管理

一、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 将其视为不同的用户。

show grants for '用户'@'IP地址'                 　　 //查看权限
grant 权限 on 数据库.表 to   '用户'@'IP地址'       //授权
revoke 权限 on 数据库.表 from '用户'@'IP地址' //取消权限　　

权限:
    如上表所列
对于目标数据库以及内部其他：
    　　数据库名.*　　　　　　　　//数据库中的所有
    　　数据库名.表　　　　　　　 //指定数据库中的某张表
    　　数据库名.存储过程　　    　//指定数据库中的存储过程
    　　*.*                   　　　　 //所有数据库
用户名@
    　　用户名@IP地址           　　//用户只能在改IP下才能访问
    　　用户名@192.168.1.%      //用户只能在改IP段下才能访问(通配符%表示任意)
    　　用户名@%               　　 //用户可以再任意IP下访问(默认IP地址为%)

2.2 MySQL权限表

在权限存取的过程中，系统会用到“mysql”数据库中 user、db 、tables_priv column、spriv和procs_priv这 5个最重要的权限表，这几个表中，最重要的是user表，表结构定义如下所示。

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。在这几个权限表中，权限范围依次递减，全局权限覆盖局部权限。下面以一个例子来说明这个权限范围的问题。

mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
+---------------------------------------+
| query                                 |
+---------------------------------------+
| User: 'root'@'%';                     |
| User: 'root'@'127.0.0.1';             |
| User: 'root'@'192.168.149.128';       |
| User: 'root'@'::1';                   |
| User: 'debian-sys-maint'@'localhost'; |
| User: 'dsw'@'localhost';              |
| User: 'root'@'localhost';             |
| User: 'root'@'ubuntu';                |
+---------------------------------------+
8 rows in set (0.00 sec)

1)创建帐号dsw@localhost，并赋予所有数据库上的所以表的查询权限。

mysql> grant select on *.* to dsw@localhost identified by '123456';　　//创建dsw用户，密码：123456 并给其查找所有数据库的权限
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql.user where user='dsw' and host='localhost'\G;
*************************** 1. row ***************************
                  Host: localhost
                  User: dsw
              Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
           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.00 sec)

2)再看看db表

mysql> select * from mysql.db where user='dsw'\G;
Empty set (0.00 sec)

可以看到，user 表的的 select_priv 列是“Y” ，而 db 表中并没有记录，也就是说，对所有数据库都具有相同权限的用户记录并不需要记入 db 表，而仅仅需要将 user 表中的select_priv 改为“Y”即可。换句话说，user 表中的每个权限都代表了对所有数据库都有的权限。

3)将dsw@localhost上的权限改为只对python_db数据库上的所有表的查询权限。

mysql> revoke select on *.* from dsw@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on python_db.* to dsw@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql.user where user='dsw' and host='localhost'\G;
*************************** 1. row ***************************
                  Host: localhost
                  User: dsw
              Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
           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 mysql.db where user='dsw'\G;
*************************** 1. row ***************************
                 Host: localhost
                   Db: python_db
                 User: dsw
          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 为 python_db 的一条记录。也就是说，当只授予部分数据库某些权限时，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 dsw@localhost identified by '123456';
Query OK, 0 rows affected (0.00 sec)

例(2)：授予 SUPER、PROCESS、FILE 权限给用户 dsw@localhost。

mysql> grant super,process,file on python_db.* to dsw@localhost;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

因为这几个权限都属于管理权限，因此不能够指定某个数据库， on 后面必须跟 “*.*”，否则会报上面的错误。

mysql> grant super, process, file on *.* to dsw@localhost;
Query OK, 0 rows affected (0.00 sec)

例(3)：只授予登录权限给 dsw@localhost。

mysql> grant usage on *.* to dsw@localhost;
Query OK, 0 rows affected (0.00 sec)

2、查看和更改帐号权限。

查看帐号权限，可以用如下命令：

mysql> show grants for dsw@localhost\G;
*************************** 1. row ***************************
Grants for dsw@localhost: GRANT ALL PRIVILEGES ON *.* TO 'dsw'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
*************************** 2. row ***************************
Grants for dsw@localhost: GRANT SELECT ON `python_db`.* TO 'dsw'@'localhost'
2 rows in set (0.00 sec)

更改权限：

权限可以添加和回收。和帐号创建一样，变更权限也有2种方式：使用grant和revoke命令方式，或者直接修改权限表。

和创建账号语法完全一样，grant 可以直接用来对账号进行增加。其实 grant 语句在执行的时候，如果权限表中不存在目标账号，则创建账号；如果已经存在，则执行权限的新增。这里就不给示例了。

Revoke语句可以回收已经赋予的权限。示例如下：

mysql> revoke select on *.* from dsw@localhost;
Query OK, 0 rows affected (0.00 sec)

注意，usage 权限不能被回收，也就是说，REVOKE 用户并不能删除用户。

3、删除帐号。

删除帐号同样也有2种方式：drop user命令和直接修改权限表

Drop user命令很简单，举例如下：

mysql> show grants for dsw@localhost\G;
*************************** 1. row ***************************
Grants for dsw@localhost: GRANT INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, 
EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'dsw'@'localhost' IDENTIFIED BY
PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
*************************** 2. row ***************************
Grants for dsw@localhost: GRANT SELECT ON `python_db`.* TO 'dsw'@'localhost'
2 rows in set (0.00 sec)

mysql> drop user dsw@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for dsw@localhost\G;
ERROR 1141 (42000): There is no such grant defined for user 'dsw' on host 'localhost'
ERROR: 
No query specified

修改权限表方法，只要把相关权限表中的用户记录删除即可。

4、修改的权限何时生效

当MySQL启动时，会将权限表在数据载入内存，当帐号通过身份认证后，就在内存中进行相应权限的存取。

当我们使用grant、revoke、set password或者rename user等命令修改用户权限时，mysql会捕获到这些权限变化并重新加载更新后的权限表。

但是，当我们使用insert、update、delete等sql语句直接操作权限表修改帐号权限时，服务器是不会自动重新加载更新后的权限的。这时，我们需要通过如下这些命令老告诉服务器去主动加载新的权限表。

FLUSH PRIVILEGES;
mysqladmin flush-privileges;
mysqladmin reload;

示例如下：

先给帐号dsw@localhost添加对python_db数据库内所有表的查询权限

mysql> grant select on python_db.* to dsw@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for dsw@localhost\G;
*************************** 1. row ***************************
Grants for dsw@localhost: GRANT USAGE ON *.* TO 'dsw'@'localhost'
*************************** 2. row ***************************
Grants for dsw@localhost: GRANT SELECT ON `python_db`.* TO 'dsw'@'localhost'
2 rows in set (0.00 sec)

通过直接修改权限表的方式回收帐号dsw@localhost对python_db数据库内所有表的查询权限

mysql> delete from db where user='dsw';
ERROR 1046 (3D000): No database selected
mysql> delete from mysql.db where user='dsw';
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for dsw@localhost\G;
*************************** 1. row ***************************
Grants for dsw@localhost: GRANT USAGE ON *.* TO 'dsw'@'localhost'
*************************** 2. row ***************************
Grants for dsw@localhost: GRANT SELECT ON `python_db`.* TO 'dsw'@'localhost'
2 rows in set (0.00 sec)

可以看到，这个时候虽然我们已经将db表中的权限手动删除掉了，但是内存中的权限表没有及时更新，因此依然可以查到相关的权限。

刷新权限列表，再次查询

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for dsw@localhost\G;
*************************** 1. row ***************************
Grants for dsw@localhost: GRANT USAGE ON *.* TO 'dsw'@'localhost'
1 row in set (0.00 sec)

这时内存中的权限表重新加载过，权限已经更新了。

5、修改用户密码

方法 1：可以用 mysqladmin 命令在命令行指定密码。

shell> mysqladmin -u dsw -h localhost password "123"

方法 2：执行 SET PASSWORD 语句。下例中将账号dsw@localhost的密码改为'111111'。

mysql> SET PASSWORD FOR dsw@localhost = PASSWORD('111111');

如果是更改自己的密码，可以省略 for 语句：

mysql> SET PASSWORD = PASSWORD('123');

方法 3：还可以在全局级别使用 GRANT USAGE 语句(在*.*)来指定某个账户的密码而不影响账户当前的权限。

mysql> GRANT USAGE ON *.* TO dsw@localhost IDENTIFIED BY '111111';

方法 4：直接更改数据库的 user 表。

mysql> UPDATE mysql.user SET Password = PASSWORD('111111') WHERE user = 'dsw' and host='localhost';
mysql> FLUSH PRIVILEGES;

注意：更改密码时候一定要使用 PASSWORD 函数(mysqladmin 和 GRANT 两种方式不用写，会自动加上)。

posted @ 2016-12-22 14:49 滴水瓦阅读(624) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

dsw846

Mysql 权限管理

公告