概述

從今天開始, 小白我將帶領(lǐng)大家一起來補(bǔ)充一下 數(shù)據(jù)庫的知識(shí).

數(shù)據(jù)控制語言

數(shù)據(jù)控制語言 (Data Control Language) 是用來設(shè)置或更改數(shù)據(jù)庫用戶或角色權(quán)限的語句.

MySQL 權(quán)限體系

MySQL 的權(quán)限信息主要存儲(chǔ)在以下幾張表中, 當(dāng)用戶連接數(shù)據(jù)庫時(shí), MySQL 會(huì)根據(jù)這些表對(duì)用戶進(jìn)行權(quán)限驗(yàn)證.

用戶管理

在 MySQL 中, 使用 CREATE USER 來創(chuàng)建用戶, 用戶創(chuàng)建后沒有任何權(quán)限.

查看所有用戶:

創(chuàng)建用戶

MySQL 的用戶賬號(hào)由兩部分組成: 用戶名和主機(jī)名, 即用戶名@主機(jī)名,主機(jī)名可以是 IP 或機(jī)器名稱, 主機(jī)名為%表示允許任何地方的主機(jī)遠(yuǎn)程登錄 MySQL 數(shù)據(jù)庫.

格式:

CREATE USER '用戶名' [@'主機(jī)名'][IDENTIFIED BY '密碼'];

例子:

<?php

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

if ($conn) {
    echo "數(shù)據(jù)庫連接成功\n";
} else {
    echo mysqli_connect_error();
}

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

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

# 查看是否執(zhí)行成功
if ($result) {
    echo "SQL 語句執(zhí)行成功!\n";
}else {
    echo mysqli_error($conn);
}

# 關(guān)閉連接
mysqli_close($conn);

?>

效果:

刪除用戶

格式:

DROP USER '用戶名‘[@'主機(jī)名']

例子:

<?php

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

if ($conn) {
    echo "數(shù)據(jù)庫連接成功\n";
} else {
    echo mysqli_connect_error();
}

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

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

# 查看是否執(zhí)行成功
if ($result) {
    echo "SQL 語句執(zhí)行成功!\n";
}else {
    echo mysqli_error($conn);
}

# 關(guān)閉連接
mysqli_close($conn);

?>

修改密碼

格式:

ALTER USER '用戶名'@'主機(jī)名' IDENTIFIED BY '新密碼';

例子:

<?php

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

if ($conn) {
    echo "數(shù)據(jù)庫連接成功\n";
} else {
    echo mysqli_connect_error();
}

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

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

# 查看是否執(zhí)行成功
if ($result) {
    echo "SQL 語句執(zhí)行成功!\n";
}else {
    echo mysqli_error($conn);
}

# 關(guān)閉連接
mysqli_close($conn);

?>

權(quán)限管理

MySQL 使用GRANT和REVOKE進(jìn)行授權(quán), 撤銷授權(quán). 權(quán)限具體分為 3 類, 數(shù)據(jù)類, 結(jié)構(gòu)類, 和管理類.

查看權(quán)限

格式:

SHOW GRANTS FOR '用戶名'[@'主機(jī)名']

例子:

<?php

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

if ($conn) {
    echo "數(shù)據(jù)庫連接成功\n";
} else {
    echo mysqli_connect_error();
}

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

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

# 查看是否執(zhí)行成功
if ($result) {
    echo "SQL 語句執(zhí)行成功!\n";
}else {
    echo mysqli_error($conn);
}

# 調(diào)試輸出
while ($line = mysqli_fetch_assoc($result)) {
    print_r($line);
}

# 關(guān)閉連接
mysqli_close($conn);

?>

輸出結(jié)果:

數(shù)據(jù)庫鏈接成功
SQL 語句執(zhí)行成功!
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
)

用戶授權(quán)

格式:

GRANT ALL PRIVILEGES ON 數(shù)據(jù)庫名.表名 TO '用戶名'[@'主機(jī)名']

例子:

<?php

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

if ($conn) {
    echo "數(shù)據(jù)庫連接成功\n";
} else {
    echo mysqli_connect_error();
}

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

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

# 查看是否執(zhí)行成功
if ($result) {
    echo "SQL 語句執(zhí)行成功!\n";
}else {
    echo mysqli_error($conn);
}

# 關(guān)閉連接
mysqli_close($conn);

?>

撤銷授權(quán)

REVOKE ALL PRIVILEGES ON 數(shù)據(jù)庫名.表名 from '用戶名'[@'主機(jī)名']

例子:

<?php

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

if ($conn) {
    echo "數(shù)據(jù)庫連接成功\n";
} else {
    echo mysqli_connect_error();
}

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

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

# 查看是否執(zhí)行成功
if ($result) {
    echo "SQL 語句執(zhí)行成功!\n";
}else {
    echo mysqli_error($conn);
}

# 關(guān)閉連接
mysqli_close($conn);

?>

刷新權(quán)限

格式:

FLUSH PRIVILEGES

注意事項(xiàng)

禁止 root 遠(yuǎn)程登錄.

禁止 root 遠(yuǎn)程登錄的原因：

• root 是 MySQL 數(shù)據(jù)庫的超級(jí)管理員. 幾乎擁有所有權(quán)限, 一旦泄露后果非常嚴(yán)重
• root 是 MySQL 數(shù)據(jù)庫的默認(rèn)用戶. 如果不禁止遠(yuǎn)程登錄, 則某些人可以針對(duì) root 用戶暴力破解密碼

到此這篇關(guān)于MySQL與PHP的基礎(chǔ)與應(yīng)用專題之?dāng)?shù)據(jù)控制的文章就介紹到這了,更多相關(guān)MySQL 數(shù)據(jù)控制內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論