mysql用户权限
Let’s see how to grant permissions (called privileges) to a user of the MySQL database
让我们看看如何向MySQL数据库的用户授予权限(称为特权)
By default when you create a new MySQL user using the syntax
默认情况下,当您使用语法创建新MySQL用户时
CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';
the user cannot do much. We can say that it can’t to anything, actually.
用户不能做太多事情。 我们可以说实际上什么也做不了。
It can’t read data from any existing database, let alone modifying the data. And it can’t even create a new database.
它无法从任何现有数据库读取数据,更不用说修改数据了。 而且它甚至无法创建新的数据库。
To make a user do anything, you have to grant privileges to it.
要使用户执行任何操作,您必须为其授予特权 。
You can do so using the GRANT
command.
您可以使用GRANT
命令执行此操作。
We can use GRANT <permission>
, using the following permission keywords:
我们可以使用GRANT <permission>
,并使用以下权限关键字:
CREATE
CREATE
DROP
DROP
DELETE
DELETE
INSERT
INSERT
SELECT
SELECT
UPDATE
UPDATE
ALL PRIVILEGES
ALL PRIVILEGES
向用户授予创建新数据库的特权 (Give privilege to create new databases to a user)
GRANT CREATE ON *.* TO '<username>'@'localhost';
授予用户特权以在特定数据库中创建新表 (Give privileges to a user to create new tables in a specific database)
GRANT CREATE ON <database>.* TO '<username>'@'localhost';
授予向用户读取(查询)特定数据库的特权 (Give privilege to read (query) a specific database to a user)
GRANT SELECT ON <database>.* TO '<username>'@'localhost';
向用户授予读取特定数据库表的特权 (Give privilege to read a specific database table to a user)
GRANT SELECT ON <database>.<table> TO '<username>'@'localhost';
向用户授予在特定数据库中插入,更新和删除行的特权 (Give privilege to insert, update and delete rows in a specific database to a user)
GRANT INSERT, UPDATE, DELETE ON <database>.* TO '<username>'@'localhost';
向用户授予删除特定数据库中表的特权 (Give privilege to delete tables in a specific database to a user)
GRANT DROP ON <database>.* TO '<username>'@'localhost';
向用户授予删除数据库的特权 (Give privilege to delete databases to a user)
GRANT DROP ON *.* TO '<username>'@'localhost';
将特定数据库的所有特权授予用户 (Give all privilege on a specific database to a user)
GRANT ALL PRIVILEGES ON <database>.* TO '<username>'@'localhost';
授予用户所有特权 (Give all privileges to a user)
GRANT ALL PRIVILEGES ON *.* TO '<username>'@'localhost';
撤销特权 (Revoke a privilege)
Example to revoke the DROP
privilege on <database>
:
撤销<database>
上的DROP
特权的示例:
REVOKE DROP ON <database>.* TO '<username>'@'localhost';
To revoke all privileges, run:
要撤消所有特权,请运行:
REVOKE ALL PRIVILEGES ON *.* TO '<username>'@'localhost';
You can visualize the privileges of a single user by running:
您可以通过运行以下命令来可视化单个用户的特权:
SHOW GRANTS FOR '<username>'@'localhost';
mysql用户权限