文章目录
【 1. 权限表 】 1.1 user 权限表 1.1.1 用户列 1.1.2 权限列 1.1.3 安全列 1.1.4 资源控制列 1.2 db 表
1.3 tables_priv 表 1.4 columns_priv 表 1.5 procs_priv表 【 2. 用户管理 】 2.1 创建用户 CREATE USER 2.2 用户的登陆、退出
2.3 重命名用户 RENAME USER 2.4 删除用户 DROP/DELETE USER DROP USER 删除普通用户 DELETE 删除普通用户 2.5 查看用户权限 2.6 用户授权 GRANT 2.6.1 数据库的权限类型 2.6.2 表的权限类型 2.6.3 列的权限类型 2.6.4 用户的权限类型 2.6 删除用户权限 REVOKE
2.8 修改用户密码
问题背景 MySQL 在安装时会自动创建一个名为 mysql 的数据库，mysql 数据库中存储的都是 用户权限表 MySQL 会根据这些权限表的内容为每个用户赋予相应的权限 user 表 记录允许连接到服务器的账号信息 在 user 表里启用的所有权限都是全局级的，适用于所有数据库 显示 user 权限表 DESC mysql. user ;
用户列 用户连接 MySQL 数据库时需要输入的信息 user 表的用户列 字段名 说明 字段类型 是否为空 默认值 Host 主机名 char(60) NO 无 User 用户名 char(32) NO 无 authentication_string 密码 text YES 无
权限列 决定了用户的权限 权限大致分为两大类，分别是高级管理权限和普通权限：
高级管理权限 普通权限 user表的权限列 字段名 字段类型 是否为空 默认值 说明 Select_priv enum(‘N’,‘Y’) NO N 是否可以通过SELECT 命令查询数据 Insert_priv enum(‘N’,‘Y’) NO N 是否可以通过 INSERT 命令插入数据 Update_priv enum(‘N’,‘Y’) NO N 是否可以通过UPDATE 命令修改现有数据 Delete_priv enum(‘N’,‘Y’) NO N 是否可以通过DELETE 命令删除现有数据 Create_priv enum(‘N’,‘Y’) NO N 是否可以创建新的数据库和表 Drop_priv enum(‘N’,‘Y’) NO N 是否可以删除现有数据库和表 Reload_priv enum(‘N’,‘Y’) NO N 是否可以执行刷新和重新加载MySQL所用的各种内部缓存的特定命令，包括日志、权限、主机、查询和表 Shutdown_priv enum(‘N’,‘Y’) NO N 是否可以关闭MySQL服务器。将此权限提供给root账户之外的任何用户时，都应当非常谨慎 Process_priv enum(‘N’,‘Y’) NO N 是否可以通过SHOW PROCESSLIST命令查看其他用户的进程 File_priv enum(‘N’,‘Y’) NO N 是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令 Grant_priv enum(‘N’,‘Y’) NO N 是否可以将自己的权限再授予其他用户 References_priv enum(‘N’,‘Y’) NO N 是否可以创建外键约束 Index_priv enum(‘N’,‘Y’) NO N 是否可以对索引进行增删查 Alter_priv enum(‘N’,‘Y’) NO N 是否可以重命名和修改表结构 Show_db_priv enum(‘N’,‘Y’) NO N 是否可以查看服务器上所有数据库的名字，包括用户拥有足够访问权限的数据库 Super_priv enum(‘N’,‘Y’) NO N 是否可以执行某些强大的管理功能，例如通过KILL命令删除用户进程；使用SET GLOBAL命令修改全局MySQL变量，执行关于复制和日志的各种命令。（超级权限） Create_tmp_table_priv enum(‘N’,‘Y’) NO N 是否可以创建临时表 Lock_tables_priv enum(‘N’,‘Y’) NO N 是否可以使用LOCK TABLES命令阻止对表的访问/修改 Execute_priv enum(‘N’,‘Y’) NO N 是否可以执行存储过程 Repl_slave_priv enum(‘N’,‘Y’) NO N 是否可以读取用于维护复制数据库环境的二进制日志文件 Repl_client_priv enum(‘N’,‘Y’) NO N 是否可以确定复制从服务器和主服务器的位置 Create_view_priv enum(‘N’,‘Y’) NO N 是否可以创建视图 Show_view_priv enum(‘N’,‘Y’) NO N 是否可以查看视图 Create_routine_priv enum(‘N’,‘Y’) NO N 是否可以更改或放弃存储过程和函数 Alter_routine_priv enum(‘N’,‘Y’) NO N 是否可以修改或删除存储函数及函数 Create_user_priv enum(‘N’,‘Y’) NO N 是否可以执行CREATE USER命令，这个命令用于创建新的MySQL账户 Event_priv enum(‘N’,‘Y’) NO N 是否可以创建、修改和删除事件 Trigger_priv enum(‘N’,‘Y’) NO N 是否可以创建和删除触发器 Create_tablespace_priv enum(‘N’,‘Y’) NO N 是否可以创建表空间
字段名 字段类型 是否为空 默认值 说明 ssl_type enum(‘’,‘ANY’,‘X509’,‘SPECIFIED’) NO 支持ssl标准加密安全字段 ssl_cipher blob NO 支持ssl标准加密安全字段 x509_issuer blob NO 支持x509标准字段 x509_subject blob NO 支持x509标准字段 plugin char(64) NO mysql_native_password 引入plugins以进行用户连接时的密码验证，plugin创建外部/代理用户 password_expired enum(‘N’,‘Y’) NO N 密码是否过期 (N 未过期，y 已过期) password_last_changed timestamp YES 记录密码最近修改的时间 password_lifetime smallint(5) unsigned YES 设置密码的有效时间，单位为天数 account_locked enum(‘N’,‘Y’) NO N 用户是否被锁定（Y 锁定，N 未锁定）
资源控制列 限制用户使用的资源 user 表中的资源控制列。
以下字段的默认值为 0，表示没有限制。 一个小时内用户查询或者连接数量超过资源控制限制，用户将被锁定 可以使用 GRANT 语句更新这些字段的值。 字段名 字段类型 是否为空 默认值 说明 max_questions int(11) unsigned NO 0 规定每小时允许执行查询的操作次数 max_updates int(11) unsigned NO 0 规定每小时允许执行更新的操作次数 max_connections int(11) unsigned NO 0 规定每小时允许执行的连接操作次数 max_user_connections int(11) unsigned NO 0 规定允许同时建立的连接次数
db 表 存储了用户对某个数据库的操作权限 db 表用户列有 3 个字段，分别是 Host、User、Db，标识 从某个主机连接某个用户对某个数据库的操作权限 db 用户列 字段名 字段类型 是否为空 默认值 说明 Host char(60) NO 无 主机名 Db char(64) NO 无 数据库名 User char(32) NO 无 用户名
db 表中的权限列和 user 表中的权限列大致相同，只是user 表中的权限是针对所有数据库的，而 db 表中的权限只针对指定的数据库，如果希望用户只对某个数据库有操作权限，可以先将 user 表中对应的权限设置为 N，然后在 db 表中设置对应数据库的操作权限 tables_priv 表 对单个表进行权限设置 tables_priv 表 字段名 字段类型 是否为空 默认值 说明 Host char(60) NO 无 主机 Db char(64) NO 无 数据库名 User char(32) NO 无 用户名 Table_name char(64) NO 无 表名 Grantor char(93) NO 无 修改该记录的用户 Timestamp timestamp NO CURRENT_TIMESTAMP 修改该记录的时间 Table_priv set(‘Select’,‘Insert’,‘Update’,‘Delete’,‘Create’,‘Drop’,‘Grant’,‘References’,‘Index’,‘Alter’,‘Create View’,‘Show view’,‘Trigger’) NO 无 表示对表的操作权限，包括 Select、Insert、Update、Delete、Create、Drop、Grant、References、Index 和 Alter 等 Column_priv set(‘Select’,‘Insert’,‘Update’,‘References’) NO 无 表示对表中的列的操作权限，包括 Select、Insert、Update 和 References
columns_priv 表 对单个数据列进行权限设置 tables_priv 表 字段名 字段类型 是否为空 默认值 说明 Host char(60) NO 无 主机 Db char(64) NO 无 数据库名 User char(32) NO 无 用户名 Table_name char(64) NO 无 表名 Column_name char(64) NO 无 数据列名称，用来指定对哪些数据列具有操作权限 Timestamp timestamp NO CURRENT_TIMESTAMP 修改该记录的时间 Column_priv set(‘Select’,‘Insert’,‘Update’,‘References’) NO 无 表示对表中的列的操作权限，包括 Select、Insert、Update 和 References
procs_priv 表 对存储过程和存储函数进行权限设置 procs_priv 表 字段名 字段类型 是否为空 默认值 说明 Host char(60) NO 无 主机名 Db char(64) NO 无 数据库名 User char(32) NO 无 用户名 Routine_name char(64) NO 无 表示存储过程或函数的名称 Routine_type enum(‘FUNCTION’,‘PROCEDURE’) NO 无 表示存储过程或函数的类型，Routine_type 字段有两个值，分别是 FUNCTION 和 PROCEDURE。FUNCTION 表示这是一个函数；PROCEDURE 表示这是一个存储过程。 Grantor char(93) NO 无 插入或修改该记录的用户 Proc_priv set(‘Execute’,‘Alter Routine’,‘Grant’) NO 无 表示拥有的权限，包括 Execute、Alter Routine、Grant 3种 Timestamp timestamp NO CURRENT_TIMESTAMP 表示记录更新时间
问题背景root 拥有超级权限，可以控制整个 MySQL 服务器 使用 CREATE USER 语句来创建 MySQL 用户，并设置相应的密码。用户 不指定主机名则默认对所有主机开放权限。 IDENTIFIED BY ‘password’ ‘password’ 表示用户登录时使用的密码，需要用单引号括起来。 使用 CREATE USER 语句 必须拥有 mysql 数据库的 INSERT 权限或全局 CREATE USER 权限 使用 CREATE USER 语句创建一个用户后，MySQL 会在 mysql 数据库的 user 表中添加一条新记录。 CREATE USER 语句可以同时创建多个用户，多个用户用逗号隔开。 CREATE USER < 用户>
[ IDENTIFIED BY 'password' ]
[ , 用户 [ IDENTIFIED BY 'password' ] ]
新创建的用户拥有的权限很少，它们只能执行不需要权限的操作。如登录 MySQL、使用 SHOW 语句查询所有存储引擎和字符集的列表等。 如果两个用户的用户名相同，但主机名不同，MySQL 会将它们视为两个用户，并允许为这两个用户分配不同的权限集合。 实例CREATE USER 'test1'@'localhost' IDENTIFIED BY 'test1'; 启动 MySQL 服务后，可以使用以下命令来登录。
-h：指定连接 MySQL 服务器的地址 。可以用 hostname 或 hostIP 表示，
hostname 为主机名，hostIP 为主机 IP 地址。 -p：指定连接 MySQL 服务器的端口号 。
port 为连接的端口号 。MySQL 的默认端口号是 3306，因此如果不指定该参数，默认使用 3306 连接 MySQL 服务器。 u：指定连接 MySQL 服务器的用户名 ，
-p：提示输入密码，即提示 Enter password。也可以直接在 mysql 命令的 -p 后加上登录密码，登录密码与 -p 之间没有空格 DatabaseName ：指定连接到 MySQL 服务器后，登录到哪一个数据库中。如果没有指定，默认为 mysql 数据库。-e：指定需要执行的 SQL 语句，登录 MySQL 服务器后执行这个 SQL 语句，然后退出 MySQL 服务器。 mysql - h hostname| hostlP [ - p port] - u username - p DatabaseName - e "SQL语句"
CMD 中 登陆 root 用户：mysql -u root -p 实例mysql -h localhost -u root -p test 实例2mysql -h localhost -u root -p mytest -e"DESC tb_tudent" 退出 MySQL 服务器的方式很简单，只要在命令行输入 EXIT 或 QUIT 即可。“\q”是 QUIT 的缩写，也可以用来退出 MySQL 服务器。 QUIT;
\q;
基本语法
<旧用户 >：系统中已经存在的 MySQL 用户账号。 <新用户 >：新的 MySQL 用户账号。 使用 RENAME USER 语句，必须拥有 mysql 数据库的 UPDATE 权限或全局 CREATE USER 权限 若系统中旧账户不存在或者新账户已存在，该语句执行时会出现错误。 RENAME USER < 旧用户> TO < 新用户>
实例
使用 RENAME USER 语句将用户名 test1 修改为 testUser1，主机是 localhost。RENAME USER 'test1'@'localhost' TO 'testUser1'@'localhost'; 在 cmd 命令行工具中，使用 testUser1 用户登录数据库服务器。mysql -h localhost -u testUser1 -p 用户的删除不会影响他们之前所创建的表、索引或其他数据库对象，因为 MySQL 并不会记录是谁创建了这些对象。用户 用来指定需要删除的用户账号。DROP USER 语句可用于删除一个或多个用户，并撤销其权限。 使用 DROP USER 语句 必须拥有 mysql 数据库的 DELETE 权限或全局 CREATE USER 权限 在 DROP USER 语句的使用中，若没有明确地给出账户的主机名，则该主机名默认为“%”。 DROP USER < 用户1 > [ , < 用户2 > ] …
实例1
使用 DROP USER 语句删除用户’testUser1@‘localhost’。DROP USER 'testUser1'@'localhost'; 在 cmd 命令行工具中，使用 test1 用户登录数据库服务器，发现登录失败，说明用户已经删除。mysql -h localhost -u test1 -p 基本语法
可以使用 DELETE 语句直接删除 mysql.user 表中相应的用户信息，但必须拥有 mysql.user 表的 DELETE 权限 Host 和 User 这两个字段都是 mysql.user 表的主键。因此，需要两个字段的值才能确定一条记录。 DELETE FROM mysql. user WHERE Host= 'hostname' AND User = 'username' ;
mysql 数据库下的 user 表中存储着用户的基本权限，而新创建的用户只有登录 MySQL 服务器的权限，没有任何其它权限，不能查询 user 表。 使用 SELECT 语句查询用户的权限。要执行该语句，必须拥有对 user 表的查询权 SELECT * FROM mysql. user ;
也可以使用 SHOW GRANTS 语句查询用户的权限。username 表示用户名。hostname 表示主机名或主机IP。 SHOW GRANTS FOR 'username' @'hostname' ;
实例1CREATE USER 'testuser1'@'localhost';USAGE ON *.*表示该用户对任何数据库和任何表都没有权限 SHOW GRANTS FOR 'testuser1'@'localhost'; 实例2：SHOW GRANTS FOR 'root'@'localhost'; 授权 例如，可以为新建的用户赋予查询所有数据库和表的权限。 为了安全起见，最好不要授予普通用户 SUPER 权限、GRANT 权限 基本语法
priv_type 参数表示权限类型；columns_list 参数表示权限作用于哪些列上，省略该参数时，表示作用于整个表；database.table 用于指定权限的级别，值有：
*：表示当前数据库中的所有表。 . ：表示所有数据库中的所有表。db_name.*：表示某个数据库中的所有表，db_name 指定数据库名。 db_name.tbl_name：表示某个数据库中的某个表或视图， - db_name 指定数据库名，tbl_name 指定表名或视图名。 db_name.routine_name：表示某个数据库中的某个存储过程或函数，routine_name 指定存储过程名或函数名。 TO 子句：如果权限被授予给一个不存在的用户，MySQL 会自动执行一条 CREATE USER 语句来创建这个用户，但同时必须为该用户设置密码。 user 参数表示用户账户，由用户名和主机名构成，格式是“‘username’@‘hostname’”；IDENTIFIED BY 参数用来为用户设置密码； password 参数是用户的新密码。WITH 关键字后面带有一个或多个 with_option 参数。这个参数有 5 个选项，详细介绍如下：
GRANT OPTION：被授权的用户可以将这些权限赋予给别的用户； MAX_QUERIES_PER_HOUR count：设置每个小时可以允许执行 count 次查询； MAX_UPDATES_PER_HOUR count：设置每个小时可以允许执行 count 次更新； MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立 count 个连接; MAX_USER_CONNECTIONS count：设置单个用户可以同时具有的 count 个连接。 GRANT priv_type [ ( column_list) ] ON database . table
TO user
[ , user ] . . .
[ WITH with_option [ with_option] . . . ]
MySQL 中可以授予的权限 描述 举例 列权限 和表中的一个具体列相关。 例如，可以使用 UPDATE 语句更新表 students 中 name 列的值的权限。 表权限 和一个具体表中的所有数据相关。 例如，可以使用 SELECT 语句查询表 students 的所有数据的权限。 数据库权限 和一个具体的数据库中的所有表相关。 例如，可以在已有的数据库 mytest 中创建新表的权限。 用户权限 和 MySQL 中所有的数据库相关。 例如，可以删除已有的数据库或者创建一个新的数据库的权限。
权限名称 对应user表中的字段 说明 SELECT Select_priv 表示授予用户可以使用 SELECT 语句访问特定数据库中所有表和视图的权限。 INSERT Insert_priv 表示授予用户可以使用 INSERT 语句向特定数据库中所有表添加数据行的权限。 DELETE Delete_priv 表示授予用户可以使用 DELETE 语句删除特定数据库中所有表的数据行的权限。 UPDATE Update_priv 表示授予用户可以使用 UPDATE 语句更新特定数据库中所有数据表的值的权限。 REFERENCES References_priv 表示授予用户可以创建指向特定的数据库中的表外键的权限。 CREATE Create_priv 表示授权用户可以使用 CREATE TABLE 语句在特定数据库中创建新表的权限。 ALTER Alter_priv 表示授予用户可以使用 ALTER TABLE 语句修改特定数据库中所有数据表的权限。 SHOW VIEW Show_view_priv 表示授予用户可以查看特定数据库中已有视图的视图定义的权限。 CREATE ROUTINE Create_routine_priv 表示授予用户可以为特定的数据库创建存储过程和存储函数的权限。 ALTER ROUTINE Alter_routine_priv 表示授予用户可以更新和删除数据库中已有的存储过程和存储函数的权限。 INDEX Index_priv 表示授予用户可以在特定数据库中的所有数据表上定义和删除索引的权限。 DROP Drop_priv 表示授予用户可以删除特定数据库中所有表和视图的权限。 CREATE TEMPORARY TABLES Create_tmp_table_priv 表示授予用户可以在特定数据库中创建临时表的权限。 CREATE VIEW Create_view_priv 表示授予用户可以在特定数据库中创建新的视图的权限。 EXECUTE ROUTINE Execute_priv 表示授予用户可以调用特定数据库的存储过程和存储函数的权限。 LOCK TABLES Lock_tables_priv 表示授予用户可以锁定特定数据库的已有数据表的权限。 ALL 或 ALL PRIVILEGES 或 SUPER Super_priv 表示以上所有权限/超级权限
权限名称 对应user表中的字段 说明 SELECT Select_priv 授予用户可以使用 SELECT 语句进行访问特定表的权限 INSERT Insert_priv 授予用户可以使用 INSERT 语句向一个特定表中添加数据行的权限 DELETE Delete_priv 授予用户可以使用 DELETE 语句从一个特定表中删除数据行的权限 DROP Drop_priv 授予用户可以删除数据表的权限 UPDATE Update_priv 授予用户可以使用 UPDATE 语句更新特定数据表的权限 ALTER Alter_priv 授予用户可以使用 ALTER TABLE 语句修改数据表的权限 REFERENCES References_priv 授予用户可以创建一个外键来参照特定数据表的权限 CREATE Create_priv 授予用户可以使用特定的名字创建一个数据表的权限 INDEX Index_priv 授予用户可以在表上定义索引的权限 ALL 或 ALL PRIVILEGES 或 SUPER Super_priv 所有的权限名
授予列权限时，<权限类型>的值只能指定为 SELECT、INSERT 和 UPDATE 最有效率的权限是用户权限。授予用户权限时，<权限类型>除了可以指定为授予数据库权限时的所有值之外，还可以是下面这些值：
CREATE USER：表示授予用户可以创建和删除新用户的权限。 SHOW DATABASES：表示授予用户可以使用 SHOW DATABASES 语句查看所有已有的数据库的定义的权限。 实例
用户 testuser1 对所有的数据有查询、插入权限，并授予 GRANT 权限。GRANT SELECT,INSERT ON *.* TO 'testuser1'@'localhost' WITH GRANT OPTION; 查询用户 testuser1 的权限。SHOW GRANTS FOR 'testuser1'@'localhost'; 问题背景删除某个用户的某些权限 一定程度上可以保证系统的安全性 例如，如果数据库管理员觉得某个用户不应该拥有 DELETE 权限，那么就可以删除 DELETE 权限。 要使用 REVOKE 语句，必须拥有 MySQL 数据库的全局 CREATE USER 权限或 UPDATE 权限 基本语法
priv_type 参数表示权限的类型；column_list 参数可选，表示权限作用于哪些列上，没有该参数时作用于整个表上；user 参数由用户名和主机名构成，格式为“username’@‘hostname’”。 REVOKE priv_type [ ( column_list) ] . . .
ON database . table
FROM user [ , user ] . . .
实例1
使用 REVOKE 语句取消用户 testuser1 的插入权限REVOKE INSERT ON *.* FROM 'testuser1 '@'localhost'; 查看 testuser1 的权限SHOW GRANTS FOR 'testuser1 '@'localhost'; REVOKE ALL PRIVILEGES , GRANT OPTION FROM user [ , user ] . . .
在 MySQL 中，只有 root 用户可以通过更新 MySQL 数据库来更改密码。使用 root 用户登录到 MySQL 服务器后，可以修改普通用户密码。username 参数是普通用户的用户名，hostname 参数是普通用户的主机名，newpwd 是要更改的新密码。 ALTER USER 'username' @'localhost' IDENTIFIED BY 'newpwd' ;
SET PASSWORD FOR 'username' @'localhost' = 'newpwd' ;
实例
创建一个没有密码的 testuser 用户CREATE USER 'testuser'@'localhost'; root 用户登录 MySQL 服务器后，再使用 SET 语句将 testuser 用户的密码修改为“newpwd”，ALTER USER 'testuser'@'localhost' IDENTIFIED BY 'newpwd'; 退出 MySQL 服务器，使用 testuser 用户登录，输入密码“newpwd”mysql -utestuser -p mysqladmin 命令可在Windows 命令行窗口（cmd）中修改root密码。usermame 指需要修改密码的用户名称，在这里指定为 root 用户；hostname 指需要修改密码的用户主机名，该参数可以不写，默认是 localhost；password 为关键字，而不是指旧密码；newpwd 为新设置的密码，必须用双引号括起来。如果使用单引号会引发错误，可能会造成修改后的密码不是你想要的。 mysqladmin - u username - h hostname - p password "newpwd"
SET PASSWORD 命令可以用来重新设置其他用户的登录密码或者自己使用的账户的密码。
SET PASSWORD = "NEWPWD" ;
实例1：mysqladmin 命令修改用户密码。
使用 mysqladmin 将 root 用户的密码修改为“rootpwd”mysqladmin -u root -p password "rootpwd" 用修改后的“rootpwd”密码登录 root 用户。mysql -u root -p 实例2：SET PASSWORD命令修改用户密码。SET PASSWORD = "rootpwd3";
