MySQL登陆帐号权限的运作

MySQL登陆帐号权限的运作 依然有人在问我 MySQL 权限设置的问 下面我把很久前在CSDN写的一个文章帖出来吧. 文中所用的 MySQL 是 3.23.x 虽然 MySQL 发展到现在， 对授权表做了很多改动， 但基本的运作方式并没有太大改变， 如果您是一个初学者， 请一步步地操作下去 ... 当初次在机器上安装完MySQL后， 你通过运行scripts/mysql_install_db安装初始的存取权限。 scripts/mysql_install_db脚本将启动mysqld服务器， 然后初始化授权表，包含下列权限集合: {WIN32用户不需要使用上述命令，在MySQL安装完后， 直接运行mysql/bin/winmysqladmin.exe即可} 当你确定MySQL服务已运行后 请在WINDOWS的MSDOS方式下运行下列指令 c:\mysql\bin>mysql -u root mysql>use mysql mysql>select * from mysql.user; +-----------+------+----------+------------+------------...-+------------+ | Host | User | Password |Select_priv |Insert_priv ... | ALter_priv | +-----------+------+----------+------------+------------...-+------------+ | localhost | root | | Y | Y ....| Y | | % | root | | Y | Y ....| Y | | localhost | | | Y | Y ....| Y | | % | | | N | N ....| N | +-----------+------+----------+------------+------------...-+------------+ 4 rows in set (0.00 sec) 1、MySQL root用户作为可做任何事情的一个超级用户被创造。 连接必须由本地主机发出。 注意:新安装系统中的root口令是空的， 因此任何人能以root而没有一个口令进行连接并且被授予所有权限。 2、一个匿名用户被创造，他可对有一个'test'或以'test_'开始的名字的数据库做任何时 期事情， 连接必须由本地主机发出。这意味着任何本地用户能连接并且视为匿名用户。 3、其他权限被拒绝。 例如，一般用户不能使用mysqladmin shutdown或mysqladmin processlist。 在Win32上缺省的权限给所有本地用户以所有数据库的完全权限。 为了使MySQL更安全， 你应该为每个用户设置口令并删除mysql.user中有Host='localhost'和User=''的行。 下面将详细讲解: 你可以匿名进行访问数据库或者以不带口令的root身份进入数据库。 c:\mysql\bin>mysql -u root 你还要进行一些用户的建立及授权，这将涉及到设置密码的问题。 一、用户授权的基本操作 下面我们就讨论一下如何设置密码: 首先我们应该知道MySQL数据库中的口令存储必须用Password()函数加密它。 因为在user表中是以加密形式存储口令，而不是作为纯文本。 如果你没有加密，直接在数据库中执行以下语句: USE mysql INSERT INTO user (Host,User,PasFLUSH PRIVILEGES; 相信结果将会令你很失望。 因为服务器比较的是加密的值，所以服务器连接一定失败。 这里需要说明的是“FLUSH PRIVILEGES;”这条命令， 它起到了重新加载授权表的作用。 你也可以在shell下直接用 mysqladmin -u root reload 或者 mysqladmin -u root flush-privileges 来实现重载授权表。 在MySQL环境下,你可以使用以下语句进行设置密码: 1、INSERT INTO user(Host,User,Password) VALUES('%','user_name',Password("your password"); 2、SET PASSWORD FOR user_name = password("your password") 以上两种方法都必须进行重载授权表。 3、当然你也可以在创建一个用户时直接设置密码,grant语句将为你自动加密口令. 如 grant all on *.* to user_name@% identified by "your password"; 4、另外你也可以在shell环境下用mysqladmin程序来设置密码 如 mysqladmin -u root password "your password" 如果你正在使用在windows下的MySQL共享软件版本， 上面的命令将以 parse error near 'SET OPTION password'的错误而失败， 这是因为，这是共享软件版本，没有SET PASSWORD命令。 对共享软件版本，你能如下设置root用户口令: C:\mysql\bin\mysql mysql mysql> UPDATE user SET Password=PASSWORD('your password') WHERE User='root'; mysql> QUIT C:\mysql\bin\mysqladmin reload 二、用户授权详述 2.1 MySQL授权表的结构和内容 MySQL的安全/权限系统是非标准的，但是技术先进～ 客户对MySQL数据库的访问权限均由授权表内容来控制。 这些表是以MySQL数据库文件形式存放在mysql数据库中， 并在第一次安装MySQL的过程中初始化(运行mysql_install_db脚本)， 在MySQL启动时和在执行用户权限刷新时，服务器读入这些数据库表内容。 授权表共有5个表:user、db、host、tables_priv和columns_priv。 对用户权限的控制，你完全可以标准SQL语句来进行操纵。 表1 user、db和host授权表结构 +-------------+----------------+----------------+----------------+ | 表名 | user | db | host | +-------------+----------------+----------------+----------------+ | 访问范围列 | Host | Host | Host | | | User | Db | Db | | | Password | User | | +-------------+----------------+----------------+----------------+ | 库/表权限列 | Alter_priv | Alter_priv | Alter_priv | | | Create_priv | Create_priv | Create_priv | | | Delete_priv | Delete_priv | Delete_priv | | | Drop_priv | Drop_priv | Drop_priv | | | Index_priv | Index_priv | Index_priv | | | Insert_priv | Insert| | References_priv| References_priv| References_priv| | | Select_priv | Select_priv | Select_priv | | | Update_priv | Update_priv | Update_priv | | | File_priv | Grant_priv | Grant_priv | | | Grant_priv | | | | | Process_priv | | | | | Reload_priv | | | | | Shutdown_priv | | | +-------------+----------------+----------------+----------------+ 表2 tables_priv和columns_priv属权表结构 +-------------+----------------+----------------+ | 表名 | tables_priv | columns_priv | +-------------+----------------+----------------+ | 访问范围列 | Host | Host | | | Db | Db | | | User | User | | | Table_name | Table_name | | | | Colunm_name | +-------------+----------------+----------------+ | 权限列 | Table_priv | Colunm_priv | | | Colunm_priv | | +-------------+----------------+----------------+ | 其它字段 | Timestamp | Timestamp | | | Grantor | | +-------------+----------------+----------------+ 授权表的用途: user表 user表列出可以连接服务器的用户及其口令，并且它指定他们有哪种全局(超级用户)权限。 范围字段决定是否允许或拒绝到来的连接。 对于允许的连接，权限字段指出用户的全局(超级用户)权限，并适用于所有数据库。 例如，如果你设定某用户的 Delete_priv='Y'， 则这个用户可以从任何表中删除记录。 db表 范围字段决定用户能从哪个主机存取哪个数据库。 权限字段决定允许哪个操作。 在这里指定的权限适用于一个数据库中的所有表。 host表 host表与db表结合使用在一个较好层次上控制特定主机对数据库的访问权限， 这可能比单独使用db好些。 db和host表一起使用: 当你想要一个给定的db条目应用于若干主机时，host表作为db表的扩展被使用。 例如，如果你想要一个用户能在你的网络从若干主机使用一个数据库， 在用户的db表的Host条目设为空值，然后将那些主机的每一个移入host表。 tables_priv表 指定表级权限，在这里指定的一个权限适用于一个表的所有列。 columns_priv表 指定列级权限,这里指定的权限适用于一个表的特定列。 tables_priv和columns_priv表类似于db表，但是更精致:他们作用于表和列级而非作用于数据库级。 注意管理权限(reload, shutdown, 等等)仅在user表中被这是因为管理性操作是服务器本身的操作并且不是特定数据库，因此没有理由在其他授权表中列出这样的权限。 事实上，只需要检查user表来决定你是否有权限执行一个管理操作。 file权限也仅在user表中指定。 它虽然不是管理性权限，但你在服务器主机上的读或写文件的的权力是独立于你正在存取的数据库的。 注: tables_priv和columns_priv表在MySQL 3.22.11版引进(与GRANT语句同时)。 如果你使用较早版本的MySQL，你的mysql数据库将只有user、db和host表。 如果你从老版本升级或更新到3.22.11，而没有tables_priv和columns_priv表，运行mysql_fix_privileges_tables脚本创建它们。 MySQL没有rows_priv表，因为它不提供记录级权限。 例如，你不能限制用户于表中包含特定列值的行。如果你确实需要这种能力，你必须用应用编程来提供。 如果你想执行建议的记录级锁定，你可用GET_LOCK()函数做到。 授权表包含两种列:决定一个权限何时运用的范围列和决定授予哪种权限的权限列。 2.1.1 授权表范围列 授权表范围列指定表中的权限何时运用。 每个授权表条目包含Host和User列来指定权限何时运用于一个从某主机的连接服务器的用户。 其他表包含附加的范围列，如db表包含一个Db列指出权限运用于哪个数据库。 类似地，tables_priv和columns_priv表包含范围字段，缩小范围到一个数据库中的特定表或一个表的特定列。 范围列内容 一些范围列要求文字值，但它们大多数允许通配符或其他特殊值。 Host Host列值可以是一个主机名、一个IP地址或'localhost'指出本地主机。 你可以在Host字段里使用通配符字符“%”和“_”。 %值匹配所有主机，并可用于允许一个用户从任何地方连接。一个空白的Host值等同于%。 如%wisc.edu匹配任何wisc.edu域内的主机，而%.edu匹配任何教育学院的主机。 类似地，192.168.%匹配任何在192.168 B类子网的主机， 而192.168.3.%匹配任何在192.168.3 C类子网的主机。 (例外:在db表中，一个空白Host值含义是“进一步检查host表”) User 用户名必须是文字的或空白。 User字段中不支持通配符，但是你能指定一个空白值匹配任何用户。 %作为一个User值并不意味着空白，相反地它匹配一个字面上的%名字，这可能不是你想要 的。 Password 口令值可以是空或非空，不允许用通配符。 一个空口令并不意味着匹配任何口令，它意味着用户连接服务器时不得指定口令。 口令是以一个加密过的值存储的，而不是一个字面上的文本。 如果你在Password列中存储一个实际字面上的口令，用户将不能连接～ GRANT语句和mysqladmin passwor 但是如果你用诸如INSERT、REPLACE、UPDATE或SET PASSWORD等命令， 一定要用PASSWORD("new_password")函数而不是简单的"new_password"来指定口令。 例如 UPDATE user SET Password=PASSWORD('your password') WHERE User='root'; Db 在columns_priv和tables_priv表中，Db值必须是真正的数据库名，不允许模式和空白。 但在db表和host表中，Db值可以以字面意义指定或使用SQL模式字符'%'或'_'指定一个 通配符。 一个'%'或空白匹配任何数据库。 Table_name，Column_name 这些列中的值必须是照字面意思的表或列名，不允许模式和空白。 某些范围列被服务器视为大小写敏感的，其余的则不是。 这些原则总结在下表中。 特别注意Table_name值总是被看作大小写敏感的， 在查询中使用的表名的大小写敏感性要视MySQL服务器运行的主机的文件系统而定(UNIX下 是大小写敏感，而Windows不是)。 表3 授权表范围列的大小写敏感性 +--------------+-----+-----+---------+----+-----------+------------+ | 列 |Host |User |Password |Db |Table_name |Column_name | | 大小写敏感性 |No |Yes |Yes |Yes |Yes |No | +--------------+-----+-----+---------+----+-----------+------------+ 2.1.2 授权表权限列 授权表还包含权限列，他们指出在范围列中指定的用户拥有何种权限。 +------------+-----------------+----------------------+ | 权限 | 列 | 作用域 | +------------+-----------------+----------------------+ | select | Select_priv | 表 | | insert | Insert_priv | 表 | | update | Update_priv | 表 | | delete | Delete_priv | 表 | | index | Index_priv | 表 | | alter | Alter_priv | 表 | | create | Create_priv | 数据库、表或索引 | | drop | Drop_priv | 数据库或表 | | grant | Grant_priv | 数据库或表 | | references | References_priv | 数据库或表 | | reload | Reload_priv | 服务器管理 | | shutdown | Shutdown_priv | 服务器管理 | | process | Process_priv | 服务器管理 | | file | File_priv | 在服务器上的文件存取 | +------------+-----------------+----------------------+ 数据库和表权限 下列权限运用于数据库和表上的操作。 ALTER 允许使用ALTER TABLE语句 CREATE 允许创建数据库和表，但不允许创建索引。 DELETE 允许从表中删除现有记录。 DROP 允许删除(抛弃)数据库和表，但不允许删除索引。 INDEX 允许创建并删除索引。 REFERENCES 目前不用。 SELECT 允许使用SELEUPDATE 允许修改表中的已有的记录。 管理权限 下列权限运用于控制服务器或用户授权能力的操作的管理性操作。 FILE 允许读写服务器主机上的文件。该权限不应该随便授予，它很危险。 虽然已经授予读写权限，但所写的文件必须不是现存的文件， 这防止你迫使服务器重写重要文件，如/etc/passwd或属于别人的数据库的数据目录。 如果授权FILE权限，确保UNIX不以root用户运行服务器，因为root可在文件系统的任何地方创建新文件。 如果你以一个非特权用户运行服务器，服务器只能该给用户能访问的目录中创建文件。 GRANT 允许将自己的权限授予别人，包括GRANT。 PROCESS 允许通过使用SHOW PROCESS语句或mysqladmin process命令查看服务器内正在运行的线程(进程)的信息。 这个权限也允许你用KILL语句或mysqladmin kill命令杀死线程。 你同样可以看到或杀死你自己的线程。 PROCESS权限赋予了你对任何线程做这些事情的权力。 RELOAD 允许执行大量的服务器管理操作。 你可以发出FLUSH语句， 你也能执行mysqladmin的reload、refresh、flush-hosts、flush-logs、flush-privileges 和flush-tables等命令。 SHUTDOWN 允许用mysqladmin shutdown关闭服务器。 在user、db和host表中，每一个权限以一个单独的列指定。 这些列全部声明为一个ENUM("N","Y")类型，所以每个权限的缺省值是“N”。 在tables_priv和columns_priv中的权限以一个SET表示，它允许权限用一个单个列以任何组合指定。 在tables_priv表中的Table_priv列被定义成: SET('Select','Insert','Update','Delete','Create','Drop','Grant','References','In dex','Alter') 在coloums_priv表中的Column_priv列被定义成: SET('Select','Insert','Update','References') user表包含某些在其他授权表不存在的权限的列: File_priv、Process_priv、Reload_priv和Shutdown_priv。 这些权限允许你让服务器执行的与任何特定数据库或表不相关的操作。 如允许一个用户根据当前数据库是什么来关闭数据库是毫无意义的。 2.2 访问权限的验证 当你试图联接一个MySQL服务器时， 服务器将基于你的登录主机、你提供的用户名和你是否供应正确的口令来验证你的身份， 从而接受或拒绝你的连接。 身份检查使用user表3个范围字段(Host, User和Password)执行。 服务器只有在发现一个user表条目匹配你的主机名和用户名并且你提供了正确的口令时才 接受连接。 每当你发出一个查询时，MySQL服务器也会检查你是否有足够的权限执行它， 它以user、db、tables_priv和columns_priv的顺序检查。更具体的说: 服务器检查user表匹配你开始连接的记录以查看你有什么全局权限。 如果你的全局权限不够，服务器为你在db表中寻找并将该记录中的权限加到你的全局权限中。 如果你的全局和数据库级组合的权限不够，服务器继续查找，首先在tables_priv表，然后columns_priv表。 如果你在检查了所有表之后仍无权限，服务器拒绝你执行查询的企图。 前面的描述毫无疑问使权限检查听起来是一个相当复杂的过程， 特别是当服务器对你发出的每个查询进行权限检查时， 然而此过程是很快的，因为服务器其实不从授权表对每个查询查找信息， 而是在MySQL启动时就将授权表的内容读入内存，以后验证查询用的是内存中的副本。 这大大提高了权限检查操作的性能。 但有一个非常明显的副作用: 如果你直接修改授权表的内容(使用SQL)，服务器将不知道权限的改变。 例如，如果你用一条INSERT语句向user表加入一个新记录来增加一个新用户， 但是新添加的用户并不能立即连接服务器。这对管理员新手(有时对有经验的老手)是很困惑的事情， 当时解决方法很简单:在你改变了它们之后告诉服务器重载授权表内容， 你可以发一条FLUSH PRIVILEGES或执行mysqladmin flush-privileges (或如果你有一个不支持flush-privileges的老版本，用mysqladmin reload重起MySQL服务)。 2.2.1 范围列匹配顺序 下面的例子显示出各种user表中Host和User条目的值的组合如何应用于到来的连接: +-----------------+------+------------------------------------------------------ -----+ | Host | User | 被条目匹配的连接 +-----------------+------+------------------------------------------------------ -----+ | thomas.loc.gov | fred | fred, 从thomas.loc.gov 连接 | thomas.loc.gov | | 任何用户, 从thomas.loc.gov连接 | % | fred | fred, 从任何主机连接 | % | | 任何用户, 从任何主机连接 | %.loc.gov | fred | fred, 从在loc.gov域的任何主机连接 | x.y.% | fred | fred, 从x.y.net、x.y.com,x.y.edu等联接。(这或许无用) | 144.155.166.177 | fred | fred, 从有144.155.166.177 IP 地址的主机连接 | 144.155.166.% | fred | fred, 从144.155.166 C类子网的任何主机连接 +-----------------+------+------------------------------------------------------ -----+ 既然你能在Host字段使用IP通配符值(例如，'144.155.166.%'匹配在一个子网上的每台主 机)， 有可能某人企图通过命名一台主机为144.155.166.somewhere.com。 为了阻止这样的企图，MySQL不允许匹配以数字和一个点起始的主机名， 这样，如果你用一个命名为类似1.2.foo.com的主机，它的名字决不会匹配授权表中Host列。 只有一个IP数字能匹 一个连接请求可以被在user表中的超过一个条目匹配。 例如，一个由fred从thomas.loc.gov的连接匹配多个条目如上所述。 如果超过一个匹配，服务器怎么选择使用哪个条目呢, 服务器在启动时读入user表后通过排序来解决这个问题，然后当一个用户试图连接时， 以排序的顺序浏览条目，第一个匹配条目的权限设置将被使用。 MySQL服务器授权表的排序规则: 1、MySQL服务器按一种特定方式排序授权表中的记录，然后通过按序浏览记录的方式来匹配连接请求。 找到的第一个匹配决定了被使用授权的记录。 理解MySQL使用的排序顺序很重要，特别是对user表。 2、当服务器读取user表内容时，它根据在Host和User列中的值排序记录， Host值起决定作用(相同的Host值排在一起，然后再根据User值排序)。 然而，它并不是按字典顺序(按词排序)排序的，它只是部分是。 要牢记的是字面上的词优先于模式。 这意味着如果你正从client.your.net连接服务器， 而Host有client.your.net和%.your.net两个值，则client.your.net条目将会被匹配。 类似地，%.your.net优先于%.net，然后是%。 IP地址的匹配也是这样的。 总之一句话，越具体越优先。可以参见本文附录的实例。 user表排序工作如下: 假定user表物理顺序如下: +-----------+----------+- | Host | User | ... +-----------+----------+- | % | root | ... | % | jeffrey | ... | localhost | root | ... | localhost | | ... +-----------+----------+- 当服务器在表中读取时，它以最特定的Host值为先的次序排列('%'在Host列里意味着“任 何主机”并且是最不特定的)。 有相同Host值的条目以最特定的User值为先的次序排列(一个空白User值意味着“任何用户”并且是最不特定的)。 最终user表排序如: +-----------+----------+- | Host | User | ... +-----------+----------+- | localhost | root | ... | localhost | | ... ? | % | jeffrey | ... | % | root | ... +-----------+----------+- 当一个连接请求发生时，服务器浏览排序的条目并使用找到的第一个匹配。 对于由jeffrey从localhost的一个连接，在Host列的'localhost'条目首先匹配。 那个有空白用户名的条目匹配连接的主机名和用户名。 ('%'/'jeffrey'条目也将匹配，但是它不是在表中的第一匹配。) 这是另外一个例子。假定user桌子看起来像这样: +----------------+----------+- | Host | User | ... +----------------+----------+- | % | jeffrey | ... | thomas.loc.gov | | ... +----------------+----------+- 排序后的表如下: +------------| Host | User | ... +----------------+----------+- | thomas.loc.gov | | ... | % | jeffrey | ... +----------------+----------+- 一个由jeffrey从thomas.loc.gov的连接被第一个条目匹配， 而一个由jeffrey从whitehouse.gov的连接被第二个匹配。 普遍的误解: 对一个给定的用户名，当服务器试图对连接寻找匹配时， 明确命名那个用户的所有条目将首先被使用。这明显不是事实。 先前的例子说明了这点， 一个由jeffrey从thomas.loc.gov的连接没被包含'jeffrey'作为User字段值的条目匹配， 但是由没有用户名的题目匹配～ 三、动手操作 WIN32下新安装MySQL，用户权限如下: 假设mysql安装在D:\mysql目录下， 本机IP为 192.168.0.2 d:\mysql\bin>mysql -u root mysql>use mysql mysql>select * from mysql.user; +-----------+------+----------+------------+------------.............-+------------+ | Host | User | Password |Select_priv |Insert_priv ............. | ALter_priv | +-----------+------+----------+------------+------------.............-+------------+ | localhost | root | |Y |Y | Y ....... | Y | | % | root | |Y |Y | Y ....... | Y | | localhost | | |Y |Y | Y ....... | Y | | % | | |N |N | N ....... | N | +-----------+------+----------+------------+------------.............-+------------+ 4 rows in set (0.00 sec) mysql>DELETE FROM user WHERE Host='%' and User=''; ->删除第四个用户 mysql>DELETE FROM user WHERE Host='%' and User='root'; ->删除第二个用户，不允许其它计算机以"root"用户名登录本机的MySQL mysql>FLUSH PRIVILEGES; ->刷新用户授权表 D:\mysql\bin>mysql -h 192.168.0.2 ->ERROR 1130: Host '192.168.0.2' is not allowed to connect to this MySQL server ->Why?? ->因为执行了上面三步后，mysql.user中的数据为 ->+-----------+------+----------+------------+------------.............-+------------+ ->| Host | User | Password |Select_priv |Insert_priv ............. | ALter_priv | ->+-----------+------+----------+------------+------------.............-+------------+ ->| localhost | root | |Y |Y | Y ....... | Y | ->| localhost | | |Y |Y | Y ....... | Y | ->+-----------+------+----------+------------+------------.............-+------------+ ->使用mysql -h 192.168.0.2，你与MySQL建立了一个TCP/IP连接，此时你的主机将是 “192.168.0.2” ->而不在是“localhost”，而在用户授权表中，并没有指定“192.168.0.2”这个主机，所以 得到错误提示 ->ERROR 1130: Host '192.168.0.2' is not allowed to connect to this MySQL server ->?当在本机->有助于提高数据传送速率 mysql>INSERT INTO user VALUES ('192.168.0.%', 'local','','Y','Y','Y','Y','Y','Y','N','N','N','N','N','N','N','N'); mysql>FLUSH PRIVILEGES; ->新建一个名为“local”的用户，他可以从局域网中登录，具有 ->Select、Insert、Update、Delete、Create、Drop权限