How to use Go language to audit data permissions of MySQL database

With the continuous development of the Internet, the use of databases is becoming more and more widespread. In enterprises, the management of data permissions has also become an issue that cannot be ignored. Therefore, how to review and manage data permissions has become a problem that enterprises must face. This article will introduce how to use Go language to audit data permissions of MySQL database.

1. Introduction to MySQL database permissions

In the MySQL database, user permissions can be divided into four types: Global (global), Database (database), Table (data table) and Column ( column), respectively representing access rights to the entire MySQL server, a certain database, a certain data table and a certain column.

Global permissions are the highest permissions, granting users permission to operate on the entire MySQL server; Database permissions indicate that users can operate on a certain database; Table permissions indicate that users can operate on a certain data table Permission to operate on a certain column; Column permission only allows operations on a certain column.

2. Connection between Go language and MySQL database

Using Go language to operate MySQL database requires the use of third-party libraries provided by Go language, such as go-sql-driver/mysql. The installation method is as follows:

go get -u github.com/go-sql-driver/mysql

Then, you need to use the following code to connect to the MySQL database in Go language:

import(
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)

func main(){
    db, err := sql.Open("mysql", "root:password@tcp(127.0.0.1:3306)/mydb")
    if err != nil {
        log.Fatal(err.Error())
    }
    defer db.Close()
}

Among them, the first parameter "mysql" means using the MySQL database, and the second parameter In "root:password@tcp(127.0.0.1:3306)/mydb", root represents the user name, password represents the password, 127.0.0.1 represents the IP address of the database, 3306 represents the port number of the MySQL database, and mydb represents the name of the database to be connected. . Next, use the defer statement to close the database connection.

3. Go language to implement MySQL database permission audit

1. Query user permissions

Use the following SQL statement to query the permissions owned by the user:

SELECT * FROM mysql.user WHERE User = 'username' AND Host = 'host';

Among them, username represents the user name to be queried, and host represents the host address.

The code for querying user permissions in Go language is as follows:

func checkUserPermission(db *sql.DB, username string, host string) bool {
    query := fmt.Sprintf("SELECT * FROM mysql.user WHERE User = '%s' AND Host = '%s'", username, host)
    rows, err := db.Query(query)
    if err != nil {
        log.Fatal(err.Error)
    }
    defer rows.Close()

    var user string
    for rows.Next() {
        err := rows.Scan(&user)
        if err != nil {
            log.Fatal(err.Error)
        }
        return true
    }
    return false
}

Among them, db represents the MySQL database to be connected, username represents the user name to be queried, and host represents the host address.

First, use the fmt.Sprintf() method to construct the SQL statement. Then, use the db.Query() method to query the database and use the rows.Close() method to close the result set.

Next, in the loop, use the rows.Scan() method to scan each row of the result set. If the user's record is found, true is returned; otherwise, false is returned.

2. Query database permissions

Use the following SQL statement to query the database permissions owned by the user:

SHOW GRANTS FOR 'username'@'host';

Query database permissions in Go language The code is as follows:

func checkDatabasePermission(db *sql.DB, username string, host string, database string) bool {
    query := fmt.Sprintf("SHOW GRANTS FOR '%s'@'%s'", username, host)
    rows, err := db.Query(query)
    if err != nil {
        log.Fatal(err.Error)
    }
    defer rows.Close()

    for rows.Next() {
        var grants string
        err := rows.Scan(&grants)
        if err != nil {
            log.Fatal(err.Error)
        }

        if strings.Contains(grants, fmt.Sprintf("`%s`.*", database)) {
            return true
        }
    }
    return false
}

Among them, db represents the MySQL database to be connected, username represents the user name to be queried, host represents the host address, and database represents the name of the database to be queried.

First, use the fmt.Sprintf() method to construct the SQL statement. Then, use the db.Query() method to query the database and use the rows.Close() method to close the result set.

Next, in the loop, use the rows.Scan() method to scan each row of the result set. If the found result contains the name of the database to be queried, true is returned; otherwise, false is returned.

3. Query data table permissions

Use the following SQL statement to query the data table permissions owned by the user:

SHOW GRANTS FOR 'username'@'host' ON `database`.`table`;

Query data in Go language The code for table permissions is as follows:

func checkTablePermission(db *sql.DB, username string, host string, database string, table string) bool {
    query := fmt.Sprintf("SHOW GRANTS FOR '%s'@'%s' ON `%s`.`%s`", username, host, database, table)
    rows, err := db.Query(query)
    if err != nil {
        log.Fatal(err.Error)
    }
    defer rows.Close()

    for rows.Next() {
        var grants string
        err := rows.Scan(&grants)
        if err != nil {
            log.Fatal(err.Error)
        }

        if strings.Contains(grants, "ALL PRIVILEGES") || strings.Contains(grants, "SELECT") {
            return true
        }
    }
    return false
}

Among them, db represents the MySQL database to be connected, username represents the user name to be queried, host represents the host address, database represents the name of the database to be queried, and table represents the data to be queried. Table name.

First, use the fmt.Sprintf() method to construct the SQL statement. Then, use the db.Query() method to query the database and use the rows.Close() method to close the result set.

Next, in the loop, use the rows.Scan() method to scan each row of the result set. If the found result contains ALL PRIVILEGES or SELECT, return true; otherwise, return false.

4. Summary

This article introduces how to use Go language to conduct data permission audit of MySQL database. By writing relevant SQL query statements and using the third-party library provided by the Go language to connect to the MySQL database, the review and management of user permissions, database permissions, and data table permissions are realized. Using Go language to review data permissions on MySQL databases is convenient and efficient, and can help enterprises better manage and review database permissions.

The above is the detailed content of How to use Go language to audit data permissions of MySQL database. For more information, please follow other related articles on the PHP Chinese website!