Go language and MySQL database: How to divide data role permissions?

With the development and popularization of the Internet, data security and rights management have become important issues that enterprises and individuals must face. The database is one of the most commonly used data storage and management tools in enterprises, and the division of data role permissions is an important means of database security and permission management. This article will introduce how to use Go language combined with MySQL database to divide data role permissions.

1. What is the division of data role permissions?

The division of data role permissions refers to dividing different users into different roles according to their functions or identities, and assigning specific operation permissions to these roles. For example, system administrators in an enterprise can have full control over the database, while ordinary employees can only perform simple query and modification operations, which requires role division and permission allocation for different users. The division of data role permissions can not only ensure data security, but also improve work efficiency and data usage effects. It is a must-have task in the enterprise.

2. Use Go language to connect to MySQL database

Go language is a simple and efficient programming language. Its standard library has built-in support for MySQL and can be used without using additional dependent libraries. Perform MySQL database operations under the circumstances.

The steps to connect to the MySQL database using Go language are as follows:

1. Install the MySQL driver

In the Go language, you need to install the MySQL driver first. Commonly used drivers include go-sql-driver and mysql-go-driver.

Taking go-sql-driver as an example, you can use the following command to install it:

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

2. Connect to MySQL database

Before connecting to the MySQL database, You need to configure the database connection information first, including database address, port, user name, password, database name, etc. In Go language, you can use the following code to connect to the MySQL database:

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

func main() {
    db, err := sql.Open("mysql", "用户名:密码@tcp(数据库地址:端口号)/数据库名称")
    if err != nil {
        //连接失败处理
        return
    }
    defer db.Close()
}

The above code uses the sql.Open function to connect to the database. This function accepts two parameters. The first parameter is the MySQL driver name ("mysql") , the second parameter is the connection string ("username:password@tcp(database address:port number)/database name"). After the connection configuration is completed, the database connection needs to be closed after the program ends.

3. Execute SQL statements

After the connection is successful, you can use Go language to execute SQL statements to operate the MySQL database. For example, to query data in a MySQL database, you can use the following code:

rows, err := db.Query("SELECT * FROM 表名")
if err != nil {
    //查询失败处理
    return
}
defer rows.Close()
for rows.Next() {
    var id int
    var name string
    var age int
    err := rows.Scan(&id, &name, &age)
    if err != nil {
        //数据读取失败处理
        continue
    }
    //数据处理
}

The above code uses the db.Query function to execute a SQL query statement and return a result set. The Query function accepts one parameter, which is the SQL statement to be executed. The query result set needs to be read one by one, which can be achieved using the rows.Next and rows.Scan functions. The parameters of the Scan function are pointers to each field value in the result set. When reading data, you need to pay attention to the field type and order. After the query is completed, the result set needs to be closed.

3. Implementation of data role permission division

The steps to implement data role permission division using Go language and MySQL database are as follows:

1. Create user role table

First you need to create a user role table to store user and role information. The structure of the user role table is as follows:

CREATE TABLE user_role (
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    PRIMARY KEY (user_id, role_id)
);

The above code creates a table named user_role, which includes two fields: user_id and role_id, representing user ID and role ID. Since a user may have multiple roles, there is a many-to-many relationship in the user_role table, using (user_id, role_id) as the composite primary key.

2. Create a role permission table

Then you need to create a role permission table to store role and permission information. The structure of the role permission table is as follows:

CREATE TABLE role_permission (
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (role_id, permission_id)
);

The above code creates a table named role_permission, including two fields: role_id and permission_id, which represent the role ID and permission ID. Also use (role_id, permission_id) as the composite primary key.

3. Assign user roles and permissions

When a user registers or modifies personal information, the program needs to save the user ID and role ID information to the user_role table, sample code As follows:

func assignRole(user_id, role_id int) error {
    db, err := sql.Open("mysql", "用户名:密码@tcp(数据库地址:端口号)/数据库名称")
    if err != nil {
        return err
    }
    defer db.Close()
    //插入用户角色信息
    _, err = db.Exec("INSERT INTO user_role (user_id, role_id) VALUES (?, ?)", user_id, role_id)
    if err != nil {
        return err
    }
    return nil
}

The above code uses the db.Exec function to execute the SQL insert statement and insert the corresponding relationship between users and roles. Similarly, you can also use the db.Exec function to save the corresponding relationship between roles and permissions into the role_permission table.

4. Verify user permissions

When a user performs an operation, the user's role and permission information need to be verified. For example, a user with the administrator role can delete user information, while an ordinary user can only view user information. In order to realize this function, the role and permission information need to be used in the program to query the database to determine whether the corresponding user has sufficient permissions to operate. The sample code is as follows:

func checkPermission(user_id, permission_id int) (bool, error) {
    db, err := sql.Open("mysql", "用户名:密码@tcp(数据库地址:端口号)/数据库名称")
    if err != nil {
        return false, err
    }
    defer db.Close()
    //查询用户角色
    rows, err := db.Query("SELECT role_id FROM user_role WHERE user_id=?", user_id)
    if err != nil {
        return false, err
    }
    defer rows.Close()
    role_ids := []int{}
    for rows.Next() {
        var role_id int
        err := rows.Scan(&role_id)
        if err != nil {
            return false, err
        }
        role_ids = append(role_ids, role_id)
    }
    //查询角色权限
    for _, role_id := range role_ids {
        rows, err := db.Query("SELECT COUNT(*) FROM role_permission WHERE role_id=? AND permission_id=?", role_id, permission_id)
        if err != nil {
            return false, err
        }
        defer rows.Close()
        count := 0
        for rows.Next() {
            err := rows.Scan(&count)
            if err != nil {
                return false, err
            }
        }
        //判断是否有权限
        if count > 0 {
            return true, nil
        }
    }
    return false, nil
}

The above code queries the user_role table to obtain the role information to which the user belongs, then queries the role_permission table to obtain the permission information owned by the role, and finally determines whether the user has the corresponding permissions to operate. If the user has permission, it returns true; if it does not, it returns false.

4. Summary

The combination of Go language and MySQL database can realize the division of data role permissions, which can make data safer and management more effective. In actual development, user roles and permission information need to be flexibly set according to different business scenarios and program requirements in order to achieve the best effects of permission control and data management.

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