MySQL用户和权限管理

原创

byte01 2022-12-27 20:10:22 ©著作权

文章标签 password Mysql 权限 mysql MySQL 文章分类 运维

©著作权归作者所有：来自51CTO博客作者byte01的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL用户和权限管理_MySQL

总共28个权限：下面是具体的权限介绍：转载的，记录一下：

一．权限表

mysql数据库中的3个权限表：user 、db、 host

权限表的存取过程是：

1)先从user表中的host、 user、 password这3个字段中判断连接的IP、用户名、密码是否存在表中，存在则通过身份验证；

2)通过权限验证，进行权限分配时，按照useràdbàtables_privàcolumns_priv的顺序进行分配。即先检查全局权限表user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db, tables_priv,columns_priv；如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推。

二．MySQL各种权限（共27个）

（以下操作都是以root身份登陆进行grant授权，以p1@localhost身份登陆执行各种命令。）

1. usage

连接（登陆）权限，建立一个用户，就会自动授予其usage权限（默认授予）。

mysql> grant usage on *.* to ‘p1′@’localhost’ identified by ‘123′;

该权限只能用于数据库登陆，不能执行任何操作；且usage权限不能被回收，也即REVOKE用户并不能删除用户。

2. select

必须有select的权限，才可以使用select table

mysql> grant select on pyt.* to ‘p1′@’localhost’;

mysql> select * from shop;

3. create

必须有create的权限，才可以使用create table

mysql> grant create on pyt.* to ‘p1′@’localhost’;

4. create routine

必须具有create routine的权限，才可以使用{create |alter|drop} {procedure|function}

mysql> grant create routine on pyt.* to ‘p1′@’localhost’;

当授予create routine时，自动授予EXECUTE, ALTER ROUTINE权限给它的创建者：

mysql> show grants for ‘p1′@’localhost’;

+—————————————————————————+

Grants for p1@localhost

+————————————————————————–+

| GRANT USAGE ON *.* TO ‘p1′@’localhost’ IDENTIFIED BY PASSWORD ‘*23AE809DDACAF96AF0FD78ED04B6A265E05AA257′ |

| GRANT SELECT, CREATE, CREATE ROUTINE ON `pyt`.* TO ‘p1′@’localhost’|

| GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `pyt`.`pro_shop1` TO ‘p1′@’localhost’ |

+————————————————————————————-+

5. create temporary tables(注意这里是tables，不是table)

必须有create temporary tables的权限，才可以使用create temporary tables.

mysql> grant create temporary tables on pyt.* to ‘p1′@’localhost’;

[mysql@mydev ~]$ mysql -h localhost -u p1 -p pyt

mysql> create temporary table tt1(id int);

6. create view

必须有create view的权限，才可以使用create view

mysql> grant create view on pyt.* to ‘p1′@’localhost’;

mysql> create view v_shop as select price from shop;

7. create user

要使用CREATE USER，必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。

mysql> grant create user on *.* to ‘p1′@’localhost’;

或：mysql> grant insert on *.* to p1@localhost;

8. insert

必须有insert的权限，才可以使用insert into ….. values….

9. alter

必须有alter的权限，才可以使用alter table

alter table shop modify dealer char(15);

10. alter routine

必须具有alter routine的权限，才可以使用{alter |drop} {procedure|function}

mysql>grant alter routine on pyt.* to ‘p1′@’ localhost ‘;

mysql> drop procedure pro_shop;

Query OK, 0 rows affected (0.00 sec)

mysql> revoke alter routine on pyt.* from ‘p1′@’localhost’;

[mysql@mydev ~]$ mysql -h localhost -u p1 -p pyt

mysql> drop procedure pro_shop;

ERROR 1370 (42000): alter routine command denied to user ‘p1′@’localhost’ for routine ‘pyt.pro_shop’

11. update

必须有update的权限，才可以使用update table

mysql> update shop set price=3.5 where article=0001 and dealer=’A';

12. delete

必须有delete的权限，才可以使用delete from ….where….(删除表中的记录)

13. drop

必须有drop的权限，才可以使用drop database db_name; drop table tab_name;

drop view vi_name; drop index in_name;

14. show database

通过show database只能看到你拥有的某些权限的数据库，除非你拥有全局SHOW DATABASES权限。

对于p1@localhost用户来说，没有对mysql数据库的权限，所以以此身份登陆查询时，无法看到mysql数据库：

mysql> show databases;

+——————–+

| Database |

+——————–+

| information_schema|

| pyt |

| test |

+——————–+

15. show view

必须拥有show view权限，才能执行show create view。

mysql> grant show view on pyt.* to p1@localhost;

mysql> show create view v_shop;

16. index

必须拥有index权限，才能执行[create |drop] index

mysql> grant index on pyt.* to p1@localhost;

mysql> create index ix_shop on shop(article);

mysql> drop index ix_shop on shop;

17. excute

执行存在的Functions,Procedures

mysql> call pro_shop1(0001,@a)；

+———+

| article |

+———+

| 0001 |

+———+

mysql> select @a;

+——+

| @a |

+——+

| 2 |

+——+

18. lock tables

必须拥有lock tables权限，才可以使用lock tables

mysql> grant lock tables on pyt.* to p1@localhost;

mysql> lock tables a1 read;

mysql> unlock tables;

19. references

有了REFERENCES权限，用户就可以将其它表的一个字段作为某一个表的外键约束。

20. reload

必须拥有reload权限，才可以执行flush [tables | logs | privileges]

mysql> grant reload on pyt.* to p1@localhost;

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

mysql> grant reload on *.* to ‘p1′@’localhost’;

Query OK, 0 rows affected (0.00 sec)

mysql> flush tables;

21. replication client

拥有此权限可以查询master server、slave server状态。

mysql> show master status;

ERROR 1227 (42000): Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation

mysql> grant Replication client on *.* to p1@localhost;

或：mysql> grant super on *.* to p1@localhost;

mysql> show master status;

+——————+———-+————–+——————+

+——————+———-+————–+——————+

| mysql-bin.000006 | 2111 | | |

+——————+———-+————–+——————+

mysql> show slave status;

22. replication slave

拥有此权限可以查看从服务器，从主服务器读取二进制日志。

mysql> show slave hosts;

ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation

mysql> show binlog events;

ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation

mysql> grant replication slave on *.* to p1@localhost;

mysql> show slave hosts;

Empty set (0.00 sec)

mysql>show binlog events;

+—————+——-+—————-+———–+————-+————–+

+—————+——-+————–+———–+————-+—————+

| mysql-bin.000005 | 4 | Format_desc | 1 | 98 | Server ver: 5.0.77-log, Binlog ver: 4 | |mysql-bin.000005|98|Query|1|197|use `mysql`; create table a1(i int)engine=myisam|

……………………………………

23. Shutdown

关闭MySQL：

[mysql@mydev ~]$ mysqladmin shutdown

重新连接：

[mysql@mydev ~]$ mysql

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

[mysql@mydev ~]$ cd /u01/mysql/bin

[mysql@mydev bin]$ ./mysqld_safe &

[mysql@mydev bin]$ mysql

24. grant option

拥有grant option，就可以将自己拥有的权限授予其他用户（仅限于自己已经拥有的权限）

mysql> grant Grant option on pyt.* to p1@localhost;

mysql> grant select on pyt.* to p2@localhost;

25. file

拥有file权限才可以执行 select ..into outfile和load data infile…操作，但是不要把file, process, super权限授予管理员以外的账号，这样存在严重的安全隐患。

mysql> grant file on *.* to p1@localhost;

mysql> load data infile ‘/home/mysql/pet.txt’ into table pet;

26. super

这个权限允许用户终止任何查询；修改全局变量的SET语句；使用CHANGE MASTER，PURGE MASTER LOGS。

mysql> grant super on *.* to p1@localhost;

mysql> purge master logs before ‘mysql-bin.000006′;

27. process

通过这个权限，用户可以执行SHOW PROCESSLIST和KILL命令。默认情况下，每个用户都可以执行SHOW PROCESSLIST命令，但是只能查询本用户的进程。

mysql> show processlist;

+—-+——+———–+——+———+——+——-+——————+

+—-+——+———–+——+———+——+——-+——————+

| 12 | p1 | localhost | pyt | Query | 0 | NULL | show processlist |

+—-+——+———–+——+———+——+——-+——————+

另外，

管理权限（如 super， process， file等）不能够指定某个数据库，on后面必须跟*.*

mysql> grant super on pyt.* to p1@localhost;

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

mysql> grant super on *.* to p1@localhost;

Query OK, 0 rows affected (0.01 sec)

MySQL用户权限表

MySQL的认证是“用户”加“主机”而权限是访问资源对象，MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库中，由mysql_install_db脚本初始化。存储账户权限信息表主要有：user,db,tables_priv,columns_priv,procs_priv这五张表（5.6之前还有host表，现在已经把host内容整合进user表），五张表其含义分别是：

user表

user表时MySQL中最重要的一个权限表，记录允许连接到服务器的账号信息，里面的权限是全局级的。例如：一个用户在user表中被授予了DELETE权限，则该用户可以删除MySQL服务器上所有数据库的任何记录。MySQL5.6中user表有43个字段，这些字段大概可以分为4类，分别是用户列、权限列、安全列和资源控制列，详细解释如下图：

MySQL用户和权限管理_password_02

db表

db表存储了用户对某个数据库的操作权限，决定用户能从哪个主机存储哪个数据库。User表中存储了某个主机对数据库的操作权限，配置和db权限表对给定主机上数据库级操作权限做更细致的控制。这个权限表不受GRANT和REVOKE语句的影响，字段大致可以分为两类：用户列和权限列，详细解释如下图：

MySQL用户和权限管理_Mysql_03

tables_priv and columns_priv表

tables_priv表用来对表设置操作权限，有8个字段分别是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv和Column_priv，各个字段说明如下：

1） Host、Db、User和Table_name4个字段分表示主机名、数据库名、用户名和表明。

2） Grantor表示修改该记录的用户。

3） Timestamp字段表示修改该记录的时间。

4） Table_priv表示对表的操作权限包括、select、insert、update、delete、create、drop、grant、references、index和alter。

5） Column_priv字段表示对表中的列的操作权限，包括select、insert、update和references。

procs_priv表

存储过程和存储函数相关的权限，分别是Host、Db、User、Routine_name、Routine_type、Grantor、Proc_priv和Timestamp，各个字段的说明如下：

1） Host、Db和User字段分别表示主机名、数据库名和用户名。Routine_name表示存储过程或函数的名称。

2） Routine_type表示存储过程或函数的类型。

3） Routine_type字段有两个值，分别是FUNCTION和PROCEDURE。FUNCTION表示这是一个函数；PROCEDURE表示这是一个存储过程。

4） Grantor是插入或修改该记录的用户。

5） Proc_priv表示拥有的权限，包括Execute、Alter Routine、Grant这3种。

6） Timestamp表示记录更新时间。

MySQL访问控制两阶段

阶段1：客户端连接核实阶段

阶段2：客户端操作核实阶段

客户端连接核实阶段，当连接MySQL服务器时，服务器基于用户的身份以及用户是否能通过正确的密码身份验证，来接受或拒绝连接。即客户端用户连接请求中会提供用户名称、主机地址和密码，MySQL使用user表中的三个字段（Host、User、Password）执行身份检查，服务器只有在user表记录的Host和User字段匹配客户端主机名和用户名，并且提供正确的面貌时才接受连接。如果连接核实没有通过，服务器完全拒绝访问；否则，服务器接受连接，然后进入阶段2等待用户请求。

客户端操作核实阶段，当客户端的连接请求被MySQL服务器端通过其身份认证后。那么接下来就可以发送数据库的操作命令给服务器端处理，服务器检查用户要执行的操作，在确认权限时，MySQL首先检查user表，如果指定的权限没有在user表中被授权；MySQL将检查db表，db表时下一安全层级，其中的权限限定于数据库层级，在该层级的SELECT权限允许用户查看指定数据库的所有表中的数据；如果在该层级没有找到限定的权限，则MySQL继续检查tables_priv表以及columns_priv表，如果所有权限表都检查完毕，但还是没有找到允许的权限操作，MySQL将返回错误信息，用户请求的操作不能执行，操作失败。其过程大概如下图：

MySQL用户和权限管理_Mysql_04

MySQL用户及密码管理

MySQL提供许多语句用来管理用户账号，这些语句可以用来管理包括登陆和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。MySQL数据库的安全性，需要通过账户管理来保证。下面介绍四种用来管理账号密码的方式：

1）通过Mysqladmin工具（只能改密码）

1 2 3 4 5	# 给root@localhost用户登录mysql设置密码为"redhat"; $mysqladmin-uroot-hlocalhost password"redhat" # 修改root@localhost用户登录Mysql数据库的密码; $mysqladmin-uroot-hlocalhost password"new passwd"-p"old passwd"

2）通过CREATE USER语句

1	Mysql>create user'USERNAME'@'HOST'identified by'PASSWORD';

创建登录用户，MySQL的登录用户必须是’USERNAME’@’HOST’（用户名加主机名），如’mysql’@’172.16.16.1′，含义是只有在172.16.16.1这台主机上才可以使用mysql用户登录MySQL数据库（还可以指定只允许登录那个数据库）。

HOST的表现方式：

1. IP地址，如172.16.16.1；

2. 主机名，如localhost；

3. 网络地址，如172.16.0.0

4. 通配符，如

%：匹配任意字符

_：匹配任意单个字符如172.16.16._(允许172.16.16.1-172.16.16.9)

1 2 3 4 5 6 7 8 9 10 11 12 13 14	# 创建用户允许任何主机登录以USERNAME用户登录; Mysql>create user'USERNAME'@'%'identified by'PASSWORD'; # 更改用户名; Mysql>rename user OLD_NAME toNEW_NAME; # 删除登录用户; Mysql>drop user'USERNAME'@'HOST'; # 删除MySQL默认的无用账户; mysql>drop user'root'@'localhost.localdomain'; # 删除MySQL默认的无用账户; mysql>drop user'root'@'127.0.0.1';

3）通过直接修改mysql.user表的用户记录

1	Mysql>update mysql.userset password=PASSWORD('redhat')where user='root';

或

1 2	# 给root@localhost用户登录mysql设置密码为redhat并以加密方式存放; Mysql>set password for'root'@'localhost'=PASSWORD('redhat');

1 2	# 刷新内存中现有的表 Mysql>flush privileges;

4）通过GRANT指令（只能用于添加新用户）

1 2	# 创建tom用户并对此库下的所有表赋予所有权限; Mysql>grant all on DB_NAME.*to'tom'@'localhost'identified by'1234';

虽然介绍了好几种方法创建用户，但真正在使用中，最好直接使用GRANT或者CREATE USER语句，而不要直接将用户信息插入user表中，因为user表中存储了全局级别的权限以及其他的账户信息，如果意外破坏了user表中的记录，则可能会对MySQL服务器造成很大的影响。

MySQL管理员密码找回

1）关闭MySQL

1	$service mysqld stop

2）在配置文件中[mysqld]字段添加skip-grant-tables指令，跳过授权表

1 2 3	$cat/etc/my.cnf [mysqld] skip-grant-tables

3）给root用户登录mysql设置密码为redhat并以加密方式

1 2	Mysql>usemysql; Mysql>update user set password=PASSWORD('redhat')where user='root';

MySQL5.7修改密码

1	Mysql>update mysql.userset authentication_string=PASSWORD('redhat')where user='root';

MySQL权限管理

权限管理主要是对登录到MySQL的用户进行权限验证，所有用户的权限都存储在MySQL的权限表中，不合理的权限规划会给MySQL服务器带来安全隐患。数据库管理员要对所有用户的权限进行合理规划管理。MySQL权限系统的主要功能时证实连接到一台给定主机的用户，并且赋予该用户在数据库上的SELECT/INSERT/UPDATE和DELETE权限。

一、MySQL权限说明

账户权限信息被存储在MySQL数据库的几张权限表中，在MySQL启动时，服务器将这些数据库表中权限信息的内容读入内存。其中GRANT和REVOKE语句所涉及的常用权限大致如下这些：CREATE、DROP、SELECT、INSERT、UPDATE、DELETE、INDEX、ALTER、CREATE、ROUTINE、FILE等，还有一个特殊的proxy权限，是用来赋予某个用户具有给他人赋予权限的权限。

二、MySQL用户授权

授权就是为某个用户授予权限，合理的授权可以保证数据库的安全，MySQL中可以使用GRANT语句为用户授予权限。授权可以分为多个层次：

全局层级：全局权限适用于一个给定服务器中的所有数据库，这些权限存储在mysql.user表中。

数据库层级：数据库权限适用于一个给定数据库中的所有目标，这些权限存储在mysql.db表中。

表层级：表权限适用于一个给定表中的所有列，这些权限存储在mysql.tables_priv表中。

列层级：列权限使用于一个给定表中的单一列，这些权限存储在mysql.columns_priv表中。

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

PS：MySQL中必须拥有GRANT权限的用户才可以执行GRANT语句。

1）GRANT赋予用户权限

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20	# 定义对已经存在的用户可以操作此库下的所有表及所有权限； Mysql>grant all privileges on DB_NAME.to'USERNAME'@'HOST'; # 创建tom用户并赋予select权限对此库下的所有表； Mysql>grant select on DB_NAME.to'tom'@'localhost'identified by'1234'; # 定义tom用户赋予insert权限对db库下的xsb表； Mysql>grant insert on db.xsbto'tom'@'localhost'; # 定义tom用户赋予update权限对db库下的xsb表； Mysql>grant update on db.xsbto'tom'@'localhost'; # 定义tom用于赋予update权限对db库下的xsb表中的AGE字段； Mysql>grant update(AGE)on db.xsbto'tom'@'localhost'; # 定义tom用于赋予super权限在.上(super权限可以对全局变量更改)； Mysql>grant superon.to'tom'@'%'; # 通过GRANT语句中的USAGE权限，你可以创建账户而不授予任何权限；它可以将所有全局权限设为'N'，假定你将在以后将具体权限授予该账户； Mysql>grant usage on.to'tom'@'%';

all表示赋予用户全部权限（包含存储过程、存储函数等创建和执行）。当数据库名称.表名称被*.*代替，表示赋予用户操作服务器上所有数据库所有表的权限。用户地址可以是localhost，也可以是ip地址、机器名字、域名。也可以用’%’表示从任何地址连接。而’连接口令’不能为空，否则创建失败。

2）REVOKE移除用户权限

1 2 3 4 5	# 移除tom用户对于db.xsb的权限; Mysql>revoke all on db.xsbfrom'tom'@'localhost'; # 刷新授权表; Mysql>flush privileges;

3）SHOW查看用户的权限

1	Mysql>show grants for'USERNAME'@'HOST';

PS：使用REVOKE收回权限之后，用户帐户的记录将从db、host、tables_priv、columns_priv表中删除，但是用户帐号记录依然在user表中保存。

三、PROXY特殊权限

如果想让某个用户具有给他人赋予权限的能力，那么就需要proxy权限了。当你给一个用户赋予all权限之后，你查看mysql.user表会发现Grant_priv字段还是为N，表示其没有给他人赋予权限的权限。

我们可以查看一下系统默认的超级管理员权限：

1 2 3 4 5 6 7 8	mysql>show grants for'root'@'localhost'; +---------------------------------------------------------------------+ \|Grants forroot@localhost\| +---------------------------------------------------------------------+ \|GRANT ALL PRIVILEGES ON.TO'root'@'localhost'WITH GRANT OPTION\| \|GRANT PROXY ON''@''TO'root'@'localhost'WITH GRANT OPTION\| +---------------------------------------------------------------------+ 2rows inset(0.00sec)

可以看到其本身有PROXY权限，并且这个语句跟一般授权语句还不太一样。所以如果想让一个远程用户有给他人赋予权限的能力，就需要给此用户PROXY权限，如下：

1 2	mysql>grant all on.to'test'@'%'identified by'123456'; mysql>GRANT PROXY ON''@''TO'test'@'%'WITH GRANT OPTION;

四、数据库开发人员，创建表、索引、视图、存储过程、函数等权限授权

grant创建、修改、删除MySQL数据表结构权限

1 2 3	grant create on testdb.todeveloper@'192.168.0.%'; grant alter on testdb.todeveloper@'192.168.0.%'; grant drop on testdb.*todeveloper@'192.168.0.%';

grant操作MySQL外键权限

1	grant references on testdb.*todeveloper@'192.168.0.%';

grant操作MySQL临时表权限。

1	grant create temporary tables on testdb.*todeveloper@'192.168.0.%';

grant操作MySQL索引权限

1	grant index on testdb.*todeveloper@'192.168.0.%';

grant操作MySQL视图、查看视图源代码权限

1 2	grant create view on testdb.todeveloper@'192.168.0.%'; grant show view on testdb.todeveloper@'192.168.0.%';

grant操作MySQL存储过程、存储函数权限

1 2 3	grant create routine on testdb.todeveloper@'192.168.0.%'; grant alter routine on testdb.todeveloper@'192.168.0.%'; grant execute on testdb.*todeveloper@'192.168.0.%';

等等。。。

http://www.ywnds.com/?p=3714

MySQL修改密码

第一种方式：

最简单的方法就是借助第三方工具Navicat for MySQL来修改，方法如下：

1、登录mysql到指定库，如：登录到test库。