ITPub博客

首页 > 数据库 > MySQL > MySQL权限管理

MySQL权限管理

原创 MySQL 作者:xiangqinghu 时间:2015-04-13 09:20:11 0 删除 编辑

 一、       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 columnsprivprocs_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_privcreate_priv 等;而管理权限主要用来对数据库进行管理的操作,比如 process_privsuper_priv 等。

 

2.3 MySQL权限系统的认证过程

MySQL 权限系统通过下面两个阶段进行认证:

1)对连接的用户进行身份认证,合法的用户通过认证,不合法的用户拒绝连接;

2)对通过认证的合法用户赋予相应的权限,用户可以在这些权限范围内对数据库做相应的操作。

 

当用户进行连接的时候,权限表的存取过程有以下两个阶段。先从 user 表中的 hostuser 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:授予 SUPERPROCESSFILE 权限给用户 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种方式:使用grantrevoke命令方式,或者直接修改权限表。

 

和创建账号语法完全一样,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启动时,会将权限表在数据载入内存,当帐号通过身份认证后,就在内存中进行相应权限的存取。

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

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

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@localhostkevin数据库内所有表的查询权限

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-07-28

  • 博文量
    10
  • 访问量
    62536