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.
| Table name | Description |
|---|---|
| User permission table, recording account number, password and global permission information | |
| Record database related permissions | |
| Permissions that users have on a certain table | |
| The user’s permissions on a column of a table | |
| The user’s permissions on stored procedures and stored functions |
% means that a host anywhere is allowed to remotely log in to the MySQL database.
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 'user1'@'%'
IDENTIFIED BY '123456'";
# 执行
$result = mysqli_query($conn, $SQL);
# 查看是否执行成功
if ($result) {
echo "SQL 语句执行成功!\n";
}else {
echo mysqli_error($conn);
}
# 关闭连接
mysqli_close($conn);
?>
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 'user1'@'%'";
# 执行
$result = mysqli_query($conn, $SQL);
# 查看是否执行成功
if ($result) {
echo "SQL 语句执行成功!\n";
}else {
echo mysqli_error($conn);
}
# 关闭连接
mysqli_close($conn);
?>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 'root'@'localhost'
IDENTIFIED BY '123456'";
# 执行
$result = mysqli_query($conn, $SQL);
# 查看是否执行成功
if ($result) {
echo "SQL 语句执行成功!\n";
}else {
echo mysqli_error($conn);
}
# 关闭连接
mysqli_close($conn);
?>GRANT and REVOKE to authorize and revoke authorization. Permissions are specifically divided into 3 categories, data category, structure category, and management Class.
| Structure | Management | |
|---|---|---|
| UPDATE DELETE FILE CREATE | ALTERINDEX DROP CREATE TEMPORARY TABLES SHOW VIEW CREATE ROUTINE ALTER ROUTINE EXECUTE CREATE VIEW EVENT TRIGGER USAGE | GRANTSUPER PROCESS RELOAD SHUTDOWN SHOW DATABASES LOCK TABLES REFERENCES REPUCATION CUENT REPUCATION SLAVE CREATE USER |
