MySQL - 授权权限

正如我们前面学到的，安装 MySQL 后，root 用户会立即连接到服务器（使用密码）。此用户可用的权限是默认的。使用 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 ] ...
   ]
]

示例

假设我们使用 CREATE USER 语句在 MySQL 中创建了一个名为 '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)

以下查询将上面创建的表的 SELECT 权限授予用户 'test_user'@'localhost'：

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”的存储过程和一个存储函数，如下所示 -

//Creating a procedure
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)

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

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));

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

Query OK, 0 rows affected (3.55 sec)

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

创建用户“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 子句之后使用 *.*。

示例

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

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** 的数据库中所有对象上的 SELECT、INSERT 和 UPDATE 权限授予用户 'test_user'@'localhost' -

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" });

  //Connecting to 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://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 = "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.