我的MYSQL学习心得 mysql的权限管理

时间：2023-09-22 09:32:14

这一篇《我的MYSQL学习心得（十三）》将会讲解MYSQL的用户管理

在mysql数据库中，有mysql_install_db脚本初始化权限表，存储权限的表有：

1、user表

2、db表

3、host表

4、table_priv表

5、columns_priv表

6、proc_priv表

MySQL存取控制包含2个阶段：

  • 阶段1：服务器检查你是否允许连接。
  • 阶段2：假定你能连接，服务器检查你发出的每个请求。看你是否有足够的权限实施它。例如，如果你从数据库中一个表精选(select)行或从数据库抛弃一个表，服务器确定你对表有select权限或对数据库有drop权限。

服务器在存取控制的两个阶段使用在mysql的数据库中的user、db和host表，在这些授权表中字段如下：

表名称 user db host
范围字段 Host Host Host
  User Db Db
  Password User  
权限字段 Select_priv Select_priv Select_priv
  Insert_priv Insert_priv Insert_priv
  Update_priv Update_priv Update_priv
  Delete_priv Delete_priv Delete_priv
  Index_priv Index_priv Index_priv
  Alter_priv Alter_priv Alter_priv
  Create_priv Create_priv Create_priv
  Drop_priv Drop_priv Drop_priv
  Grant_priv Grant_priv Grant_priv
  Reload_priv    
  Shutdown_priv    
  Process_priv    
  File_priv    

对存取控制的第二阶段(请求证实)，如果请求涉及表，服务器可以另外参考tables_priv和columns_priv表。这些表的字段如下：

表名称 tables_priv columns_priv
范围字段 Host Host
  Db Db
  User User
  Table_name Table_name
    Column_name
权限字段 Table_priv Column_priv
  Column_priv  
其他字段 Timestamp Timestamp
  Grantor  

每个授权表包含范围字段和权限字段。

user表主要分为：用户列、权限列、安全列、资源控制列

host表主要分为：用户列、权限列

这里美中不足的是mysql.user 没有一个列是保存用户创建时间的

有时候排查用户问题的时候，比如某个客户在某个时间说连接不上数据库，我们在user表里只能查到是否存在那个用户

但是不知道这个用户的创建时间，也就是说客户说的那个时间究竟用户是否已经创建我们是不知道的

帐户管理

MYSQL提供许多语句用来管理用户帐号，这些语句可以用来包括登录和退出MYSQL服务器、创建用户、删除用户、密码管理、权限管理

MYSQL数据库的安全性，需要通过帐户管理来保证

登录和退出MYSQL

mysql命令的常用参数

-h：主机名或ip，默认是localhost，最好指定-h参数

-u：用户名

-p：密码，注意：该参数后面的字符串和-p不能有空格

-P：端口号，默认为3306

数据库名：可以在命令最后指定数据库名

-e：执行SQL语句，如果指定该参数，将在登录后执行-e后面的命令或sql语句并退出

我的MYSQL学习心得 mysql的权限管理

命令执行完之后返回book表的结构，查询返回之后会自动退出MYSQL

用户

CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']

    [, user [IDENTIFIED BY [PASSWORD] 'password']]

新建普通用户

CREATE USER 'jeffrey'@'localhost' identified BY 'mypass';

用户名部分为“jeffrey”，主机名默认为“%”（即对所有主机开放权限）

如果指定用户登录不需要密码，则可以省略identified BY部分

对于使用插件认证连接的用户，服务器调用指定名称的插件，客户端需要提供验证方法所需要的凭据。

如果创建用户时或者连接服务器时，服务器找不到对应的插件，将返回一个错误

identified with语法

CREATE user 'jeffrey'@'localhost' identified with my_auth_plugin;

identified with只能在MYSQL5.5.7及以上版本使用。

identified with和identified by是互斥的，所以对一个帐户来说只能使用一个验证方法。

CREATE USER语句的操作会被记录到服务器日志文件或者操作历史文件中

例如 ~/.mysql_history。这意味着对这些文件有读取权限的人，都可以读取到新添加用户的明文密码

我的MYSQL学习心得 mysql的权限管理

一个办法就是新建用户的时候使用password关键字

CREATE user 'tom'@'localhost' identified BY password'*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';

SELECT password('mypass');

SELECT * FROM `mysql`.`user` WHERE `User` ='tom';

先查出你的密码的哈希值，然后在新建用户的时候输入哈希值

那么在日志里面就只能看到哈希值

我的MYSQL学习心得 mysql的权限管理

我的MYSQL学习心得 mysql的权限管理

使用GRANT语句创建新用户

GRANT USER语句可以用来创建帐户，通过该语句可以在user表中添加一条新记录

比起CREATE USER语句创建的新用户，还需要使用GRANT语句赋予用户权限

使用GRANT语句创建新用户时必须有GRANT权限。

语法

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...

    ON [object_type] {tbl_name | * | *.* | db_name.*}

    TO user [IDENTIFIED BY [PASSWORD] 'password']

        [, user [IDENTIFIED BY [PASSWORD] 'password']] ...

    [REQUIRE

        NONE |

        [{SSL| X509}]

        [CIPHER 'cipher' [AND]]

        [ISSUER 'issuer' [AND]]

        [SUBJECT 'subject']]

    [WITH with_option [with_option] ...]

使用GRANT语句创建一个新用户testUser，密码为testpwd，并授予用户对所有数据表的SELECT和UPDATE权限

GRANT SELECT ,UPDATE ON *.* TO 'testUser'@'localhost' identified BY 'testpwd'
SELECT `Host` ,`User` ,`Select_priv` ,`Update_priv`  FROM mysql.user WHERE `User` ='testUser';

执行结果显示执行成功，使用SELECT语句查询用户testUser的权限

我的MYSQL学习心得 mysql的权限管理

查询结果显示SELECT和UPDATE权限字段均为Y

注意：User表中的user和host字段区分大小写，在查询的时候要指定正确的用户名或主机名

直接操作MYSQL用户表

不管是CREATE USER还是GRANT USER，在创建用户时，实际上都是在user表中添加一条新记录。

使用INSERT语句向mysql.user表INSERT一条记录来创建一个新用户

插入的时候必须要有INSERT权限

INSERT INTO mysql.user(host,user,password,[privilegelist])

VALUES ('host','username',password('password'),privilegevaluelist)

使用INSERT创建一个新用户，其用户名称为customer1，主机名为localhost，密码为customer1

INSERT INTO mysql.user(host,user,password)

VALUES ('localhost','customer1',password('customer1'))

我的MYSQL学习心得 mysql的权限管理

语句执行失败，查看警告信息如下：

show WARNINGS ;

我的MYSQL学习心得 mysql的权限管理

因为ssl_cipher这个字段在user表中没有定义默认值，所以在这里提示错误信息。

影响insert语句的执行，使用SELECT语句查看user表中的记录

我的MYSQL学习心得 mysql的权限管理

可以看到，插入失败

删除普通用户

使用DROP USER语句删除用户，也可以直接通过DELETE从mysql.user表中删除对应的记录来删除用户

DROP USER语句用于删除一个或多个MYSQL帐户。要使用DROP USER，必须拥有MYSQL数据库的全局

CREATE USER 权限或DELETE权限。

删除testUser这个用户

DROP user 'testUser'@'localhost';

我的MYSQL学习心得 mysql的权限管理

我的MYSQL学习心得 mysql的权限管理

可以发现testUser这个用户已经删除了

使用delete语句删除用户

DELETE FROM mysql.user WHERE `Host`='localhost' and `User`='testUser'

root用户修改自己的密码

修改root密码的方式有多种

1、使用mysqladmin命令在命令行指定新密码

mysqladmin -u root -p password"rootpwd"

2、修改mysql数据库的user表

UPDATE mysql.user SET `Password` =password('rootpwd') WHERE `User`='root' and `Host`='localhost'

password('')函数用来加密用户密码。执行update之后需要执行flush privileges语句重新加载用户权限

3、使用SET语句修改root用户的密码

SET PASSWORD语句可以用来重新设置其他用户的登录密码或者自己使用的帐户密码

语法

SET PASSWORD=PASSWORD("ROOTPWD")

新密码必须用PASSWORD函数加密

使用root用户登录到mysql之后执行下面语句

SET password=password('123456')

执行之后需要使用执行flush privileges语句或者重启MYSQL重新加载用户权限

root用户修改普通用户密码

1、使用SET语句修改普通用户的密码

SET PASSWORD FOR 'USER'@'HOST' =PASSWORD("ROOTPWD")

2、使用update语句修改普通用户的密码

UPDATE mysql.user SET `Password` =password('rootpwd') WHERE `User`='root' and `Host`='localhost'

执行完毕之后需要使用flush privileges语句或者重启MYSQL重新加载用户权限

3、使用GRANT语句修改普通用户密码

GRANT USAGE ON *.* TO 'someuser'@'%'  IDENTIFIED BY 'somepwd'

使用下面语句把testUser用户的密码改为123456

grant USAGE ON *testUser*TO 'localhost' identified BY '123456';

注意：使用GRANT语句和MYSQLADMIN设置密码，他们均会加密密码，这种情况下，不需要使用PASSWORD()函数

普通用户修改密码

使用SET语句修改自己的密码

SET password=password('newpassword');

比如修改testUser这个用户的密码，需要使用testUser这个用户登录到mysql，然后执行

SET password=password('123456');

root用户密码丢失的解决办法

使用--skip-grant-tables选项启动MYSQL服务

使用--skip-grant-tables选项启动MYSQL时，服务器将不加载权限判断，任何用户都能访问数据库

LINUX下

使用mysqld_safe来启动MYSQL服务，也可以使用/etc/init.d/mysql命令来启动mysql

mysqld_safe --skip-grant-tables user=mysql

或者

/etc/init.d/mysql start-mysqld --skip-grant-tables

启动MYSQL服务后，就可以使用root用户登录了

Windows下

详细可以看一下这篇文章

Windows mysql提示：1045 access denied for user 'root'@'localhost' using password yes

权限管理

MYSQL中的各种权限

对于GRANT和REVOKE语句，priv_type可以被指定为以下任何一种：

权限

意义

ALL [PRIVILEGES]

设置除GRANT OPTION之外的所有简单权限

ALTER

允许使用ALTER TABLE

ALTER ROUTINE

更改或取消已存储的子程序

CREATE

允许使用CREATE TABLE

CREATE ROUTINE

创建已存储的子程序

CREATE TEMPORARY TABLES

允许使用CREATE TEMPORARY TABLE

CREATE USER

允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。

CREATE VIEW

允许使用CREATE VIEW

DELETE

允许使用DELETE

DROP

允许使用DROP TABLE

EXECUTE

允许用户运行已存储的子程序

FILE

允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE

INDEX

允许使用CREATE INDEX和DROP INDEX

INSERT

允许使用INSERT

LOCK TABLES

允许对您拥有SELECT权限的表使用LOCK TABLES

PROCESS

允许使用SHOW FULL PROCESSLIST

REFERENCES

未被实施

RELOAD

允许使用FLUSH

REPLICATION CLIENT

允许用户询问从属服务器或主服务器的地址

REPLICATION SLAVE

用于复制型从属服务器（从主服务器中读取二进制日志事件）

SELECT

允许使用SELECT

SHOW DATABASES

SHOW DATABASES显示所有数据库

SHOW VIEW

允许使用SHOW CREATE VIEW

SHUTDOWN

允许使用mysqladmin shutdown

SUPER

允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。

UPDATE

允许使用UPDATE

USAGE

“无权限”的同义词

GRANT OPTION

允许授予权限

当从旧版本的MySQL升级时，要使用EXECUTE, CREATE VIEW, SHOW VIEW, CREATE USER, CREATE ROUTINE和ALTER ROUTINE权限

授权

授权就是为某个用户授予权限

授予的权限可以分为多个层级：

·         全局层级

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

·         数据库层级

数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。GRANT ALL ONdb_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表中。

当后续目标是一个表、一个已存储的函数或一个已存储的过程时，object_type子句应被指定为TABLE、FUNCTION或PROCEDURE。当从旧版本的MySQL升级时，要使用本子句，您必须升级您的授权表

使用GRANT语句创建一个新用户grantUser，密码为“grantpwd”

用户对所有的数据有查询、插入权限，并授予GRANT权限

GRANT SELECT ,INSERT ON *.*TO 'grantUser'@'localhost' identified BY '123456' WITH GRANT OPTION ;

我的MYSQL学习心得 mysql的权限管理

查询显示grantUser被创建成功，并赋予了SELECT、INSERT、GRANT权限，其相应字段值为Y

被授予GRANT权限的用户可以登录MYSQL并创建其他用户帐户，在这里是grantUser的用户

收回权限

收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。

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

在user表中保存。

语法

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...

    ON [object_type] {tbl_name | * | *.* | db_name.*}

    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

使用REVOKE语句，必须拥有mysql数据库的全局CREATE权限或UPDATE权限

使用REVOKE语句取消用户grantUser的INSERT权限

REVOKE INSERT ON *.* FROM 'grantUser'@'localhost';

我的MYSQL学习心得 mysql的权限管理

可以看到grantUser用户的INSERT权限已经被收回了

注意：当从旧版本的MYSQL升级时，如果要使用EXECUTE、CREATE VIEW、SHOW VIEW、CREATE USER、CREATE ROUTINE、ALTER ROUTINE

权限，必须先升级授权表

查看权限

SHOW GRANT语句可以显示用户的权限信息

语法

show grants FOR 'user'@'host';

使用SHOW GRANT语句查询用户grantUser的权限信息

show grants FOR 'grantUser'@'localhost';

我的MYSQL学习心得 mysql的权限管理

返回结果显示了user表中的帐户信息；接下来以为GRANT SELECT ON关键字开头，表示用户被授予了SELECT权限；

*.*表示SELECT权限作用于所有数据库的所有数据表；

IDENTIFIED BY 后面的为用户加密后的密码

在这里，只是定义了个别的用户权限，GRANT可以显示更加详细的权限信息，包括全局级的和非全局级的权限

如果表层级或者列层级的权限被授予用户的话，他们也能在结果中显示出来。

查看MYSQL里面匿名用户

如果有匿名用户，那么客户端就可以不用密码登录MYSQL数据库，这样就会存在安全隐患

检查匿名用户的方法

SELECT * FROM mysql.user WHERE `User`='';

我的MYSQL学习心得 mysql的权限管理

如果查找到user字段值为空的那条记录，说明存在匿名用户，需要把这条记录删除

如果用匿名用户登录MYSQL就可以看到用户名是空的

我的MYSQL学习心得 mysql的权限管理

删除语句

DELETE FROM mysql.user WHERE `User`='';

SELECT * FROM mysql.user WHERE `User`='';

总结

本文简单的阐述了MYSQL的用户管理和权限方面的内容，希望对大家有帮助

如果大家想更深入学习MYSQL访问控制方面的知识

可以参考这篇文章：MySQL权限的架构体系

核心就是两个图

1、客户端连接请求认证阶段

我的MYSQL学习心得 mysql的权限管理

2、客户端操作请求认证阶段

我的MYSQL学习心得 mysql的权限管理

最最后说一下，我开了一个MYSQL走起的微博，希望大家支持一下o(∩_∩)o

欢迎关注MYSQL官方微博：MYSQL走起

在我的博客的左边栏就会看到微博链接，大家也可以通过点击我的博客左边栏的微博链接进入MYSQL走起微博o(∩_∩)o

我的MYSQL学习心得 mysql的权限管理

相关文章