投稿
  1. 簡單一點首頁
  2. 投稿

分析mysql創建用戶的方法「mysql免密碼登錄下無法創建用戶」

投稿專員 • • 投稿

一、用戶與權限管理

（一）grant

1、help grant

和權限相關的命令關鍵字grant可通過help查看其用法：

mysql> help grant;
Name: 'GRANT'
Description:
Syntax:
GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH with_option ...]

...
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
...

2、grant授權

對於上述：

GRANT ALL ON db1.* TO 'jeffrey'@'localhost';

它實際包含了兩條命令，先是創建用戶jeffrey，然後才是對這個用戶進行授權。如下：

mysql> create user 'jeffrey'@'localhost' identified by 'mypass';
mysql> grant all on db1.* to 'jeffrey'@'localhost' identified by 'mypass';

對於授權語句的一些關鍵字解釋如下：

grantall privilegeson dbname.*to username@localhostIdentified by ‘mypass’
授權命令對應權限目標：庫和表用戶名和客戶端主機用戶密碼

3、實戰練習

  • 查看當前數據庫用戶情況
mysql> select user,host from mysql.user;
+--------+---------------+
| user   | host          |
+--------+---------------+
| root   | 127.0.0.1     |
| root   | ::1           |
| root   | hadoop-slave1 |
| root   | localhost     |
| system | localhost     |
+--------+---------------+
5 rows in set (0.00 sec)
  • 創建用戶並授權
mysql> grant all privileges on test.* to 'admin'@'localhost' identified by 'admin123';
Query OK, 0 rows affected (0.01 sec)
  • 查看授權情況
mysql> select user,host from mysql.user;
+--------+---------------+
| user   | host          |
+--------+---------------+
| root   | 127.0.0.1     |
| root   | ::1           |
| root   | hadoop-slave1 |
| admin  | localhost     |
| root   | localhost     |
| system | localhost     |
+--------+---------------+
6 rows in set (0.00 sec)
  • 查看admin具體權限
mysql> show grants for 'admin'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for admin@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'admin'@'localhost'                                                      |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

（二）create和grant配合使用

1、使用步驟

  • 創建用戶username、主機localhost、密碼passwd
mysql> create user 'username'@'localhost' identified by 'passwd';
  • 授權創建的用戶管理dbname數據庫權限（無需密碼）
mysql> grant all privileges to dbname.* to 'username'@'localhost';

2、實戰練習

  • 查看當前數據庫用戶情況
mysql> select user,host from mysql.user;
+--------+---------------+
| user   | host          |
+--------+---------------+
| root   | 127.0.0.1     |
| root   | ::1           |
| root   | hadoop-slave1 |
| admin  | localhost     |
| root   | localhost     |
| system | localhost     |
+--------+---------------+
6 rows in set (0.02 sec)
  • 創建用戶
mysql> create user 'admin1'@'localhost' identified by 'admin123456';
Query OK, 0 rows affected (0.02 sec)

注意的是這一步並沒有授權，僅僅是創建一個普通用戶。

  • 查看用戶情況
mysql> select user,host from mysql.user;
+--------+---------------+
| user   | host          |
+--------+---------------+
| root   | 127.0.0.1     |
| root   | ::1           |
| root   | hadoop-slave1 |
| admin  | localhost     |
| admin1 | localhost     |
| root   | localhost     |
| system | localhost     |
+--------+---------------+
7 rows in set (0.00 sec)

如果對admin1進行授權就參照步使用步驟的第二步完成。

（三）用戶授權的權限有什麼

1、查看用戶權限

在上面授權過程中可以看出來，使用的基本都是全部權限：

grant all privileges to dbname.* to 'username'@'localhost';

然後查看用戶的權限後是這樣的：

mysql> show grants for 'admin'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for admin@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'admin'@'localhost'                                                      |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

有時候並不需要給用戶給這麼多權限，那麼怎麼收回呢？

2、回收用戶權限（revoke）

mysql> revoke insert on test.* from 'admin'@'localhost';  #一定要指定在那個數據庫上的權限
Query OK, 0 rows affected (0.00 sec)

可以再次查看該用戶的權限：

mysql> show grants for 'admin'@'localhos
+---------------------------------------------------------------------------------------------------------+
| Grants for admin@localhost                                                                         |
+---------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' |
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES,LOCK TABLES, EXECUTE,
 CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'admin'@'localhost' |
+----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

可以看到用戶在test數據庫上除了insert權限外的權限它都有了。

也就是說數據庫的all priveleges包含下面的權限：

INSERT, 
SELECT, 
UPDATE, 
DELETE, 
CREATE, 
DROP,
REFERENCES, 
INDEX, 
ALTER, 
CREATE TEMPORARY TABLES,
LOCK TABLES, 
EXECUTE,
CREATE VIEW, 
SHOW VIEW,
CREATE ROUTINE,
ALTER ROUTINE,
EVENT, 
TRIGGER

所以我們在授權時盡量採用最小化的授權原則，比如：

mysql> grant select,insert,update,delete,create,drop on crm.* to 'admin'@'10.0.0.%' identified by '123456';

當admin用戶創建表後記得收回create權限：

mysql> revoke create on crm.* from 'admin'@'10.0.0.0.%';

注意：可通過help revoke查看用法

二、遠程連接

通過上面的授權，比如：…’admin1’@’localhost’..中的localhost是授權的主機，也就是說什麼樣的機器有權限連接MySQL服務器。 localhost可以用域名、IP地址、IP端來代替。

（一）匹配方式

1、百分號匹配法

mysql> grant all 0n dbname.* to 'admin1'@'10.0.0.%' identified by '123456';
mysql> flush privileges;

2、子網掩碼配置法

mysql> grant all 0n dbname.* to 'admin1'@'10.0.0.0、255.255.255.0' identified by '123456';
mysql>flush privileges;

（二）客戶端連接

客戶端本地連接與遠程連接是不一樣的，如果遠程連接首先應該賦予遠程連接的權限：

mysql> grant all 0n dbname.* to 'admin1'@'10.0.0.%' identified by '123456';

其次，再進行遠程連接：

mysql> mysql -uadmin1 -p123456 -h 10.0.0.0.3

原創文章，作者：投稿專員，如若轉載，請註明出處：https://www.506064.com/zh-hant/n/284441.html

贊 (0)
打賞 微信掃一掃 微信掃一掃 支付寶掃一掃 支付寶掃一掃
投稿專員的頭像投稿專員
0 0
上一篇 2024-12-22 15:07
下一篇 2024-12-22 15:07

相關推薦

發表回復

登錄後才能評論