mysql权限管理
文章来源:http://www.itnose.net/detail/6250970.html
更多文章:http://www.itnose.net
一、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个数据权限表的结构
user表 |
db表 |
host表 |
tables_priv表 |
columns_pirv表 |
proc_priv表 |
访问控制列 |
|||||
Host |
Host |
Host |
Host |
Host |
Host |
User |
Db |
Db |
Db |
Db |
Db |
Password |
User |
User |
User |
User |
|
数据库/表权限控制列 |
|||||
Select_priv |
Select_priv |
Select_priv |
Table_name |
Table_name |
Routine_name |
Insert_priv |
Insert_priv |
Insert_priv |
Grantor |
Column_name |
Routine_type |
Update_priv |
Update_priv |
Update_priv |
Timestamp |
Timestamp |
Grantor |
Delete_priv |
Delete_priv |
Delete_priv |
Table_priv |
Column_priv |
Proc_priv |
Create_priv |
Create_priv |
Create_priv |
Column_priv |
Timestamp |
|
Drop_priv |
Drop_priv |
Drop_priv |
|||
Reload_priv |
Grant_priv |
Grant_priv |
|||
Shutdown_priv |
References_priv |
References_priv |
|||
Process_priv |
Index_priv |
Index_priv |
|||
File_priv |
Alter_priv |
Alter_priv |
|||
Grant_priv |
Create_tmp_table_priv |
Create_tmp_table_priv |
|||
References_priv |
Lock_tables_priv |
Lock_tables_priv |
|||
Index_priv |
Create_view_priv |
Create_view_priv |
|||
Alter_priv |
Show_view_priv |
Show_view_priv |
|||
Show_db_priv |
Create_routine_priv |
Create_routine_priv |
|||
Super_priv |
Alter_routine_priv |
Alter_routine_priv |
|||
Create_tmp_table_priv |
Execute_priv |
Execute_priv |
|||
Lock_tables_priv |
|||||
Execute_priv |
|||||
Repl_slave_priv |
|||||
Repl_client_priv |
|||||
Create_view_priv |
|||||
Show_view_priv |
|||||
Create_routine_priv |
|||||
Alter_routine_priv |
|||||
Create_user_priv |
|||||
安全控制列 |
|||||
ssl_type |
|||||
ssl_cipher |
|||||
x509_issuer |
|||||
x509_subject |
|||||
资源控制列: |
|||||
max_questions :规定每个小时之内可以允许执行多少次数据查询 |
|||||
max_updates :规定每个小时之内可以允许执行多少次数据修改 |
|||||
max_connections :规定每个小时单个用户可以连接到少此 |
|||||
max_user_connections :规定单个用户同时连接多少数 |
说明: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匹配连接关系如下
三、权限分配管理:
权限可分为以下层级:
全局层级: 全局权限适用于一个给定服务器中的所有数据库。这些权限存储在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表中。
授权管理:根据操作人员的所在职责要求具体操作如下:
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:单个用户每小时可连接两次;
说明:对于修改数据库、表、视图的修改,存储过程和函数的创建和修改权限,授
权和资源控制权限一旦授权后存在风险,建议应该只有管理员拥有这些权限
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的连接;
帐户管理
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帐号 的名称进行更改。