用户

create user 官网教程

语法

  1. CREATE USER [IF NOT EXISTS]
  2.     user [auth_option] [, user [auth_option]] ...
  3.     [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
  4.     [WITH resource_option [resource_option] ...]
  5.     [password_option | lock_option] ...
  7. user:
  8.     (see Section 6.2.4, “Specifying Account Names”)
  10. auth_option: {
  11.     IDENTIFIED BY 'auth_string'
  12.   | IDENTIFIED WITH auth_plugin
  13.   | IDENTIFIED WITH auth_plugin BY 'auth_string'
  14.   | IDENTIFIED WITH auth_plugin AS 'auth_string'
  15.   | IDENTIFIED BY PASSWORD 'auth_string'
  16. }
  18. tls_option: {
  19.    SSL
  20.  | X509
  21.  | CIPHER 'cipher'
  22.  | ISSUER 'issuer'
  23.  | SUBJECT 'subject'
  24. }
  26. resource_option: {
  27.     MAX_QUERIES_PER_HOUR count
  28.   | MAX_UPDATES_PER_HOUR count
  29.   | MAX_CONNECTIONS_PER_HOUR count
  30.   | MAX_USER_CONNECTIONS count
  31. }
  33. password_option: {
  34.     PASSWORD EXPIRE
  35.   | PASSWORD EXPIRE DEFAULT
  36.   | PASSWORD EXPIRE NEVER
  37.   | PASSWORD EXPIRE INTERVAL N DAY
  38. }
  40. lock_option: {
  41.     ACCOUNT LOCK
  42.   | ACCOUNT UNLOCK
  43. }

首次建立用户，默认权限 USAGE = 没有权限

首次创建用户，用户是没有权限的。使用新用户链接并去查看，新用户只可以连到数据库，但是没有权限。 show grants 语句只显示具有 USAGE 权限（USAGE 就是没有权限）；当然也可查看 select * from mysql.user 表。

  1. -- 创建用户，不指定 host，则默认为 %，任意可访问
  2. create user if not exists xxh identified by 'xiaohui';
  3. -- 授权，可以指定列权限
  4. -- grant select (id,name) on db_1.test_data to xiaohuihui with grant option;
  5. -- 查看所有的用户，重点关注的是 host、user 两个字段
  6. select * from mysql.user;
  7. -- 刷新权限，不知道是不是我的 mysql 8，还是为什么，不需要 flush ，用户已具备权限
  8. flush privileges;
  9. -- 显示当前用户的权限
  10. show grants;

授权给不存在的用户，会报错

  1. grant select on db_1.* to xh@localhost
  2. -- > 1410 - You are not allowed to create a user with GRANT

不同的 host 是不同的用户

  1. -- grant select on db_1.* to xh@localhost;
  2. select * from mysql.user;
  3. -- create user xh@'192.168.1.59' identified by 'xiaohui';
  4. create user if not exists xh@'192.168.0.12' identified by 'xiaohui';

结果

Host User Select_priv
% xiaohui N
% xiaohuihui N
% xxh N
192.168.0.12 xh N
192.168.1.59 xh N
localhost mysql.infoschema Y
localhost mysql.session N
localhost mysql.sys N
localhost root Y
localhost xiaohui N

删除用户

  1. drop user xh@'192.168.0.12';

然后再次访问，就会报错 1045 - Access denied for user ‘xh’@’192.168.0.12’(using password: YES)

匿名用户

  1. -- 本机的匿名用户
  2. create user if not exists ''@localhost identified by '123456';
  3. grant select on db_1.test_data to ''@'localhost';
  5. -- 192.168.0.12 的匿名用户
  6. create user if not exists ''@'192.168.0.12' identified by 'xiaohui';
  7. grant select on db_1.test_data to ''@'192.168.0.12';
  9. -- 列出了所有匿名用户：
  10. SELECT Host, User FROM mysql.user WHERE User='';
  12. -- 删除本地匿名用户帐户：
  13. DROP USER ''@'localhost';

同一个 Host 同时指定用户和匿名用户时，可以乱输用户名。
只要存在匿名账户，就可以随意输入用户名，只要密码匹配，便可以进行账户对应权限的操作。

修改用户密码

  1. alter user ''@'localhost' identified by 'xiaohui'
  3. -- 或者SET PASSWORD 语句
  5. SET PASSWORD [FOR user] = password_option
  7. password_option: {
  8.     'auth_string'
  9.   | PASSWORD('auth_string')
  10. }
  12. -- 注意
  13. SET PASSWORD ... = PASSWORD('auth_string') 语法在 MySQL 5.7 中已弃用，并在 MySQL 8.0 中删除。
  15. SET PASSWORD ... = 'auth_string' 语法并未被弃用，但它ALTER USER是更改帐户（包括分配密码）的首选语句。例如：
  17. ALTER USER user IDENTIFIED BY 'auth_string';

重名用户

  1. RENAME USER old_user TO new_user
  2.     [, old_user TO new_user] ...
  5. rename user xh@'%' to 'xxhh'@'%';

权限

grant mysql 官网

特权 意义和可授予级别
ALL [PRIVILEGES] 授予指定访问级别的所有权限，除了 GRANT OPTION
和 PROXY
。
ALTER 启用使用ALTER TABLE
. 级别：全局、数据库、表。
ALTER ROUTINE 允许更改或删除存储的例程。级别：全局、数据库、例程。
CREATE 启用数据库和表创建。级别：全局、数据库、表。
CREATE ROUTINE 启用存储例程创建。级别：全局、数据库。
CREATE TABLESPACE 允许创建、更改或删除表空间和日志文件组。级别：全球。
CREATE TEMPORARY TABLES 启用使用CREATE TEMPORARY TABLE
. 级别：全局、数据库。
CREATE USER 允许使用CREATE USER
， DROP USER
， RENAME USER
，和 REVOKE ALL PRIVILEGES
。级别：全球。
CREATE VIEW 允许创建或更改视图。级别：全局、数据库、表。
DELETE 启用使用DELETE
. 级别：全局、数据库、表。
DROP 启用要删除的数据库、表和视图。级别：全局、数据库、表。
EVENT 启用事件调度程序的事件使用。级别：全局、数据库。
EXECUTE 使用户能够执行存储的例程。级别：全局、数据库、例程。
FILE 使用户能够使服务器读取或写入文件。级别：全球。
GRANT OPTION 启用授予其他帐户或从其他帐户中删除的权限。级别：全局、数据库、表、例程、代理。
INDEX 允许创建或删除索引。级别：全局、数据库、表。
INSERT 启用使用INSERT
. 级别：全局、数据库、表、列。
LOCK TABLES 启用LOCK TABLES
对您具有SELECT
权限的表的使用。级别：全局、数据库。
PROCESS 使用户能够查看带有SHOW PROCESSLIST
. 级别：全球。
PROXY 启用用户代理。级别：从用户到用户。
REFERENCES 启用外键创建。级别：全局、数据库、表、列。
RELOAD 启用FLUSH
操作。级别：全球。
REPLICATION CLIENT 允许用户询问源服务器或副本服务器在哪里。级别：全球。
REPLICATION SLAVE 启用副本从源读取二进制日志事件。级别：全球。
SELECT 启用使用SELECT
. 级别：全局、数据库、表、列。
SHOW DATABASES 启用SHOW DATABASES
以显示所有数据库。级别：全球。
SHOW VIEW 启用使用SHOW CREATE VIEW
. 级别：全局、数据库、表。
SHUTDOWN 启用mysqladmin shutdown 的
使用。级别：全球。
SUPER 能够使用如其他行政业务 CHANGE MASTER TO
， KILL
， PURGE BINARY LOGS
， SET GLOBAL
，和中mysqladmin调试
命令。级别：全球。
TRIGGER 启用触发操作。级别：全局、数据库、表。
UPDATE 启用使用UPDATE
. 级别：全局、数据库、表、列。
USAGE “无特权”的同义词

grant 授权，必须授予存在的用户（完整的用户是 user@xxx，就是 user表中 User@Host 组成的）

  1. show create table mysql.user;
  2. -- 结果
  3. CREATE TABLE `user` (
  4.   `Host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
  5.   `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  6.   `Select_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  7.   `Insert_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  8.   `Update_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  9.   `Delete_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  10.   `Create_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  11.   `Drop_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  12.   `Reload_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  13.   `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  14.   `Process_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  15.   `File_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  16.   `Grant_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  17.   `References_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  18.   `Index_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  19.   `Alter_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  20.   `Show_db_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  21.   `Super_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  22.   `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  23.   `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  24.   `Execute_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  25.   `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  26.   `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  27.   `Create_view_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  28.   `Show_view_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  29.   `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  30.   `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  31.   `Create_user_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  32.   `Event_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  33.   `Trigger_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  34.   `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  35.   `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  36.   `ssl_cipher` blob NOT NULL,
  37.   `x509_issuer` blob NOT NULL,
  38.   `x509_subject` blob NOT NULL,
  39.   `max_questions` int unsigned NOT NULL DEFAULT '0',
  40.   `max_updates` int unsigned NOT NULL DEFAULT '0',
  41.   `max_connections` int unsigned NOT NULL DEFAULT '0',
  42.   `max_user_connections` int unsigned NOT NULL DEFAULT '0',
  43.   `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'caching_sha2_password',
  44.   `authentication_string` text COLLATE utf8_bin,
  45.   `password_expired` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  46.   `password_last_changed` timestamp NULL DEFAULT NULL,
  47.   `password_lifetime` smallint unsigned DEFAULT NULL,
  48.   `account_locked` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  49.   `Create_role_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  50.   `Drop_role_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  51.   `Password_reuse_history` smallint unsigned DEFAULT NULL,
  52.   `Password_reuse_time` smallint unsigned DEFAULT NULL,
  53.   `Password_require_current` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  54.   `User_attributes` json DEFAULT NULL,
  55.   PRIMARY KEY (`Host`,`User`)
  56. ) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='Users and global privileges'
  1. 从 mysql.user 表的主键就可以看出 user + host 才构成一个真是的账号。所有 grant 授权需要指定（Host 为 % 的除外）。

授权例子

  1. -- 全局权限是管理权限或适用于给定服务器上的所有数据库。要分配全局权限，请使用 ON *.*语法：
  2. GRANT ALL ON *.* TO 'someuser'@'somehost';
  3. GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';

问题

  1. Host 是指需要连接的机器的 IP，可是多层网络或路由后，可能多台设备出去的 IP 一样，mysql 判断用户的 Host 是根据的什么呢？

关于上面问题，可以先看看 mysql 的访问控制