MySQL - REVOKE 語句

前面，我們討論了 root 使用者如何在安裝後獲得具有預設許可權的 MySQL 伺服器的訪問許可權。這些許可權足以對資料執行基本操作。但是，在某些特殊情況下，使用者可能需要請求伺服器主機撤銷某些許可權。為此，我們使用 MySQL REVOKE 語句。

MySQL REVOKE 語句

MySQL REVOKE 語句用於從使用者中刪除某些管理許可權或角色。它撤銷先前授予的許可權。

語法

以下是 MySQL REVOKE 語句的語法：

REVOKE privileges
   ON database_name.table_name
   FROM 'user'@'host';

示例

假設我們使用 CREATE USER 語句在 MySQL 中建立了一個名為 'test_user'@'localhost' 的使用者，如下所示：

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

生成的輸出如下：

Query OK, 0 rows affected (0.23 sec)

現在，讓我們建立一個名為 'test_database' 的資料庫：

CREATE DATABASE test_database;

生成的輸出如下：

Query OK, 1 row 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'：

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

我們得到的輸出如下所示：

現在，您可以使用如下所示的REVOKE 語句撤銷上述已授予的許可權：

REVOKE SELECT ON test_database.MyTable FROM 'test_user'@'localhost';

我們得到如下所示的輸出：

Query OK, 0 rows affected (0.25 sec)

驗證

我們可以使用如下所示的 SHOW GRANTS 語句驗證 SELECT 許可權是否已被撤銷：

SHOW GRANTS FOR 'test_user'@'localhost';

我們可以看到輸出不再列出 SELECT 許可權，這表明它已被撤銷：

撤銷所有許可權

如果使用者對使用者具有多個許可權，則可以使用 MySQL 中的 REVOKE ALL 語句一次撤銷所有這些許可權。

語法

以下是 MySQL 中撤銷所有許可權的語法：

REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'host';

示例

假設我們建立了一個使用者，如下所示：

CREATE USER 'sample_user'@'localhost';

生成的輸出如下：

Query OK, 0 rows affected (0.18 sec)

我們還建立了一個過程，如下所示：

DELIMITER //
CREATE PROCEDURE sample ()
   BEGIN
      SELECT 'This is a sample procedure';
   END//
DELIMITER ;

獲得的輸出如下：

Query OK, 0 rows affected (0.29 sec)

此外，我們在資料庫中建立一個名為 'sample' 的表：

CREATE TABLE sample(data INT);

我們得到如下所示的輸出：

Query OK, 0 rows affected (0.68 sec)

現在，以下查詢將 ALTER ROUTINE、EXECUTE 許可權授予到上面建立的過程給名為 'sample_user'@'localhost' 的使用者。

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

上述程式碼的輸出如下所示：

Query OK, 0 rows affected (0.20 sec)

類似地，以下查詢將 SELECT、INSERT 和 UPDATE 許可權授予到表 'sample' 給使用者 'sample_user'@'localhost'：

GRANT SELECT, INSERT, UPDATE 
ON test.sample TO 'sample_user'@'localhost';

產生的結果是：

Query OK, 0 rows affected (0.14 sec)

您可以使用 SHOW GRANTS 語句驗證使用者的所有已授予許可權列表：

SHOW GRANTS FOR 'sample_user'@'localhost';

獲得的結果如下：

最後，要撤銷授予 'sample_user'@'localhost' 的所有許可權，可以使用以下語句：

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'sample_user'@'localhost';

產生的結果是：

Query OK, 0 rows affected (0.30 sec)

驗證

撤銷許可權後，您可以再次檢查使用者的許可權：

SHOW GRANTS FOR 'sample_user'@'localhost';

下面的輸出確認所有許可權已被撤銷：

撤銷代理許可權

您可以透過授予 PROXY 許可權來使一個使用者成為另一個使用者的代理。如果您這樣做，則兩個使用者都具有相同的許可權。

示例

假設我們使用 CREATE 語句在 MySQL 中建立了名為 sample_user、proxy_user 的使用者：

CREATE USER sample_user, proxy_user IDENTIFIED BY 'testpassword';

獲得的輸出如下：

Query OK, 0 rows affected (0.52 sec)

現在，我們正在建立表 'Employee'：

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

我們得到如下所示的輸出：

Query OK, 0 rows affected (6.47 sec)

以下查詢將 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)

您可以使用如下所示的 REVOKE PROXY 語句撤銷代理許可權：

REVOKE PROXY ON sample_user FROM proxy_user;

我們得到以下結果：

Query OK, 0 rows affected (0.33 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';

獲得的結果為：

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)

以下查詢將從使用者那裡撤銷角色：

REVOKE 'TestRole_ReadOnly' FROM 'newuser'@'localhost';

執行上述程式碼後，我們將得到以下輸出：

Query OK, 0 rows affected (1.23 sec)

使用客戶端程式撤銷許可權

我們還可以使用客戶端程式從 MySQL 使用者撤銷許可權。

語法

以下是使用各種程式語言撤銷 MySQL 許可權的語法：

$sql = "REVOKE ALL, GRANT OPTION FROM user_name";
$mysqli->query($sql);

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

String sql = "REVOKE ALL PRIVILEGES, GRANT OPTION FROM USER_NAME";
statement.execute(sql);

sql = f"REVOKE {privileges} ON your_database.* FROM '{username_to_revoke}'@'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 = "REVOKE ALL, GRANT OPTION FROM Sarika";
if($result = $mysqli->query($sql)){
   printf("Revoke privileges executed successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

Revoke 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("**Granted privileges:**");
    console.log(result);
    console.log("--------------------------");
  });
  sql = "REVOKE SELECT ON test_database.MyTable FROM '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("**Grants after revoking:**");
    console.log(result);
  });
});

Connected!
--------------------------
**Granted privileges:**
[
  {
    '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`'
  }
]
--------------------------
**Grants after revoking:**
[
  {
    'Grants for test_user@localhost': 'GRANT USAGE ON *.* TO `test_user`@`localhost`'
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class RevokePriv {
	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 = "REVOKE ALL PRIVILEGES, GRANT OPTION FROM Vivek";
            st.execute(sql);
            System.out.println("You revoked all the privileges form user 'Vivek'");    
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

You revoked all the privileges form user 'Vivek'

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

Privileges revoked from the user 'newUser' successfully.