Mysql 简明教程
MySQL - Lock User Account
通过阻止未经授权的事务或可疑活动来增加数据库的安全性，从而引入了 MySQL 中的帐户锁定。
Account Locking in MySQL is introduced to increase security of the database by preventing unauthorized transactions or suspicious activities.
在许多情况下，出于各种原因需要锁定 MySQL 用户帐户。例如，在完成帐户授权时等待，或如果该帐户已长时间处于非活动状态等。在这些情况下，锁定帐户将提高 MySQL 服务器的效率。
In many cases, the MySQL user accounts require to be locked for various reasons. For instance, to wait while completing the authorization of an account, or if the account has been inactive for a very long time, etc. In such cases, locking accounts will improve the efficiency of the MySQL server.
MySQL Lock User Account
为了检查某个帐户是否已锁定，MySQL 在“mysql.user”表中提供了“account_locked”属性，该属性将分别保存“Y”或“N”值。值“Y”表示帐户已锁定，而“N”表示未锁定。
To check whether an account is locked or not, MySQL provides the 'account_locked' attribute in the 'mysql.user' table that will hold either 'Y' or 'N' values respectively. A value of 'Y' indicates that the account is locked, while 'N' indicates that it is not locked.
Locking New Accounts
MySQL 提供 ACCOUNT LOCK 子句来锁定帐户。将此子句与 CREATE USER 和 ALTER USER 语句一起使用将分别创建新的已锁定用户或锁定现有用户。
MySQL provides ACCOUNT LOCK clause to lock the accounts. Using this clause with CREATE USER and ALTER USER statements will either create a new already locked user or lock the existing user respectively.
Syntax
以下是 CREATE USER… ACCOUNT LOCK 语句的语法 −
Following is the syntax of CREATE USER… ACCOUNT LOCK statement −
CREATE USER username@hostname
IDENTIFIED BY 'new_password' ACCOUNT LOCK;Example
在以下查询中，我们使用 CREATE USER 语句在 MySQL 中创建新的已锁定用户帐户 −
In the following query, we are creating a new already-locked user account in MySQL using the CREATE USER statement −
CREATE USER test@localhost IDENTIFIED BY 'asdfgh' ACCOUNT LOCK;Output
以下是上面代码的输出： -
Following is the output of the above code −
Query OK, 0 rows affected (0.02 sec)Verification
我们使用以下 SELECT 语句验证用户 'test' 的帐户是否已锁定:
We can verify whether the account of the 'test' user is locked or not using the following SELECT statement −
SELECT User, Host, account_locked
FROM mysql.user WHERE User = 'test';以上代码的输出如下所示 −
Output of the above code is as shown below −
由于账户被锁定，所以除非再次将其解锁，否则你无法访问该账户。查看下方的示例:
Since the account is locked, you cannot access it unless it is unlocked again. Look at the example below −
C:\Windows\System32> mysql -u test -p
Enter password: ******产生的结果如下 −
The result produced is as follows −
ERROR 3118 (HY000): Access denied for user 'test'@'localhost'. Account is locked.Locking Existing Accounts
我们可以在 MySQL 中使用 ALTER USER… ACCOUNT LOCK 语句锁定现有帐户。但在执行查询之前，你必须确保用户处于解锁状态。
We can use the ALTER USER… ACCOUNT LOCK statement to lock existing accounts in MySQL. But you must make sure that the user is in the unlock state before executing the query.
Syntax
以下是 ALTER USER… ACCOUNT LOCK 语句的语法:
Following is the syntax of ALTER USER… ACCOUNT LOCK statement −
ALTER USER username@hostname ACCOUNT LOCK;Example
在这里，我们使用 ALTER USER 语句锁定 MySQL 中的一个现有用户帐户 −
In here, we are locking an existing user account in MySQL using the ALTER USER statement −
ALTER USER sample@localhost ACCOUNT LOCK;Verification
我们可以使用以下 SELECT 语句来验证“示例”用户的帐户是否被锁定 −
We can verify whether the account of the 'sample' user is locked or not using the following SELECT statement −
SELECT User, Host, account_locked
FROM mysql.user WHERE User = 'sample';获得的结果如下所示 −
The result obtained is as shown below −
为了验证帐户是否被锁定，让我们如以下查询中所示访问它 −
To verify that the account is locked, let us access it as shown in the query below −
C:\Windows\System32> mysql -u sample -p
Enter password: ******我们得到了如下输出 −
We get the output as follows −
ERROR 3118 (HY000): Access denied for user 'sample'@'localhost'. Account is locked.