MySQL - 建立使用者

在 MySQL 中，您可以建立多個使用者帳戶來訪問資料庫，每個帳戶都有特定的身份驗證詳細資訊，例如密碼。可以使用 SQL 語句（例如，建立新使用者時的CREATE USER 身份驗證，以及分別分配和刪除管理許可權的GRANT 和REVOKE）授予這些使用者特定許可權。

MySQL CREATE USER 語句

我們可以使用 MySQL 中的 CREATE USER 語句建立一個新的使用者帳戶。要執行此語句，當前帳戶必須具有 CREATE USER 許可權或 MySQL 系統模式的 INSERT 許可權。

語法

以下是 MySQL CREATE USER 語句的語法：

CREATE USER 'user_name'@'host_name' IDENTIFIED BY 'password';

其中：

• user_name 是您需要建立的使用者名稱。

• hostname 指定使用者可以從中連線的主機。

• password 是使用者的密碼。

示例

在下面的查詢中，我們正在建立一個名為“sample”的使用者，該使用者只能從“localhost”主機連線，並將他們的密碼設定為“123456”。請確保您已使用具有管理員許可權（root）的使用者登入：

CREATE USER 'sample'@'localhost' IDENTIFIED BY '123456';

輸出

輸出將顯示為：

Query OK, 0 rows affected (0.12 sec)

驗證

您可以使用以下查詢驗證使用者列表：

SELECT USER FROM MySQL.USER;

表將顯示如下：

在 MySQL 中授予許可權

您可以使用 GRANT ALL 語句向建立的使用者授予所有許可權。這允許您為使用者提供對資料庫、表執行操作（例如 SELECT、INSERT 或 DELETE）的特定許可權。

語法

以下是 MySQL 中授予所有許可權的語法：

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

示例

以下查詢授予使用者“sample”在從“localhost”主機連線時對任何資料庫或表執行任何操作的完全許可權，從而提供對本地 MySQL 伺服器的完全控制：

GRANT ALL PRIVILEGES ON * . * TO 'sample'@'localhost';

輸出

輸出將顯示為：

Query OK, 0 rows affected (0.02 sec)

以不同使用者身份登入

要在 MySQL 中以不同的使用者身份登入，如果已經登入，則應首先退出當前 MySQL 會話，然後在系統的命令提示符或終端中（而不是在 MySQL shell 本身中）執行命令-u user_name -p。

示例

在這裡，我們正在執行-u sample -p 命令。執行該命令後，系統將提示您輸入指定使用者的密碼。輸入正確的密碼以如下所示的使用者身份登入：

mysql -u sample -p
Enter password: ******

輸出

這將以 sample 使用者身份登入，並具有如下所示的相應許可權和許可：

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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.

EXPIRE 子句

如果您使用 expire 子句，舊密碼（當前密碼）將立即過期，使用者需要在第一次連線時選擇新密碼。

示例

在這裡，我們首先刪除現有的“sample”@“localhost”使用者：

DROP user sample@localhost;

我們現在正在建立一個新的“sample”@“localhost”使用者，密碼為“MyPassword”，同時立即使密碼過期，強制使用者在第一次登入時設定新密碼：

CREATE USER 'sample'@'localhost' 
IDENTIFIED BY 'MyPassword' PASSWORD EXPIRE;

現在，如果您以新建立的使用者身份登入，將生成錯誤。因此，要以新建立的使用者身份登入，請開啟命令提示符，瀏覽到 MySQL 目錄的 bin 資料夾，然後執行以下命令：

C:\Program Files\MySQL\MySQL Server 8.0\bin> mysql -u sample@localhost -p
Enter password: **********

此時執行任何 MySQL 命令都將觸發如下所示的錯誤訊息：

select now(); 

獲得的輸出如下所示：

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

由於密碼已過期，因此會生成上述錯誤訊息。要解決此問題，我們需要使用以下命令更改（重置）密碼：

SET PASSWORD='passwordtest'; 

生成的輸出如下所示：

Query OK, 0 rows affected (0.34 sec)

您還可以為 EXPIRE 子句設定一個間隔，以實現定期更改密碼，如下所示：

DROP user sample@localhost;
CREATE USER 'sample'@'localhost'
   IDENTIFIED BY 'MyPassword'
   PASSWORD EXPIRE INTERVAL 25 DAY
   FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

執行上述程式碼後，我們將獲得以下輸出：

Query OK, 0 rows affected (0.20 sec)

使用者評論

在 MySQL 中建立使用者時，您可以使用 COMMENT 子句向用戶添加註釋。這提供了有關使用者的其他資訊或上下文。

示例

在下面的示例中，我們首先刪除現有的“sample”@“localhost”使用者。然後，我們建立一個新的“sample”@“localhost”使用者，同時添加註釋來描述使用者：

drop user sample@localhost;
CREATE USER 'sample'@'localhost' COMMENT 'Sample information';

輸出

獲得的結果如下所示：

Query OK, 0 rows affected (0.10 sec)

驗證

您可以使用以下 SELECT 查詢驗證屬性和註釋資訊：

SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES 
WHERE USER='sample' AND HOST='localhost';

生成的輸出如下所示：

使用者屬性

在建立使用者帳戶時，您可以使用 ATTRIBUTE 子句向 MySQL 中的使用者新增屬性。這些屬性可以儲存有關使用者的其他資訊。

示例

在這裡，我們首先刪除現有的“sample@localhost”使用者。然後，我們建立一個新的“sample”@“localhost”使用者，其屬性“attr1”和“attr2”分別設定為“val1”和“val2”，並與使用者帳戶相關聯：

DROP user sample@localhost;
CREATE USER 'sample'@'localhost' 
ATTRIBUTE '{"attr1": "val1", "attr2": "val2"}'; 

獲得的結果如下所示：

輸出

Query OK, 0 rows affected (0.09 sec)

驗證

您可以使用以下 SELECT 查詢驗證屬性和註釋資訊：

SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES 
WHERE USER='sample' AND HOST='localhost';

獲得的結果如下所示：

IF NOT EXISTS 子句

如果您嘗試建立具有現有名稱的使用者，則會生成錯誤。為防止此錯誤並確保僅在使用者不存在時才建立使用者，您可以使用“IF NOT EXISTS”子句。

示例

下面的例子中，我們建立了一個名為 'sample@localhost' 的使用者，**沒有**使用 "IF NOT EXISTS" 子句。

CREATE USER 'sample@localhost';

從下面的輸出中可以看到，生成了一個錯誤。

ERROR 1396 (HY000): Operation CREATE USER failed for 'sample@localhost'@'%'

但是，如果我們在 CREATE 語句中使用 "IF NOT EXISTS" 子句，則會建立一個新使用者；如果已存在具有給定名稱的使用者，則該查詢將被忽略。

CREATE USER IF NOT EXISTS 'sample@localhost';

以下是獲得的輸出。

Query OK, 0 rows affected, 1 warning (0.01 sec)

使用客戶端程式建立使用者

除了使用 MySQL 查詢在 MySQL 資料庫中建立使用者外，我們還可以使用客戶端程式建立使用者。

語法

以下是使用各種程式語言建立 MySQL 使用者的語法：

$sql = "CREATE USER 'user_name'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'";
$mysqli->query($sql);

sql= " CREATE USER [IF NOT EXISTS] account_name IDENTIFIED BY 'password'; 
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

sql = "CREATE USER 'USER_NAME'@LOCALHOST INDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'PASSWORD'";
statement.execute(sql);

sql = "CREATE USER 'UserNew'@'localhost' IDENTIFIED BY 'newPassword'";
cursorObj.execute(sql)

示例

以下是用於在 MySQL 中建立使用者的客戶端程式：

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.
');
$sql = "CREATE USER 'Revathi'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'";
if($mysqli->query($sql)){
   printf("User created successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();     

User created successfully...!

var mysql = require('mysql2');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "Nr5a0204@123"
});

//Connecting to MySQL
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   console.log("--------------------------");

   //Creating User
   sql = "CREATE USER 'sample'@'localhost' IDENTIFIED BY '123456';"
   con.query(sql);

   //List of users
   sql = "select user from MySQl.user;"
   con.query(sql, function(err, result){
      if (err) throw err
      console.log(result)
   });
});

Connected!
--------------------------
[
  { user: 'mysql.infoschema' },
  { user: 'mysql.session' },
  { user: 'mysql.sys' },
  { user: 'root' },
  { user: 'sample' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class CreateUsers {
   public static void main(String[] args) {
      String url = "jdbc:mysql://:3306/TUTORIALS";
      String user = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection con = DriverManager.getConnection(url, user, password);
         Statement st = con.createStatement();
         //System.out.println("Database connected successfully...!");
         String sql = "CREATE USER 'Vivek'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'";
         st.execute(sql);
         System.out.println("User 'Vivek' created successfully...!");
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

User 'Vivek' created successfully...!

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
   host='localhost',
   user='root',
   password='password',
   database='textx'
)
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute("CREATE USER 'UserNew'@'localhost' IDENTIFIED BY 'newPassword'")
print("User 'newUser' is created successfully.")
cursorObj.close()
connection.close()

User 'newUser' is created successfully.