Data control methods of MySQL and PHP

Data Control Language

Data Control Language (Data Control Language) is a statement used to set or change database user or role permissions.

Level	Description
Global level	Applies to all databases in a given server. These permissions are stored in mysql. The
database hierarchy	in the user table applies to all targets in a given database. These permissions are stored in the mysql.db and mysql.host tables
Table level	Applies to all columns in a given table. These permissions are stored in the
columns of the mysql.tables_priv table Hierarchy	Use for a single column in a given table. These permissions are stored in the mysql.columns_priv table
Subroutine Hierarchy	CREATE ROUTINE , ALTER ROUTINE, EXECUTE and GRANT permissions apply to stored subroutines. These permissions can be granted at the global level and database level

MySQL Permission System

MySQL's permission information is mainly stored in the following tables. When a user connects to the database, MySQL will verify the user's permissions based on these tables.

##userUser permission table, recording account number, password and global permission informationdbRecord database related permissionstable_privPermissions that users have on a certain tablecolumn_privThe user’s permissions on a column of a tableprocs_privThe user’s permissions on stored procedures and stored functions

Table name	Description

User Management

In MySQL, use CREATE USER to create a user. The user does not have any permissions after creation.

View all users :

Create user

MySQL user account consists of two parts: username and hostname, that is, username@hostname, the hostname can be IP Or the machine name, the host name

% means that a host anywhere is allowed to remotely log in to the MySQL database.

Format:

CREATE USER 'Username' [@ 'Hostname'][IDENTIFIED BY 'Password'];

Example:

<?php

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

if ($conn) {
    echo "数据库连接成功\n";
} else {
    echo mysqli_connect_error();
}

# SQL语句
$SQL = "CREATE USER &#39;user1&#39;@&#39;%&#39;
        IDENTIFIED BY &#39;123456&#39;";

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

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

# 关闭连接
mysqli_close($conn);

?>

Effect:

Delete user

Format:

DROP USER 'Username‘[@'Hostname']

Example:

<?php

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

if ($conn) {
    echo "数据库连接成功\n";
} else {
    echo mysqli_connect_error();
}

# SQL语句
$SQL = "DROP USER &#39;user1&#39;@&#39;%&#39;";

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

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

# 关闭连接
mysqli_close($conn);

?>

Modify Password

Format:

ALTER USER 'Username'@'Host Name' IDENTIFIED BY 'New Password';

Example:

<?php

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

if ($conn) {
    echo "数据库连接成功\n";
} else {
    echo mysqli_connect_error();
}

# SQL语句
$SQL = "ALTER USER &#39;root&#39;@&#39;localhost&#39; 
        IDENTIFIED BY &#39;123456&#39;";

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

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

# 关闭连接
mysqli_close($conn);

?>

Permission Management

MySQL uses

GRANT and REVOKE to authorize and revoke authorization. Permissions are specifically divided into 3 categories, data category, structure category, and management Class.

DataStructureManagement## SELECTINSERTALTERGRANT

查看权限

格式:

SHOW GRANTS FOR '用户名'[@'主机名']

例子:

<?php

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

if ($conn) {
    echo "数据库连接成功\n";
} else {
    echo mysqli_connect_error();
}

# SQL语句
$SQL = "SHOW GRANTS FOR &#39;root&#39;@&#39;localhost&#39;";

# 执行
$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 &#39;user1&#39;@&#39;%&#39;";

# 执行
$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 &#39;user1&#39;@&#39;%&#39;";

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

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

# 关闭连接
mysqli_close($conn);

?>

刷新权限

格式:

FLUSH PRIVILEGES

注意事项

禁止 root 远程登录.

禁止 root 远程登录的原因：

• root 是 MySQL 数据库的超级管理员. 几乎拥有所有权限, 一旦泄露后果非常严重

• root 是 MySQL 数据库的默认用户. 如果不禁止远程登录, 则某些人可以针对 root 用户暴力破解密码

UPDATE DELETE FILE CREATE	INDEX DROP CREATE TEMPORARY TABLES SHOW VIEW CREATE ROUTINE ALTER ROUTINE EXECUTE CREATE VIEW EVENT TRIGGER USAGE	SUPER PROCESS RELOAD SHUTDOWN SHOW DATABASES LOCK TABLES REFERENCES REPUCATION CUENT REPUCATION SLAVE CREATE USER

The above is the detailed content of Data control methods of MySQL and PHP. For more information, please follow other related articles on the PHP Chinese website!