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://127.0.0.1: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.