MySQL数据库用户帐号管理基础知识详解

admin

MySQL管理员应该知道怎样通过指定哪些用户可连接到服务器、从哪里进行连接，以及在连接时做什么，来设置MySQL用户账号。MySQL3.22.11引入了两个更容易进行这项工作的语句：GRANT 语句创建MySQL用户并指定其权限，REVOKE 语句删除权限。这两个语句充当mysql数据库中的授权表的前端，并提供直接操纵这些表内容的可选择的方法。GRANT 和REVOKE 语句影响以下四个表：
  
授权表 内容
user 可连接到服务器的用户和他们拥有的任何全局特权
db 数据库级的特权
tables_priv 表级特权
columns_priv 列级特权
还有第五个授权表（ host），但它不受GRANT 或REVOKE的影响。
  
当您为某个用户发布GRANT 语句时，应在user表中为该用户创建一个项。如果该语句指定了所有全局特权（管理权限或用于所有数据库的权限），则这些指定也被记录在user表中。如果指定了数据库、表或列的权限，它们将记录在db、tables_priv 和columns_priv表中。
使用GRANT 和REVOKE语句比直接修改授权表更容易。但是，建议您最好通过阅读第12章来补充本章的内容，第12章中详细讨论了授权表。这些表非常重要，作为一位管理员应该了解这些表是怎样在GRANT 和REVOKE 语句级上工作的。
本节下面的部分将讨论如何设置MySQL用户的账号和授权，还将介绍如何取消权限以及从授权表中删除全部用户，并且将考虑一个困扰许多新的MySQL管理员的难题。
您还要考虑使用mysqlaccess 和mysql_setpermission 脚本，它们是MySQL分发包的组成部分。这些是Perl 的脚本，它们提供了设置用户账号的GRANT 语句的代用品。mysql_setpermission 需要具有DBI 的支持环境。
  
创建新用户和授权
  
GRANT 语句的语法如下：
  
  

GRANT privileges (columns) ON what TO user IDENTIFIEDBY "password" WITH GRANT OPTION 复制代码

  要使用该语句，需要填写以下部分：
  
privileges 分配给用户的权限。下表列出了可在GRANT 语句中使用的权限说明符：
  
权限说明符权限允许的操作
上表显示的第一组权限说明符适用于数据库、表和列。第二组说明符是管理特权。通常，这些权限的授予相当保守，因为它们会影响服务器的操作（例如， SHUTDOWN 特权不是按每天来分发的权限）。第三组说明符是特殊的。ALL的意思是“所有的权限”，而USAGE 的意思是“无权限”─即创建用户，但不授予任何的权限。
  
columns 权限适用的列。这是可选的，只来设置列专有的权限。如果命名多于一个列，则用逗号分开。
  
what 权限应用的级别。权限可以是全局的（适用于所有数据库和所有的表）、数据库专有的（适用于某个数据库中的所有表），或表专有的。可以通过指定一个C O L U M N S子句将权限授予特定的列。
user 使用权限的用户。它由用户名和主机名组成。在MySQL中，不仅指定谁进行连接，还要指定从哪里连接。它允许您拥有两个带有相同名字的、从不同位置连接的用户。MySQL允许在它们之间进行区别并相互独立地分配权限。
  
MySQL的用户名就是您在连接到服务器时指定的名字。该名字与您的UNIX 注册名或Windows 名的没有必然连系。缺省设置时，客户机程序将使用您注册的名字作为MySQL的用户名（如果您不明确指定一个名字的话），但这只是一个约定。有关将root作为可以操作一切MySQL的超级用户名也是这样，就是一种约定。您也可以在授权表中将此名修改成nobody，然后作为nobody 用户进行连接，以执行需要超级用户特权的操作。
  
password 分配给该用户的口令。这是可选的。如果您不给新用户指定IDENTIFIEDBY子句，该用户不分配口令（是非安全的）。对于已有的用户，任何指定的口令将替代旧口令。如果不指定新口令，用户的旧口令仍然保持不变。当您确实要使用IDENTIFIEDBY 时，该口令串应该是直接量，GRANT 将对口令进行编码。当用SET PASSWORD语句时，不要使用PASSWORD() 函数。
  
WITH GRANT OPTION 子句是可选的。如果包含该子句，该用户可以将GRANT 语句授予的任何权限授予其他的用户。可以使用该子句将授权的能力授予其他的用户。
  
  
用户名、口令以及数据库和表的名称在授权表项中是区分大小写的，而主机名和列名则不是。
  
通过查询某些问题，通常可以推断出所需的GRANT 语句的类型：
谁可以进行连接，从哪里连接？
用户应具有什么级别的权限，这些权限适用于什么？
允许用户管理权限吗？
  
让我们来提问这些问题，同时看一些利用GRANT 语句设置MySQL用户账号的例子。
  
1. 谁可以进行连接，从哪里连接。
  
您可以允许用户在特定的主机或涉及范围很宽的一组主机中进行连接。在一个极端，如果知道用户将仅从那个主机中进行连接，则可限定对单个主机的访问：
  
  

GRANT ALL ON samp_db.* TO boris@localhost IDENTFIEDBY "ruby" GRANT ALL ON samp_db.* TO fred@ares.mars.net IDENTFIEDBY "quartz" 复制代码

  （符号samp_db.* 含义是“在samp_db 数据库中的所有表”）在另一个极端，您可能会有一个用户max，他周游世界并需要能够从世界各地的主机中进行连接。在这种情况下，无论他从哪里连接您都将允许：
  
  

GRANT ALL ON samp_db.* TO max@% IDENTFIEDBY "diamond" 复制代码

  ‘%’字符起通配符的作用，与LIKE模式匹配的含义相同，在上个语句中，它的意思是“任何主机”。如果您根本不给出主机名部分，则它与指定“ %”的含义相同。因此，max和max@%是等价的。这是设置一个用户最容易的方法，但安全性最小。
  
要想采取妥协的办法，可允许用户在一组有限的主机中进行连接。例如，要使mary 从snake.net 域的任何主机中进行连接，可使用%.snake.net 主机说明符：
  
  

GRANT ALL ON samp_db.* TO mary@%.snake.net IDENTFIEDBY "topaz" 复制代码

  该用户标识符的主机部分可用IP 地址而不是主机名给出（如果愿意的话）。可以指定一个直接的IP 地址或包含模式字符的地址。同样，自MySQL3.23 起，可以用一个网络掩码来指定IP 号，网络掩码表明了用于该网络号的二进制位数：
  
  

GRANT ALL ON samp_db.* TO joe@192.168.0.3 IDENTIFIEDBY "water" GRANT ALL ON samp_db.* TO ardis@192.168.128.% IDENTIFIEDBY "snow" GRANT ALL ON samp_db.* TO rex@192.168.128.0/17 IDENTIFIEDBY "ice" 复制代码

  第一条语句指明用户可进行连接的特定的主机。第二条语句指定129.168.128 Class C 子网的IP 模式。在第三条语句中， 192.168.128.0/17 指定一个17 位二进制的网络号，并将任何主机与其IP 地址的前17 个二进制位中的192.168.128.0/17 进行匹配。
  
如果MySQL抱怨您指定的用户值，则可能需要使用引号（但对用户名和主机名分别加引号）：
  
  

GRANT ALL ON samp_db.president TO "my friend"@"boa.snake.net" 复制代码

   
  
2. 用户应具有什么级别的权限，这些权限适用于什么。
  
您可授予不同级别的权限。全局权限的功能最强，因为它们适用于任何数据库。为了使ethel 成为可以进行一切操作的超级用户（其中包括可以对其他用户授权），发布下列语句：
  
  

GRANT ALL ON *.* TO ethel@localhost IDENTIFIEDBY "coffee" WITH GRANT OPTION 复制代码

  ON 子句中*.* 说明符的意思是“所有数据库，所有的表”，为保险起见，我们已经指定ethel 只能从本地主机中连接。限制超级用户从哪些主机上进行连接通常是明智的做法，因为它限制住了其他用户对口令进行试探。
  
有些权限（FILE、PROCESS、RELOAD 和SHUTDOWN）是管理权限，只能用NO *.* 全局权限说明符来授予。如果希望的话，也可以不用授予数据库级的权限来授予这些权限。例如，下列语句建立了一个flush 用户，它除了发布FLUSH语句外不做其他任何事情。在管理脚本中这可能是有用的，因为需要在这些脚本中执行诸如在日志文件循环期间刷新日志的操作：
  
  

GRANT RELOAD ON *.* TO flush@localhost IDENTIFIEDBY "flushpass" 复制代码

  通常授予管理权限应该是保守的，因为具有这些权限的用户可能影响服务器的操作。
  
数据库级的权限适用于特定数据库中的所有表。这些权限使用ON db_name.* 子句进行授予：
  
  

GRANT ALL ON samp_db.* TO bill@racer.snake.net IDENTIFIEDBY "rock" GRANT SELECT ON menagerie.* TO ro_user@% IDENTIFIEDBY "dirt" 复制代码

  第一条语句将bill 的所有权限授予samp_db 数据库的任何表。第二条语句创建一个限制访问的用户r o _ user（只读用户），它可以访问menagerie 数据库的所有表，但只能读取。也就是说，该用户只能发布SELECT 语句。
  
  
在授权表项中指定本地主机名
  
如果您使用服务器的主机名而非localhost，通常存在从该服务器主机连接的问题。这可能是由于在授权表中指定名字的方法和名字分解器例程（ name reslover routine）向程序报告名字的方法之间的错误匹配。如果分解器报告了一个非限定的名字（如pit-viper），但授权表包含了具有全限定的名字的项（如pit-viper.snake.net，反之亦然），则发生错误匹配。
  
为了确定这种情况是否正在系统中发生，可试着用-h 选项连接到本地服务器，该选项指定了主机的名字。然后查看服务器的常规日志文件。它是怎样报告主机名的？是以非限定形式还是限定形式？不论它是哪种形式，都将告诉您在发布GRANT 语句时需要怎样指定用户说明符的主机名部分。
  
可以同时列出许多被授予的单个权限。例如，如果想让用户能读取和修改已有表的内容，但又不允许创建新表或删除表，可按如下授权：
  
  

GRANT SELECT,INSERT,DELETE, UPDATE ON samp_db.* TO jennie@% IDENTIFIEDBY "boron" 复制代码

  对于更小粒度（fine-grained）的访问控制，可以在单个表上授权，甚至在表的单个列上授权。当存在要对用户隐藏的表时，或者，当只允许用户修改特定列时，列专有的权限是有用的。假定历史同盟会中有一些志愿者利用您作为同盟会秘书应履行的职责来帮助您工作。这是一个好消息，但您决定首先给新的助手授予对member表只读的权限（该表中包含了会员资格的信息），然后再对他们增加授予该表的expiration 列的列专有UPDATE权限。也就是说，您的助手可以在人们更新其会员资格时进行更改。
第一条语句授予对整个member表的读访问权并设置口令。第二条语句增加UPDATE权限，但只是对expiration 列。此时不必要再指定口令，因为在第一条语句中已经完成了。
截止日期的工作。设置此MySQL用户的语句如下：
  
如果想要为多个列授予列专有的权限，可指定一个列清单，并用逗号将这些列分隔。例如，为了给assistant 用户增加对member 表地址列的UPDATE权限，可以使用下列语句。新的权限将被增加到对该用户来说已经存在的所有列上：
  
  

GRANT ALL ON samp_db.* TO boris@localhost IDENTFIEDBY "ruby" GRANT ALL ON samp_db.* TO fred@ares.mars.net IDENTFIEDBY "quartz"0 复制代码

  通常，不要给用户授予比实际需要更大的权限。但是，当您想要使用户能够创建存储中间结果的临时表，而又不允许用户在包含有他们不能修改的数据的数据库中这样做时，就有了要在数据库上授予相当多的许可权限的理由。您可以建立一个单独的数据库（笔者称它为t m p）并授予用户该数据库的所有权限。例如，如果想要mars.net 域的主机中的任何用户都能够使用tmp 数据库，可发布下列GRANT语句：
  
  

GRANT ALL ON samp_db.* TO boris@localhost IDENTFIEDBY "ruby" GRANT ALL ON samp_db.* TO fred@ares.mars.net IDENTFIEDBY "quartz"1 复制代码

  在完成这些之后，用户可使用tmp.tbl_name 格式的名字创建和引用tmp 数据库中的表（用户说明符中的“” 创建一个匿名用户项，任何用户都与空白用户名相匹配）。
  
3. 允许用户管理权限:
  
通过授予数据库所有者数据库的所有权限并在操作时指定WITH GRANT OPTION，可以允许数据库所有者控制对该数据库的访问。例如，如果要让alicia 能在big.corp.com 域的所有主机中进行连接并管理sales 数据库中所有表的权限，应使用GRANT语句。
  
实际上，WITH GRANT OPTION 子句允许将访问的权利授予给另一个用户。要知道，具有GRANT 权限的两个用户可以相互授予自己的权限。如果只给一个用户授予SELECT 权限而给另一个用户除SELECT 外还授予了GRANT 和其他的权限，则第二个用户可以使第一个用户“强大”起来。
  
取消权限和删除用户
  
为了收回某个用户的权限，可使用REVOKE 语句。除了要用FROM 替换TO 并且没有IDENTIFIEDBY 或WITH GRANT OPTION 子句外，REVOKE 的语法与GRANT 语句非常相似：
  
  

GRANT ALL ON samp_db.* TO boris@localhost IDENTFIEDBY "ruby" GRANT ALL ON samp_db.* TO fred@ares.mars.net IDENTFIEDBY "quartz"2 复制代码

  user 部分必须与您想要取消其权限的用户的原始GRANT 语句的user 部分相匹配。privileges部分不需要匹配，您可用GRANT 语句授权，然后用REVOKE 语句取消其中的一部分。REVOKE 语句只删除权限，不删除用户。用户的项仍然保留在user 表中，即使您取消了该用户的所有权限也是如此。这意味着该用户仍然可连接到服务器上。要想删除整个用户，必须用DELETE 语句将该用户的记录从user 表中直接删除：
  
DELETE 语句删除该用户的项， FLUSH 语句告诉服务器重新加载授权表（当使用GRANT 或REVOKE 语句，而不是直接修改授权表时，这些表将自动重新加载）。
  
一个权限难题
  
下面是一个在MySQL邮件清单中反复出现的情况：一位新的MySQL管理员给某用户增加一个项，使用了主机名部分，该部分是用一个模式来指定的。例如：
  
  

GRANT ALL ON samp_db.* TO boris@localhost IDENTFIEDBY "ruby" GRANT ALL ON samp_db.* TO fred@ares.mars.net IDENTFIEDBY "quartz"3 复制代码

  这里的意图是允许用户fred 从snake.net 域的所有主机中进行连接，并且具有对samp_db 数据库的所有权限。事实上， fred 能够从那些主机中连接（除了服务器主机本身外）。当fred 试着从服务器主机中进行连接时，该企图以“访问被拒绝”的消息而告失败。即使用户指定了正确的口令也是如此。
  
如果授权表中包含了由mysql_install_db 安装脚本安装的缺省项，这种情况也会发生。其原因是，当服务器验证fred 连接的企图时，一个匿名用户项（ anonymous-user entry）比fred 项优先。匿名用户项要求该用户不用口令来连接，并且一个口令错误匹配发生。
目前，只要说修正此问题的方法是从user表中删除匿名用户项就足够了，我们不能用REVOKE，因为该命令只删除权限。要想完全摆脱这些匿名项，执行如下操作：
  
  

GRANT ALL ON samp_db.* TO boris@localhost IDENTFIEDBY "ruby" GRANT ALL ON samp_db.* TO fred@ares.mars.net IDENTFIEDBY "quartz"4 复制代码

  现在，当fred试图从本地主机连接时成功了。