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