MySQL - 授予权限

• MySQL 授予权限
• MySQL GRANT 语句
• 授予存储例程权限
• 授予多个用户的权限
• 全局权限
• 数据库级权限
• 列级权限
• 代理用户权限
• 授予角色
• 使用客户端程序授予权限

正如我们之前所了解的，root 用户在安装 MySQL 后会立即连接到服务器(使用密码)。该用户可用的权限是默认的。使用 root 帐户访问 MySQL 的用户拥有足够的权限对​​数据执行基本操作。但是，在特殊情况下，用户必须手动请求主机授予权限。

MySQL 授予权限

MySQL 提供了一些 SQL 语句来允许或限制用户与数据库中存储的数据交互的管理权限。它们列示如下 -

• GRANT 语句

• REVOKE 语句

在本教程中，我们将详细了解 GRANT 语句。

MySQL GRANT 语句

MySQL GRANT 语句用于向 MySQL 用户帐户分配各种权限或角色。但是，需要注意的是，您不能在单个 GRANT 语句中同时分配权限和角色。要使用此语句向用户授予权限，您需要拥有 GRANT OPTION 权限。

语法

以下是 MySQL GRANT 语句的语法 -

GRANT
privilege1, privilege2, privilege3...
ON object_type
TO user_or_role1, user_or_role2, user_or_role3...
[WITH GRANT OPTION]
[AS user
  [WITH ROLE
    DEFAULT
    | NONE
    | ALL
    | ALL EXCEPT role [, role ] ...
    | role [, role ] ...
   ]
]

示例

假设我们在 MySQL 中使用 CREATE USER 语句创建了一个名为 'test_user'@'localhost' 的用户 -

CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'testpassword';

以下是上述代码的输出 -

Query OK, 0 rows affected (0.23 sec)

现在，让我们创建一个数据库 -

CREATE DATABASE test_database;

生成的输出如下 -

Query OK, 0 rows affected (0.56 sec)

接下来，我们将使用创建的数据库 -

USE test_database;

我们得到如下所示的输出 -

Database changed

现在，让我们在数据库中创建一个表 -

CREATE TABLE MyTable(data VARCHAR(255));

获得的输出如下 -

Query OK, 0 rows affected (0.67 sec)

以下查询将授予用户"test_user"@"localhost"对上述创建的表的 SELECT 权限 -

GRANT SELECT ON test_database.MyTable TO 'test_user'@'localhost';

执行上述代码后，我们得到以下输出 -

Query OK, 0 rows affected (0.31 sec)

验证

您可以使用 SHOW GRANTS 语句验证已授予的权限 -

SHOW GRANTS FOR 'test_user'@'localhost';

我们得到的输出如下所示 -

授予各种权限

我们知道，MySQL GRANT 语句可以授予用户帐户各种权限。以下是一些可以使用 GRANT 语句授予的常用权限列表 -

要授予用户所有可用权限，您需要在 GRANT 语句中使用"ALL"关键字 -

GRANT ALL ON test_database.MyTable TO 'test_user'@'localhost';

执行上述代码后，我们得到以下输出 -

Query OK, 0 rows affected (0.13 sec)

授予存储例程的权限

要在 MySQL 中授予存储例程(例如表、过程或函数)的权限，您需要在 ON 子句后指定对象类型(PROCEDURE 或 FUNCTION)，后跟例程的名称。

您可以授予这些存储例程的 ALTER ROUTINE、CREATE ROUTINE、EXECUTE 和 GRANT OPTION 权限。

示例

假设我们在当前数据库中创建了一个名为"sample"的存储过程和存储函数，如下所示 -

//创建一个过程
DELIMITER //
CREATE PROCEDURE sample ()
   BEGIN
      SELECT 'This is a sample procedure';
   END//
Query OK, 0 rows affected (0.29 sec)

//Creating a function
CREATE FUNCTION sample()
   RETURNS VARCHAR(120)
   DETERMINISTIC
   BEGIN
      DECLARE val VARCHAR(120);
      SET val = 'This is a sample function';
      return val;
   END// 
DELIMITER ;

以下是获得的输出 -

Query OK, 0 rows affected (0.34 sec)

创建这些存储例程后，您可以向名为 'test_user'@'localhost' 的用户授予上述创建过程的 ALTER ROUTINE、EXECUTE 权限，如下所示 -

GRANT ALTER ROUTINE, EXECUTE ON
PROCEDURE test_database.sample TO 'test_user'@'localhost';

生成的输出如下所示 -

Query OK, 0 rows affected (0.24 sec)

现在，以下查询将上述创建的函数的 ALTER ROUTINE, EXECUTE 权限授予名为 'test_user'@'localhost' 的用户。

GRANT ALTER ROUTINE, EXECUTE ON FUNCTION test_database.sample TO 'test_user'@'localhost';

以下是上述查询的输出 -

Query OK, 0 rows affected (0.15 sec)

授予多个用户的权限

您可以向多个用户授予权限。为此，您需要提供对象或用户的名称，并以逗号分隔。

示例

假设我们使用如下所示的 CREATE 语句创建了一个名为"sample"的表和三个用户帐户。

创建表 -

CREATE TABLE sample (data VARCHAR(255));

我们将得到如下所示的输出 -

查询成功，0 行受影响(3.55 秒)

现在，让我们创建用户帐户。

创建用户"test_user1"-

CREATE USER test_user1 IDENTIFIED BY 'testpassword';

获得的输出如下 −

Query OK, 0 rows affected (0.77 sec)

创建用户"test_user2" -

CREATE USER test_user2 IDENTIFIED BY 'testpassword';

以下是生成的输出 -

Query OK, 0 rows affected (0.28 sec)

创建第三个用户 −

创建用户"test_user3" −

CREATE USER test_user3 IDENTIFIED BY 'testpassword';

我们得到如下输出 −

Query OK, 0 rows affected (0.82 sec)

以下查询使用单个 GRANT 语句向所有三个用户("test_user1"、"test_user2"和"test_user3")授予对表"sample1"、"sample2"和"sample3"的 SELECT、INSERT 和 UPDATE 权限。

GRANT SELECT, INSERT, UPDATE ON TABLE sample TO test_user1, test_user2, test_user3;

输出

执行上述代码后，我们得到以下输出 -

Query OK, 0 rows affected (0.82 sec)

全局权限

您可以授予全局权限，而无需指定表、过程或函数:即适用于所有数据库的权限。为此，您需要在 ON 子句后使用 *.*。

示例

以下查询将向名为"test_user"@"localhost"的用户授予所有数据库的 SELECT、INSERT 和 UPDATE 权限 -

GRANT SELECT, INSERT, UPDATE ON *.* TO 'test_user'@'localhost';

输出

以下是获得的输出 -

Query OK, 0 rows affected (0.43 sec)

示例

类似地，以下查询将所有数据库的所有权限授予 'test_user'@'localhost -

GRANT ALL ON *.* TO 'test_user'@'localhost';

输出

生成的输出如下所示 -

Query OK, 0 rows affected (0.41 sec)

数据库级别权限

您可以通过在 ON 子句后指定数据库名称并加上".*"来授予数据库中所有对象的权限。

示例

以下查询将授予用户"test_user"@"localhost"对名为 test 的数据库中所有对象的 SELECT、INSERT 和 UPDATE 权限 -

GRANT SELECT, INSERT, UPDATE 
ON test.* TO 'test_user'@'localhost';

输出

以下是上述代码的输出 -

Query OK, 0 rows affected (0.34 sec)

示例

类似地，以下查询将所有数据库的所有权限授予 'test_user'@'localhost -

GRANT ALL ON test.* TO 'test_user'@'localhost';

输出

上述代码的输出如下 -

Query OK, 0 rows affected (0.54 sec)

列级权限

您可以向用户授予表中特定列的权限。为此，您需要在权限后指定列名。

示例

假设我们使用 CREATE 查询创建了一个名为 Employee 的表，如下所示:-

CREATE TABLE Employee (
ID INT, Name VARCHAR(15), Phone INT, SAL INT);

生成的输出如下所示:-

Query OK, 0 rows affected (6.47 sec)

以下查询授予名为 'test_user'@'localhost' 的用户在 ID 列上的 SELECT 权限，以及在 Employee 表的 Name 和 Phone 列上的 INSERT 和 UPDATE 权限 -

GRANT SELECT (ID), INSERT (Name, Phone) 
ON Employee TO 'test_user'@'localhost';

获得的输出如下 −

Query OK, 0 rows affected (0.54 sec)

代理用户权限

您可以通过授予一个用户 PROXY 权限，使其成为另一个用户的代理。这样做后，两个用户将拥有相同的权限。

示例

假设我们在 MySQL 中使用 CREATE 语句创建了一个名为 sample_user, proxy_user 的用户，如下所示 -

CREATE USER sample_user, proxy_user IDENTIFIED BY 'testpassword';

以下是获得的输出 -

Query OK, 0 rows affected (0.52 sec)

以下查询将上面创建的 Employee 表的 SELECT 和 INSERT 权限授予用户 sample_user -

GRANT SELECT, INSERT ON Emp TO sample_user;

我们得到如下所示的输出 -

Query OK, 0 rows affected (0.28 sec)

现在，我们可以使用 GRANT 语句为用户 proxy_user 分配代理权限，如下所示 -

GRANT PROXY ON sample_user TO proxy_user;

输出如下 -

Query OK, 0 rows affected (1.61 sec)

授予角色

MySQL 中的角色是一组具有名称的权限。您可以使用 CREATE ROLE 语句在 MySQL 中创建一个或多个角色。如果使用不带 ON 子句的 GRANT 语句，则可以授予角色而不是权限。

示例

我们首先创建一个名为 TestRole_ReadOnly 的角色。

CREATE ROLE 'TestRole_ReadOnly';

以下是获得的输出 -

Query OK, 0 rows affected (0.13 sec)

现在，让我们使用 GRANT 语句向创建的角色授予访问数据库中所有对象的只读权限 -

GRANT SELECT ON * . * TO 'TestRole_ReadOnly';

此 GRANT 语句的输出应为 -

Query OK, 0 rows affected (0.14 sec)

然后，您可以将创建的角色授予特定用户。首先，您需要按如下所示创建用户 -

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

以下是生成的输出 -

Query OK, 0 rows affected (0.14 sec)

现在，您可以将"TestRole_ReadOnly"角色授予"newuser"@"localhost" -

GRANT 'TestRole_ReadOnly' TO 'newuser'@'localhost';

获得的输出如下所示 -

Query OK, 0 rows affected (0.13 sec)

使用客户端程序授予权限

现在，让我们看看如何使用客户端程序向 MySQL 用户授予权限。

语法

以下是语法 -

$sql = "GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost'";
$mysqli->query($sql);

sql= "GRANT privilege_name(s) ON object TO user_account_name";
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

String sql = "GRANT ALL PRIVILEGES ON DATABASE_NAME.* TO 'USER_NAME'@'localhost'";
statement.execute(sql);

sql = f"GRANT {privileges} ON your_database.* TO '{username_to_grant}'@'localhost'";
cursorObj.execute(sql);

示例

以下是程序 -

$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 = "GRANT ALL PRIVILEGES ON tutorials.* TO 'Revathi'@'localhost'";
if($result = $mysqli->query($sql)){
   printf("Grant privileges executed successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

Grant privileges executed successfully...!

var mysql = require('mysql2');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "Nr5a0204@123" });
 //连接到 MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");
 sql = "CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'testpassword';"
  con.query(sql);
 sql = "CREATE DATABASE test_database;"
  con.query(sql);
  sql = "USE test_database;"
  con.query(sql);
  sql = "CREATE TABLE MyTable(data VARCHAR(255));"
  con.query(sql);
 sql = "GRANT SELECT ON test_database.MyTable TO 'test_user'@'localhost';"
  con.query(sql);
  sql = "SHOW GRANTS FOR 'test_user'@'localhost';";
  con.query(sql, function(err, result){
    if (err) throw err;
    console.log(result);
  });
});

Connected!
--------------------------
[
  {
    'Grants for test_user@localhost': 'GRANT USAGE ON *.* TO `test_user`@`localhost`'
  },
  {
    'Grants for test_user@localhost': 'GRANT SELECT ON `test_database`.`mytable` TO `test_user`@`localhost`'
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class GranPriv {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost: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 = "GRANT ALL PRIVILEGES ON tutorials.* TO 'Vivek'@'localhost'";
            st.execute(sql);
            System.out.println("You grant all privileges to user 'Vivek'...!");    
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

You grant all privileges to user 'Vivek'...!

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password'
)
username_to_grant = 'newUser'
# privileges we want to grant
privileges = 'SELECT, INSERT, UPDATE'  
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute(f"GRANT {privileges} ON your_database.* TO '{username_to_grant}'@'localhost'")
print(f"Privileges granted to user '{username_to_grant}' successfully.")
cursorObj.close()
connection.close()

Privileges granted to user 'newUser' successfully.