mysql权限管理

来源：互联网发布：东北乌托帮网络大电影编辑：程序博客网时间：2024/05/16 07:28

mysql的访问分两个阶段：

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

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

二、 权限控制

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

表名

含义

user

控制“用户名”（user）可以从哪一台“主机名”(host)访问mysql

该表包括全局权限的控制

规定哪个用户可以访问哪个数据库

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

User

Password

User

数据库/表权限控制列

Select_priv

Table_name

Routine_name

Insert_priv

Grantor

Column_name

Routine_type

Update_priv

Timestamp

Grantor

Delete_priv

Table_priv

Column_priv

Proc_priv

Create_priv

Column_priv

Timestamp

Drop_priv

Reload_priv

Grant_priv

Shutdown_priv

References_priv

Process_priv

Index_priv

File_priv

Alter_priv

Grant_priv

Create_tmp_table_priv

References_priv

Lock_tables_priv

Index_priv

Create_view_priv

Alter_priv

Show_view_priv

Show_db_priv

Create_routine_priv

Super_priv

Alter_routine_priv

Create_tmp_table_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为例)

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值

匹配连接

Localhost

Root

Root用户，从本地可以连接

192.168.1.%

Root

Root用户，从192.168.1的子网可以连接

192.168.1.2

Root

Root用户，从192.168.1.2的主机可以连接

192.168.1.2

空值

任何用户，从192.168.1.2的主机都可以连接

Root

Root用户，从任何主机都可以连接

空值

任何用户，任何主机都可以连接

空值

任何用户，任何主机都可以连接

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帐号的名称进行更改。