1.用户的组成
1 查看创建用户命令 2 mysql> help create user; 3 Name: 'CREATE USER' 4 Description: 5 Syntax: 6 CREATE USER [IF NOT EXISTS] 7 user [auth_option] [, user [auth_option]] ... 8 DEFAULT ROLE role [, role ] ... 9 [REQUIRE {NONE | tls_option [[AND] tls_option] ...}] 10 [WITH resource_option [resource_option] ...] 11 [password_option | lock_option] ... 12 [COMMENT 'comment_string' | ATTRIBUTE 'json_object'] 13 14 user: 15 (see ) 16 17 auth_option: { 18 IDENTIFIED BY 'auth_string' 19 | IDENTIFIED BY RANDOM PASSWORD 20 | IDENTIFIED WITH auth_plugin 21 | IDENTIFIED WITH auth_plugin BY 'auth_string' 22 | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD 23 | IDENTIFIED WITH auth_plugin AS 'auth_string' 24 } 25 26 tls_option: { 27 SSL 28 | X509 29 | CIPHER 'cipher' 30 | ISSUER 'issuer' 31 | SUBJECT 'subject' 32 } 33 34 resource_option: { 35 MAX_QUERIES_PER_HOUR count 36 | MAX_UPDATES_PER_HOUR count 37 | MAX_CONNECTIONS_PER_HOUR count 38 | MAX_USER_CONNECTIONS count 39 } 40 41 password_option: { 42 PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY] 43 | PASSWORD HISTORY {DEFAULT | N} 44 | PASSWORD REUSE INTERVAL {DEFAULT | N DAY} 45 | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL] 46 | FAILED_LOGIN_ATTEMPTS N 47 | PASSWORD_LOCK_TIME {N | UNBOUNDED} 48 } 49 50 lock_option: { 51 ACCOUNT LOCK 52 | ACCOUNT UNLOCK 53 } 54 55 The CREATE USER statement creates new MySQL accounts. It enables 56 authentication, role, SSL/TLS, resource-limit, and password-management 57 properties to be established for new accounts. It also controls whether 58 accounts are initially locked or unlocked. 59 60 To use CREATE USER, you must have the global CREATE USER privilege, or 61 the INSERT privilege for the mysql system schema. When the read_only 62 system variable is enabled, CREATE USER additionally requires the 63 CONNECTION_ADMIN privilege (or the deprecated SUPER privilege). 64 65 As of MySQL 8.0.22, CREATE USER fails with an error if any account to 66 be created is named as the DEFINER attribute for any stored object. 67 (That is, the statement fails if creating an account would cause the 68 account to adopt a currently orphaned stored object.) To perform the 69 operation anyway, you must have the SET_USER_ID privilege; in this 70 case, the statement succeeds with a warning rather than failing with an 71 error. Without SET_USER_ID, to perform the user-creation operation, 72 drop the orphan objects, create the account and grant its privileges, 73 and then re-create the dropped objects. For additional information, 74 including how to identify which objects name a given account as the 75 DEFINER attribute, see 76 https://dev.mysql.com/doc/refman/8.0/en/stored-objects-security.html#st 77 ored-objects-security-orphan-objects. 78 79 CREATE USER either succeeds for all named users or rolls back and has 80 no effect if any error occurs. By default, an error occurs if you try 81 to create a user that already exists. If the IF NOT EXISTS clause is 82 given, the statement produces a warning for each named user that 83 already exists, rather than an error. 84 85 URL: https://dev.mysql.com/doc/refman/8.0/en/create-user.html 86 87 举例： 88 #格式： 89 用户名@'白名单' 90 #举例： 91 test@'%' 92 test@'10.0.0.1' 93 test@'10.0.0.%' 24掩码 1-254 94 test@'10.0.0.5%' 50-59 95 test@'localhost' 数据库本地socket
2.创建用户
1 create user test@'10.0.0.%' identified by '123456abcd'; 2 Query OK, 0 rows affected (0.01 sec) 3 4 create user oldguo@'10.0.0.%' identified by '123456abcd'; 5 mySQL> select user,host,authentication_string,plugin from mySQL.user; 6 +------------------+-----------+------------------------------------------------------------------------+-----------------------+ 7 | user | host | authentication_string | plugin | 8 +------------------+-----------+------------------------------------------------------------------------+-----------------------+ 9 | mySQL.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password 10 sha2 : 8.0新的特性 默认密码插件 11 native: 兼容老版本 5.6,5.7版本 12 13 #可以更改密码插件 14 create user test@'10.0.0.%' identified with mySQL_native_password by '123456abcd'; 15 mySQL> select user,host,authentication_string,plugin from mySQL.user; 16 +------------------+-----------+------------------------------------------------------------------------+-----------------------+ 17 | user | host | authentication_string | plugin | 18 +------------------+-----------+------------------------------------------------------------------------+-----------------------+ 19 | test | 10.0.0.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | mySQL_native_password
3.创建密码/修改密码
#1，创建密码 mySQL> create user oldguo@'10.0.0.%'; Query OK, 0 rows affected (0.02 sec) mySQL> create user test@'10.0.0.%' identified by '123456abcd'; #2.修改密码 mySQL> alter user user1@'10.0.0.%' identified by '123456abcd'; 只修改密码 mySQL> alter user oldguo@'10.0.0.%' identified with mySQL_native_password by '123456abcd'; 修改密码及密码插件
4.删除用户
#生产谨慎！！！！！！！！！！！！！ drop user test@'10.0.0.%'; mySQL> select user,host from mySQL.user where (user='' or host='' or authentication_string='') and user!='root'; +-------+-----------+ | user | host | +-------+-----------+ | test1 | | | | localhost | +-------+-----------+ 2 rows in set (0.01 sec) mySQL> drop user test1@''; Query OK, 0 rows affected (0.00 sec) mySQL> drop user ''@'localhost'; Query OK, 0 rows affected (0.01 sec)
5.修改用户
alter user oldguo@'10.0.0.%' identified with mySQL_native_password by '123456abcd';
如果是caching_sha2_password 密码插件，因为8.0默认密码插件为caching_sha2_password alter user oldguo@'10.0.0.%' identified by '123456abcd';
6.锁用户
1 #一般不会删除用户 2 可以先将不用的用户锁住，如果还是有人使用，可以快速解锁 3 ALTER USER 'test'@'10.0.0.%' ACCOUNT LOCK; 4 5 #解锁用户 6 ALTER USER 'test'@'10.0.0.%' ACCOUNT UNLOCK; 7 8 #查看用户是否上锁（其中N表示未锁，Y表示已锁用户,mysql 有3个默认用户是带锁） 9 mySQL> select user,host,authentication_string,plugin, account_locked from mySQL.user; 10 +------------------+-----------+------------------------------------------------------------------------+-----------------------+----------------+ 11 | user | host | authentication_string | plugin | account_locked | 12 | test | 10.0.0.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257| mySQL_native_password | N 13 | root | localhost | | caching_sha2_password | N
7.忘记root管理原密码处理方式
1 1. 关闭数据库 2 /etc/init.d/mySQLd stop 3 4 2. 安全模式启动数据库并后台启动 5 [root@localhost data]# mySQLd_safe --skip-grant-tables --skip-networking & 6 --skip-grant-tables #不加载用户认证授权表 7 --skip-networking #关闭TCP协议，只能本地连接 8 9 3. 登陆数据库 10 mySQL 11 12 4. 刷新授权表 13 flush privileges; #因为跳过用户证授权表了，所以需要人为加载授权表 14 15 5. 修改密码 16 mySQL> alter user root@'localhost' identified with mySQL_native_password by '123456abcd'; 17 18 6. 重启数据库到正常模式 19 [root@localhost data]# /etc/init.d/mySQLd restart