MySQL用户管理

MySQL用户管理教程

MySQL 是一个多用户的数据库,MySQL 的用户可以分为两大类:超级管理员权限(Root)、普通用户。

权限 概述
Root 具有创建用户、删除用户和修改普通用户、密码等管理权限。
普通用户 由 Root 创建,普通用户只拥有 Root 所分配的权限。普通用户 具有管理用户的账户、权限等。

MySQL用户管理权限表定义

说明

MySQL 服务器通过权限表来控制用户对数据库的访问,权限表存放在 mysql 数据库中,由 mysql_install_db 脚本初始化。

存储账号全新信息表的主要有:user、db、host、tables_priv、columns_priv 和 procs_priv。

user表

user 表是 MySQL 中最重要的一个权限表,记录允许连接到服务器的账号信息,里面的权限是全局级的。我们使用命令查看 user 表结构,首先使用 Mysql use 命令,切换到 mysql mysql 数据库。

mysql> use mysql;

此时终端显示如下:

01_mysql user表.png

我们使用 Mysql desc 命令,查看 user 表结构。

mysql> desc user;

此时终端显示如下:

02_mysql user表.png

user 表列名解析:

列名 概述
用户列 user 表的用户列包括 Host、User、Password,分别表示主机名、用户名和密码。
权限列 权限列的字段决定了用户的权限,描述了在全局范围内允许对数据和数据库进行的操作。
安全列 安全列只有6个字段,其中两个是 ssl 相关的,2个是 x509 相关的,另外两个是授权插件相关的。
资源控制列 资源控制列的字段用来限制用户使用的资源,包含4个字段,分别为:
1. max_questions:(用户每小时允许执行的查询操作次数)。
2. max_updates:(用户每小时允许执行的更新操作次数)。
3. max_connections:(用户每小时允许执行的连接操作次数)。
4. max_user_connections:(用户允许同时建立的连接次数)。

一个小时内用户查询或者连接数量超过资源控制限制,用户将被锁定,直到下一个小时,才可以执行在此执行对应的操作。可以使用 GRANT 语句更新这些字段的值。

db表

db 表是 MySQL 数据中非常重要的权限表。db 表中存储了用户对某个数据库的操作权限,决定用户能从哪个主机存取哪个数据库。

host表中存储了某个主机对数据库的操作权限,配合 db 权限对某个主机上数据库级操作权限做更细致的控制。这个权限表不受 GRANT 和 REVOKE 语句的影响。

用户列:db 表用户列有3个字段,分别是Host、User、Db,标识从某个主机连接某个用户对某个数据库的操作权限,这3个字段的组合构成了 db 表的主键。

权限列:db 表中 create_routine_priv 和 alter_routine_priv 这两个字段表明用户是否有创建和修改存储过程的权限。

我们使用命令查看 db 和 host 表结构,首先使用 Mysql use 命令,切换到 mysql mysql 数据库。

mysql> use mysql;

此时终端显示如下:

03_mysql db表.png

我们使用 Mysql desc 命令,查看 db 表结构。

mysql>desc db;

此时终端显示如下:

04_mysql db表.png

tables_priv表和 columns_priv表

tables_priv :用来对表设置操作权限。columns_priv :用来对表的某一列设置权限。

我们使用命令查看 tables_priv 和 columns_priv 表结构,首先使用 Mysql use 命令,切换到 mysql mysql 数据库。

mysql> use mysql;

05_mysql tables_priv columns_priv表.png

我们使用 Mysql desc 命令,查看 tables_priv 表结构。

mysql>desc tables_priv;

此时终端显示如下:

06_mysql tables_priv columns_priv表.png

我们使用 Mysql desc 命令,查看 columns_priv 表结构。

mysql>desc columns_priv;

此时终端显示如下:

07_mysql tables_priv columns_priv表.png

procs_priv表

procs_priv 表可以对存储过程和存储函数设置操作权限。我们使用命令查看 procs_priv 表结构,首先使用 Mysql use 命令,切换到 mysql mysql 数据库。

mysql> use mysql;

08_mysql procs_priv表.png

我们使用 Mysql desc 命令,查看 tables_priv 表结构。

mysql>desc procs_priv ;

此时终端显示如下:

09_mysql procs_priv表.png

MySQL用户账号管理

MySQL登录服务器

登录 MySQL 服务器,命令行输入命令,如下:

mysql -uroot -p1234546

命令解析:

命令 概述
-h 主机名,可以使用该参数指定主机名或 ip,如果不指定,默认是 localhost。
-u 用户名,可以使用该参数指定用户名。
-p 密码,可以使用该参数指定登录密码。注意:该参数后面的字符串和 -p 之间不能有空格。
-e 执行 sql 语句。如果指定了该参数,将在登录后执行 -e 后面的命令或 sql 语句并退出。

运行结果如下:

10_mysql登录服务器.png

MySQL退出服务器

退出 MySQL 服务器有三种方式:exit、quit、\q.。exit 运行结果如下:

11_mysql退出服务器.png

quit 运行结果如下:

12_mysql退出服务器.png

\q 运行结果如下:

13_mysql退出服务器.png

MySQL新建普通用户

执行 CREATE USER 或 GRANT 语句时,服务器会修改相应的用户授权表,添加或者修改用户及其权限。

使用 CREATE USER 语句的基本语法格式如下:

CREATE USER <用户名> [ IDENTIFIED ] BY [ PASSWORD ] <口令>

命令解析

  • <用户名>:指定创建用户账号,格式为 :‘haicoder’@‘localhost’。这里 haicoder是用户名,localhost为主机名,即用户连接 MySQL 时所在主机的名字。若在创建的过程中,只给出了账户的用户名,而没指定主机名,则主机名默认为 “%”,表示一组主机。

  • PASSWORD:可选项,用于指定散列口令,即若使用明文设置口令,则需忽略 PASSWORD 关键字。若不想以明文设置口令,且知道 PASSWORD() 函数返回给密码的散列值,则可以在口令设置语句中指定此散列值,但需要加上关键字 PASSWORD。

  • IDENTIFIED BY 子句:用于指定用户账号对应的口令,若该用户账号无口令,则可省略此子句。

MySQL删除普通用户

使用 DROP USER 语句删除用户,语法如下:

DROP USER user[,user];

命令解析:

  • DROP USER 语句用于删除一个或多个 MySQL 账户。要使用DROP USER,必须拥有 MySQL 数据库的全局 CREATE USER 权限或 DELETE 权限。

注:FROP USER 不能自动关闭任何打开的用户对话,而且,如果用户又打开的对话,此时取消用户,命令则不会生效,知道用户对话被关闭后才能生效。一旦对话被关闭,用户也被取消,此用户再次视图登录时将会失败。

使用 DELETE 语句删除用户,语法如下:

DELETE FROM MySQL.user WHERE host='hostname' and user='username'

MySQL用户管理总结

MySQL 是一个多用户的数据库,MySQL 的用户可以分为两大类:超级管理员权限(Root)、普通用户。

用户管理常用命令

命令 作用
mysql -h127.0.0.1 -u root -p1234546 MySQL 登录服务器
exit、quit、\q. MySQL 退出服务器
CREATE USER <用户名> [ IDENTIFIED ] BY [ PASSWORD ] <口令> MySQL 新建普通用户
DROP USER user[,user]; MySQL 删除普通用户