目錄
MySQL用戶授權
一，密碼策略
1，查看臨時密碼
2，查看數(shù)據(jù)庫當前密碼策略：
二， 用戶授權和撤銷授權
1、創(chuàng)建用戶
2，刪除用戶
3，授權和回收權限
MySQL用戶授權
一，密碼策略
mysql剛安裝時，臨時密碼會存儲在 /var/log/mysqld.log
1，查看臨時密碼
方法一：直接給出密碼
[root@localhost ~]# awk '/temporary password/ {print $NF}' /var/log/mysqld.log
*2D):irrJ_!_
?
方法二：查到的語句的最后為密碼
[root@localhost ~]# grep 'password' /var/log/mysqld.log
2023-02-07T13:07:17.842453Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: *2D):irrJ_!_
?
?
?
2，查看數(shù)據(jù)庫當前密碼策略：
mysql8.0.30 [(none)]>show VARIABLES like"%password%";
二， 用戶授權和撤銷授權
MySql8有新的安全要求，不能像之前的版本那樣一次性創(chuàng)建用戶并授權。需要先創(chuàng)建用戶，再進行授權操作。
mysql8.0.30 [(none)]>grant all privileges on *.* to 'xiaoming'@'%';
ERROR 1410 (42000): You are not allowed to create a user with GRANT
?
1、創(chuàng)建用戶
創(chuàng)建新用戶，語法：create user 'username'@'host' identified by 'password';
說明：username為自定義的用戶名，host為客戶端的域名或者IP，如果host為'%'時表示為任意IP，password為密碼。
（1）創(chuàng)建一個用戶名為xiaoming，客戶端的ip為任意，密碼為Guest123!的新用戶
mysql8.0.30 [(none)]>create user xiaoming@'%' identified by 'Guest123!';
Query OK, 0 rows affected (0.01 sec)
（2）查看剛創(chuàng)建的的用戶信息
mysql8.0.30 [(none)]>select user,host from mysql.user;
+------------------+-----------+
| user???????????? | host????? |
+------------------+-----------+
| root???????????? | %???????? |
| xiaoming???????? | %???????? |
| mysql.infoschema | localhost |
| mysql.session??? | localhost |
| mysql.sys??????? | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
（3）使用xiaoming用戶登錄一下數(shù)據(jù)庫：
[root@localhost ~]# mysql -uxiaoming -p'Guest123!';
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.? Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.32 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
（4）使用xiaoming用戶查看所有的數(shù)據(jù)庫：
mysql8.0.30 [(none)]>show databases;
+--------------------+
| Database?????????? |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.01 sec)
?
（5）也可以在windows端登錄xiaoming用戶：
?
?
?
?
?
2，刪除用戶
（1）刪除用戶名為xiaoming，客戶端的ip為任意，密碼為Guest123!的用戶
mysql8.0.30 [(none)]>drop user xiaoming@'%';
Query OK, 0 rows affected (0.01 sec)
（2）刪除后的user表中已經(jīng)沒了xiaoming的信息
mysql8.0.30 [(none)]>select user,host from mysql.user;
+------------------+-----------+
| user???????????? | host????? |
+------------------+-----------+
| root???????????? | %???????? |
| mysql.infoschema | localhost |
| mysql.session??? | localhost |
| mysql.sys??????? | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
??
注意，如果刪除用戶時顯示如下提示：
mysql8.0[mysql]>drop user xiaoming;
ERROR 1227(42000): Access denied; you need (atleast oneof) the SYSTEM_USER privilege(s) forthis operation
需要執(zhí)行該語句：
【mysql8.0 [(none)]>grant? system_user? on *.*? to? root@'%';】
3，授權和回收權限
?
授予權限的原則：
（1）只授予能滿足需要的最小權限 ，防止用戶干壞事。比如用戶只是需要查
????????? 詢，那就只給 select 權限就可以了，不要給用戶賦予update 、 insert 或
?????????? 者 delete 權限
（2）創(chuàng)建用戶的時候限制用戶的登錄主機 ，一般是限制成指定 IP 或者內(nèi)網(wǎng)
????????? ?IP 段。
（3）為每個用戶設置滿足密碼復雜度的密碼 。
（4）定期清理不需要的用戶 ，回收權限或者刪除用戶。
?
授權語法：grant 權限列表 on 庫名.表名 to 用戶名@'主機' [with GRANT option];
沒有with GRANT option,被授權者無法授權于下一個人用戶
| mysql用戶常用權限列表 |
說明 |
| all 或者all privileges |
授予用戶所有權限 |
| create |
授予用戶創(chuàng)建新數(shù)據(jù)庫和表的權限 |
| drop |
授予用戶刪除數(shù)據(jù)庫和表的權限 |
| delete |
授予用戶刪除表中的行的權限 |
| alter |
授予用戶修改表結(jié)構的權限 |
| insert |
授予用戶在表中插入行（add）的權限 |
| select |
授予用戶運行select命令以從表中讀取數(shù)據(jù)的權限 |
| update |
授予用戶更新表中的數(shù)據(jù)的權限 |
查看授予用戶的權限的四種方法：
（1）?查看root用戶的權限（方法一）
mysql8.0.30 [(none)]>show grants;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@%??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
（2）查看xiaoming用戶的權限（方法二）
mysql8.0.30 [(none)]>show grants for xiaoming@'%';
+--------------------------------------+
| Grants for xiaoming@%??????????????? |
+--------------------------------------+
| GRANT USAGE ON *.* TO `xiaoming`@`%` |
+--------------------------------------+
1 row in set (0.00 sec)
（3）mysql8.0[mysql]>select* from?mysql.user;（方法三）
（4）方法四：
授權操作
不具有授予下一個人權限的權利的授權
（1）此時，xiaoming用戶可以訪問以下兩個數(shù)據(jù)庫，須授予其他數(shù)據(jù)庫的訪問權限，才能對其
? ? ? ? ?它數(shù)據(jù)庫進行操作
mysql8.0.30 [(none)]>show databases;
+--------------------+
| Database?????????? |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.01 sec)? 
?
?
（2）給xiaoming用戶授予所有權限
*.*中第一個*表示所有數(shù)據(jù)庫，第二個*表示所有數(shù)據(jù)表
mysql8.0.30 [(none)]>grant all privileges on *.* to xiaoming@'%';
Query OK, 0 rows affected (0.01 sec)
（3）此時xiaoming用戶擁有和root用戶一樣的權限，可以訪問其它數(shù)據(jù)庫的權限
mysql8.0.30 [(none)]>show databases;
+--------------------+
| Database?????????? |
+--------------------+
| chap03???????????? |
| information_schema |
| mysql????????????? |
| performance_schema |
| sys??????????????? |
+--------------------+
5 rows in set (0.00 sec)
（4）此時的xiaoming用戶沒有賦予下一個人權限的權利
mysql8.0.30 [(none)]>grant all privileges on *.* to xiaohei@'%';
ERROR 1045 (28000): Access denied for user 'xiaoming'@'%' (using password: YES)
mysql8.0.30 [(none)]>
具有授予下一個人權限的權利的授權
（1）重新授予xiaoming用戶授予其他用戶權限的權利
mysql8.0.30 [(none)]>grant all privileges on *.* to xiaoming@'%' with GRANT option;
Query OK, 0 rows affected (0.00 sec)
（2）使用xiaoming用戶為xiaohei 用戶授權
mysql8.0.30 [(none)]>grant all privileges on *.* to xiaohei@'%';
Query OK, 0 rows affected (0.01 sec)
（3）此時xiaohei用戶擁有訪問所有數(shù)據(jù)庫的權利
mysql8.0.30 [(none)]>select user(); （查看當前的用戶）
+-------------------+
| user()??????????? |
+-------------------+
| xiaohei@localhost |
+-------------------+
1 row in set (0.00 sec)
mysql8.0.30 [(none)]>show databases; （此用戶可以訪問以下數(shù)據(jù)庫的權利）
+--------------------+
| Database?????????? |
+--------------------+
| chap03???????????? |
| information_schema |
| mysql????????????? |
| performance_schema |
| sys??????????????? |
+--------------------+
5 rows in set (0.00 sec)
收回權限
收回權限(不包含賦權權限)
REVOKE ALL PRIVILEGES ON *.* FROM username;
收回賦權權限
REVOKE GRANT OPTION ON *.* FROM username;
收回賦權權限
（1）收回xiaoming用戶授予其他用戶權限的權利
mysql8.0.30 [(none)]>revoke grant option on *.* from xiaoming;
Query OK, 0 rows affected (0.00 sec)
（2）此時，xiaoming用戶不在擁有授予其他用戶的權限
mysql8.0.30 [(none)]>grant all privileges on *.* to xiaohei@'%';
ERROR 1045 (28000): Access denied for user 'xiaoming'@'%' (using password: YES)
mysql8.0.30 [(none)]>
收回權限(不包含賦權權限)
（1）收回xiaoming用戶的所有權限
mysql8.0.30 [(none)]>revoke all privileges on *.* from xiaoming@'%';
Query OK, 0 rows affected (0.00 sec)
（2）此時xiaoming用戶不在擁有訪問其它數(shù)據(jù)庫的權限文章來源：http://www.zghlxwxcb.cn/news/detail-515872.html
mysql8.0.30 [(none)]>show databases;
+--------------------+
| Database?????????? |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)
文章來源地址http://www.zghlxwxcb.cn/news/detail-515872.html
到了這里，關于【數(shù)據(jù)庫】 mysql用戶授權詳解的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！
