MySQL用户和权限管理

时间：2024-02-19 08:50:01


 

 

总共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 |

| 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;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| 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;

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

| Log_name | Pos | Event_type | Server_id| End_log_pos|Info | 

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

| 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;

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

| Id | User | Host | db | Command | Time | State | Info |

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

| 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)

https://blog.csdn.net/wulantian/article/details/38230635





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用户和权限管理

db表

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

MySQL用户和权限管理

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用户及密码管理

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

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

2）通过CREATE USER语句

创建登录用户，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)

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

或

 

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

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

MySQL管理员密码找回

1）关闭MySQL

 

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

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

MySQL5.7修改密码

 

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赋予用户权限

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

2）REVOKE移除用户权限

3）SHOW查看用户的权限

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

三、PROXY特殊权限

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

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

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

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

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

grant操作MySQL外键权限

grant操作MySQL临时表权限。

grant操作MySQL索引权限

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

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

等等。。。

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



MySQL修改密码

第一种方式：

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

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

2、然后点击上方“用户”按钮。

3、选择要更改的用户名，然后点击上方的“编辑用户”按钮。

4、出现如图界面，输入新密码，并确认新密码，点击“保存”按钮即可。

 

 

第二种方式：

方法1： 用SET PASSWORD命令   

首先登录MySQL。  

格式：mysql> set password for 用户名@localhost = password(\'新密码\');  

例子：mysql> set password for root@localhost = password(\'123\');  

方法2：用mysqladmin   

格式：mysqladmin -u用户名 -p旧密码 password 新密码  

例子：mysqladmin -uroot -p123456 password 123  

方法3：用UPDATE直接编辑user表   

首先登录MySQL。  

mysql> use mysql;  

mysql> update user set password=password(\'123\') where user=\'root\' and host=\'localhost\';  

mysql> flush privileges;  

方法4：在忘记root密码的时候，可以这样   

以windows为例：   

1. 关闭正在运行的MySQL服务。  

2. 打开DOS窗口，转到mysql\bin目录。  

3. 输入mysqld --skip-grant-tables 回车。--skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。  

4. 再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysql\bin目录。  

5. 输入mysql回车，如果成功，将出现MySQL提示符 >。  

6. 连接权限数据库： use mysql; 。  

7. 改密码：update user set password=password("123") where user="root";（别忘了最后加分号） 。  

8. 刷新权限（必须步骤）：flush privileges;　。  

 9. 退出 quit。  

10. 注销系统，再进入，使用用户名root和刚才设置的新密码123登录。

第三种方式：

  1. 打开mysql.exe和mysqld.exe所在的文件夹,复制路径地址

     

     

     
  2. 2

    打开cmd命令提示符，进入上一步mysql.exe所在的文件夹

     

     

  3. 3

    输入命令  mysqld --skip-grant-tables  回车，此时就跳过了mysql的用户验证。注意输入此命令之后命令行就无法操作了，此时可以再打开一个新的命令行。注意：在输入此命令之前先在任务管理器中结束mysqld.exe进程，确保mysql服务器端已结束运行。

     

     

  4. 4

    然后直接输入mysql，不需要带任何登录参数直接回车就可以登陆上数据库。

     

     

  5. 5

    输入show databases;   可以看到所有数据库说明成功登陆。

     

     

  6. 6

    其中mysql库就是保存用户名的地方。输入 use mysql;   选择mysql数据库。

     

     

  7. 7

    show tables查看所有表，会发现有个user表，这里存放的就是用户名，密码，权限等等账户信息。

     

     

  8. 8

    输入select user,host,password from user;   来查看账户信息。

     

     

  9. 9

    更改root密码，输入update user set password=password(\'123456\') where user=\'root\' and host=\'localhost\';

     

     

  10. 10

    再次查看账户信息，select user,host,password from user;   可以看到密码已被修改。

     

     

  11. 11

    退出命令行，重启mysql数据库，用新密码尝试登录。

     

     

  12. 12

    测试不带密码登录mysql,发现还是能够登陆上，但显示数据库时只能看到两个数据库了，说明重启之后跳过密码验证已经被取消了。

     

     

  13. 13

    我这地方重启数据库之后之所以不带密码任然能够登录是因为我的数据库里存在设无须口令的账户。

     

     

 

相关文章