MySQL与PHP的基础与应用专题之数据控制(2)-木庄网络博客

删除用户

格式:

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

?>