mysql权限管理

一、mysql的访问分两个阶段

1.        检查用户是否具有建立与mysql的连接的权利，主要依靠检查：用户名、主机名和密码；

2.        建立了连接后，服务器检查客户端发出的每个请求。看是否有足够的权限实施它。例如执行select命令等。

二、 权限控制

1.        mysql访问权限管理通过6个表：user、db、host、tables_priv、columns_priv和procs_priv来控制，6个表的作用如下表：

• 表名	含义
  user	控制“用户名”（user）可以从哪一台“主机名”(host)访问mysql 该表包括全局权限的控制
  Db	规定哪个用户可以访问哪个数据库
  Host	host表与db表结合使用在一个较好层次上控制特定主机对数据库的访问权限
  Tables_priv	规定谁可以访问数据库的哪一个表
  columns_priv	规定谁可以访问表的哪一个列
  procs_priv	规定谁可以执行哪个存储过程

2.        mysql识别的权限如下表：

• Mysql权限	对应权限列	含义
  用于数据表访问
  Select	Select_priv	是否可以读取表的数据
  Insert	Insert_priv	是否可以向表中插入新的记录
  delete	Delete_priv	是否可以删除表中现有记录
  Update	Update_priv	是否可以更新表中现有记录
  Lock tables	Lock_tables_priv	是否可以锁定表
  用于改变数据库、数据表和视图
  Create	Create_priv	是否可以创建一个新的数据库和数据表
  Create temporary table	Create_tmp_table_priv	是否可以创建临时表
  Alter	Alter_priv	是否可以重命名并改变它的结构
  Index	Index_priv	是否可以添加或删除表索引
  References	References_priv	暂不用
  Drop	Drop_priv	是否可以删除现有的数据库或表
  Create view	Create_view_priv	是否可以创建视图
  Show view	Show_view_priv	是否是可以检查视图定义
  用于存储过程
  Alter routine	Alter_routine_priv	是否改变现有的存储过程
  Create routine	Create_routine_priv	是否可以定义新的存储过程
  Execute	Execute_priv	是否可以执行存储过程
  用于数据库访问
  File	File_priv	是否可以读取和改变本地文件系统的文件
  Create user	Create_user_priv	是否可以创建新用户
  用于mysql管理
  Grant option	Grant_priv	是否可以赋予其他用户个人的权限
  Show databases	Show_db_priv	是否可以看到一份全体数据库的清单
  Process	Process_priv	是否可以看淡到其他用户的mysql进程
  Super	Super_priv	是否可以终止其他用户的mysql进程（kill）
  Reload	Reload_priv	是否可以执行各种命令
  Replication client	Repl_client_priv	是否可以决定镜像系统中参与者的信息
  Replication slave	Repl_slient_priv	是否可以通过镜像机制读取msyql服务器数据
  Shoutdown	Shutdown_priv	是否可以关闭mysql

3.        6个数据权限表的结构

说明：1）.user表中的字段列代表的全局的权限，即一个用户如果在user表中设置允许某个操作，则该用户对所有数据库都可以操作，例如如果给一个用户授予select，则该用户对所有数据库都读的权限；

2）.当user表的全局权限与其他表对象权限都有设置时，有下列关系：

• User表	Y(select为例)	N(select为例)
  Y	Y	Y
  N	Y	N

3．数据访问列的内容

3.1、 Host 字段

一个Host列值可以是一个主机名或一个IP地址。可以用通配符指定Host值。可以使用SQL的模式字符“%”和“_”并具有当你在一个查询中使用LIKE算符同样的含义（不允许regex算符）。 （例如：192.168.3.%匹配任何在192.168.3 C类子网的主机。）

3.2、User 字段

用户名必须是文字的或空白。一个空白值匹配任何用户。%作为一个User值不意味着空白，而是匹配一个字面上的%名字， 当一个到来的连接通过user表被验证而匹配的记录包含一个空白的User值，客户被认为是一个匿名用户。

3.3、Password字段

口令值可以是空或非空，不允许用通配符。一个空口令不意味着匹配任何口令，它意味着用户必须不指定口令。

3.4、Db字段

在columns_priv和tables_priv表中，Db值必须是真正的数据库名（照字面上），不允许模式和空白。在db和host中，Db值可以以字面意义指定或使用SQL模式字符‘%‘或‘_‘指定一个通配符。一个‘%‘或空白匹配任何数据库。

3.5、Table_name，Column_name字段

这些列中的值必须是照字面意思的表或列名，不允许模式和空白。

3.6、mysql匹配连接关系如下

三、权限分配管理：

1. 权限可分为以下层级：

全局层级： 全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销全局权限。

数据库层级 ：数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限。

表层级：表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。

列层级：列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，您必须指定与被授权列相同的列。

子程序层级：CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在mysql.procs_priv表中。

1. 授权管理：根据操作人员的所在职责要求具体操作如下：
   2.1:数据库管理员：
   权限：全局层的所有的操作权限:
   操作：grant all privileges on *.* to ‘usename’@’hostnane’ IDENTIFIED
   by ‘password‘ with grant option
   2.2:数据库操作者：
   权限：表层级的访问和更新权限：
   2.2.1授予某个用户某个表的操作权限：
   操作:grant select,update on dbname.tbl_name to
   ‘usename’@’hostnane’ IDENTIFIED by ‘password‘
   2.2.2授予某个用户表的某个字段操作权限：
   操作：grant update(column_name) on dbname.tbl_name to
   ‘usename’@’hostnane’ IDENTIFIED by ‘password‘
   2.3:数据访问者：
   权限：表层级的访问权限：
   操作：grant select on dbname.tbl_name to ‘usename’@’hostnane’
   IDENTIFIED by ‘password‘
   2.4:执行存储过程的权限：
   权限：可以执行某个数据库的存储过程或者函数
   操作：grant Execute on dbname.* to ‘usename’@’hostnane’
   IDENTIFIED by ‘password‘
   说明：1.创建某个数据库的某个存储过程的执行权限操作为：
   insert into `procs_priv` (`Host`, `Db`, `User`, `Routine_name`,
   `Routine_type`, `Grantor`, `Proc_priv`, `Timestamp`)
   values(‘host‘,‘dbname‘,‘username‘,‘proc_name‘,‘PROCEDURE‘,‘存
   储过程创建者的访问帐号‘,‘Execute,Alter Routine‘,‘2007-08-14
   17:33:39‘);
   2.mysql的中，root用户可以执行、修改任何用户创建的存储过程；
   其他访问用户只能修改自己创建的存储过程，如果想要修改其他用户
   的存储过程，则必须要授予mysql.proc表的选择权限。（如果某用户
   被授予了此权限，则此用户也拥有了他能访问的数据库的所有存储过
   程的执行权限）
   
   2.5：限制某个帐户的资源使用：
   权限：访问帐号在每个小时内可以查询多少次、访问多少次等：
   操作：GRANT ALL ON dbname.* TO ‘username‘@‘hostname‘
   IDENTIFIED BY ‘frank‘ WITH MAX_QUERIES_PER_HOUR 20
   MAX_UPDATES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 5
   MAX_USER_CONNECTIONS 2;
   说明：WITH MAX_QUERIES_PER_HOUR 20：平均每小时可以执行20次查询；
   MAX_UPDATES_PER_HOUR 10：平均每个小时可以执行20次更新；
   MAX_CONNECTIONS_PER_HOUR 5:每小时最大连接用户数；
   MAX_USER_CONNECTIONS 2：单个用户每小时可连接两次；
   
   说明：对于修改数据库、表、视图的修改，存储过程和函数的创建和修改权限，授
   权和资源控制权限一旦授权后存在风险，建议应该只有管理员拥有这些权限
   

2. Mysql连接匹配关系如下表：

• 编号	Host值	User值	匹配连接
  1	Localhost	Root	Root用户，从本地可以连接
  2	192.168.1.%	Root	Root用户，从192.168.1的子网可以连接
  3	192.168.1.2	Root	Root用户，从192.168.1.2的主机可以连接
  4	192.168.1.2	空值	任何用户，从192.168.1.2的主机都可以连接
  5	%	Root	Root用户，从任何主机都可以连接
  6	%	空值	任何用户，任何主机都可以连接
  7	空值	空值	任何用户，任何主机都可以连接
  8	Localhost	空值	任何用户，从localhost都可以连接

说明：在连接中如果没有指定连接参数，mysql客户端程序使用默认值：
1. 默认主机名上localhost，默认用户名在windows中是odbc,在linux中是
linux的登陆名；
2. 如果在mysql.user表中同时存在编号7、8两个访问连接，那么在当客户
端的连接参数都为空时，mysql默认匹配编号为8的连接，如果只存在7，
那么匹配编号7的连接；

1. 帐户管理
   4.1.给root帐户和没有设置密码的帐户设置一个强密码：
   操作：SET PASSWORD FOR ‘root‘= PASSWORD(‘biscuit‘);
   或者：GRANT USAGE ON *.* TO ‘usernmae’ IDENTIFIED BY ‘biscuit‘
   4.2.删除匿名用户：
   操作：delete from user where user=’’
   4.3.删除host中为%的访问者：
   操作：delete from user where host=’%’
   4.4.撤消某个用户的权限：
   4.4.1撤消某个用户对某个数据库的操作权限：
   操作：revoke update on dbname.* from ‘username’%’hostname‘
   4.4.2撤消某个用户对数据库的某个表的操作权限：
   操作：revoke update on dbname.tblname from ‘username’%’hostname’
   4.5.删除废用的访问帐号：
   操作：drop user ‘usename’@’hostname’
   4.6.撤消某个帐户的资源限制：
   操作：GRANT USAGE ON dbname.* TO ‘username‘@‘hostname‘ WITH
   MAX_CONNECTIONS_PER_HOUR 0

后记：

若访问MySQL服务器中的数据的客户端IP可以固定的话，建议尽量采用固定IP地址，或者机器名称（若在my.cnf中禁用了skip-networking，就不要使用主机名称，会导致无法解释）。启动Mysqld时不要使用root帐号，使用专用的帐号，若不嫌麻烦尽量把root帐号的名称进行更改。

mysql权限管理

原文：http://my.oschina.net/soarwilldo/blog/403809