MySQL與PHP的基礎與應用專題之資料控制

2022-02-22 16:01:19

概述

從今天開始, 小白我將帶領大家一起來補充一下資料庫的知識.

資料控制語言

資料控制語言 (Data Control Language) 是用來設定或更改資料庫使用者或角色許可權的語句.

層級	描述
全域性層級	適用於一個給定伺服器中的所有資料庫. 這些許可權儲存在 mysql.user 表中
資料庫層級	適用於一個給定資料庫中的所有目標. 這些許可權儲存在 mysql.db 和 mysql.host 表中
表層級	適用於一個給定表中所有的列. 這些許可權儲存在 mysql.tables_priv 表中
列層級	使用於一個給定表中的單一列. 這些許可權儲存在 mysql.columns_priv 表中
子程式層級	CREATE ROUTINE, ALTER ROUTINE, EXECUTE 和 GRANT 許可權適用於已儲存的子程式. 這些許可權可以被授予為全域性層級和資料庫層級

MySQL 許可權體系

MySQL 的許可權資訊主要儲存在以下幾張表中, 當用戶連線資料庫時, MySQL 會根據這些表對使用者進行許可權驗證.

表名	描述
user	使用者許可權表, 記錄賬號, 密碼及全域性性許可權資訊
db	記錄資料庫相關許可權
table_priv	使用者對某個表擁有的許可權
column_priv	使用者對某表某個列所擁有的許可權
procs_priv	使用者對儲存過程及儲存函數的操作許可權

使用者管理

在 MySQL 中, 使用 CREATE USER 來建立使用者, 使用者建立後沒有任何許可權.

檢視所有使用者:

建立使用者

MySQL 的使用者賬號由兩部分組成: 使用者名稱和主機名, 即使用者名稱@主機名,主機名可以是 IP 或機器名稱, 主機名為%表示允許任何地方的主機遠端登入 MySQL 資料庫.

格式:

CREATE USER '使用者名稱' [@'主機名'][IDENTIFIED BY '密碼'];

例子:

<?php

$conn = mysqli_connect("localhost", "root","admin","mysql");

if ($conn) {
    echo "資料庫連線成功n";
} else {
    echo mysqli_connect_error();
}

# SQL語句
$SQL = "CREATE USER 'user1'@'%'
        IDENTIFIED BY '123456'";

# 執行
$result = mysqli_query($conn, $SQL);

# 檢視是否執行成功
if ($result) {
    echo "SQL 語句執行成功!n";
}else {
    echo mysqli_error($conn);
}

# 關閉連線
mysqli_close($conn);

?>

效果:

刪除使用者

格式:

DROP USER '使用者名稱‘[@'主機名']

例子:

<?php

$conn = mysqli_connect("localhost", "root","admin","mysql");

if ($conn) {
    echo "資料庫連線成功n";
} else {
    echo mysqli_connect_error();
}

# SQL語句
$SQL = "DROP USER 'user1'@'%'";

# 執行
$result = mysqli_query($conn, $SQL);

# 檢視是否執行成功
if ($result) {
    echo "SQL 語句執行成功!n";
}else {
    echo mysqli_error($conn);
}

# 關閉連線
mysqli_close($conn);

?>

修改密碼

格式:

ALTER USER '使用者名稱'@'主機名' IDENTIFIED BY '新密碼';

例子:

<?php

$conn = mysqli_connect("localhost", "root","admin","mysql");

if ($conn) {
    echo "資料庫連線成功n";
} else {
    echo mysqli_connect_error();
}

# SQL語句
$SQL = "ALTER USER 'root'@'localhost' 
        IDENTIFIED BY '123456'";

# 執行
$result = mysqli_query($conn, $SQL);

# 檢視是否執行成功
if ($result) {
    echo "SQL 語句執行成功!n";
}else {
    echo mysqli_error($conn);
}

# 關閉連線
mysqli_close($conn);

?>

許可權管理

MySQL 使用GRANT和REVOKE進行授權, 復原授權. 許可權具體分為 3 類, 資料類, 結構類, 和管理類.

資料	結構	管理
SELECT INSERT UPDATE DELETE FILE	CREATE ALTER INDEX DROP CREATE TEMPORARY TABLES SHOW VIEW CREATE ROUTINE ALTER ROUTINE EXECUTE CREATE VIEW EVENT TRIGGER	USAGE GRANT SUPER PROCESS RELOAD SHUTDOWN SHOW DATABASES LOCK TABLES REFERENCES REPUCATION CUENT REPUCATION SLAVE CREATE USER

檢視許可權

格式:

SHOW GRANTS FOR '使用者名稱'[@'主機名']

例子:

<?php

$conn = mysqli_connect("localhost", "root","admin");

if ($conn) {
    echo "資料庫連線成功n";
} else {
    echo mysqli_connect_error();
}

# SQL語句
$SQL = "SHOW GRANTS FOR 'root'@'localhost'";

# 執行
$result = mysqli_query($conn, $SQL);

# 檢視是否執行成功
if ($result) {
    echo "SQL 語句執行成功!n";
}else {
    echo mysqli_error($conn);
}

# 偵錯輸出
while ($line = mysqli_fetch_assoc($result)) {
    print_r($line);
}

# 關閉連線
mysqli_close($conn);

?>

輸出結果:

資料庫連結成功
SQL 語句執行成功!
Array
(
[Grants for root@localhost] => GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
)
Array
(
[Grants for root@localhost] => GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
)
Array
(
[Grants for root@localhost] => GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION
)

使用者授權

格式:

GRANT ALL PRIVILEGES ON 資料庫名.表名 TO '使用者名稱'[@'主機名']

例子:

<?php

$conn = mysqli_connect("localhost", "root","admin");

if ($conn) {
    echo "資料庫連線成功n";
} else {
    echo mysqli_connect_error();
}

# SQL語句
$SQL = "GRANT ALL PRIVILEGES ON study.table1 TO 'user1'@'%'";

# 執行
$result = mysqli_query($conn, $SQL);

# 檢視是否執行成功
if ($result) {
    echo "SQL 語句執行成功!n";
}else {
    echo mysqli_error($conn);
}

# 關閉連線
mysqli_close($conn);

?>

復原授權

REVOKE ALL PRIVILEGES ON 資料庫名.表名 from '使用者名稱'[@'主機名']

例子:

<?php

$conn = mysqli_connect("localhost", "root","admin");

if ($conn) {
    echo "資料庫連線成功n";
} else {
    echo mysqli_connect_error();
}

# SQL語句
$SQL = "REVOKE ALL PRIVILEGES ON study.table1 FROM 'user1'@'%'";

# 執行
$result = mysqli_query($conn, $SQL);

# 檢視是否執行成功
if ($result) {
    echo "SQL 語句執行成功!n";
}else {
    echo mysqli_error($conn);
}

# 關閉連線
mysqli_close($conn);

?>