How to design a secure MySQL table structure to implement permission control functions?

How to design a secure MySQL table structure to implement permission control functions?

With the development of the Internet, system security has attracted increasing attention. In many applications, permission control is an important means of protecting sensitive data and functionality. In the MySQL database, we can implement permission control functions by properly designing the table structure to ensure that only authorized users can access specific data.

The following is a basic MySQL table structure design for implementing permission control functions:

Table name: users
Fields: id, username, password, role_id

Table name: roles
Fields: id, role_name

Table name: permissions
Fields: id, permission_name

Table name: role_permissions
Fields: role_id, permission_id

Table name: user_roles
Fields: user_id, role_id

Here, we use association tables to realize the relationship between roles and permissions, and users and roles. Here is some sample code to create the table and populate the sample data:

--Create users table
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(255) NOT NULL,
role_id INT
);

-- Create roles table
CREATE TABLE roles (
id INT PRIMARY KEY AUTO_INCREMENT ,
role_name VARCHAR(30) NOT NULL
);

-- Create permissions table
CREATE TABLE permissions (
id INT PRIMARY KEY AUTO_INCREMENT,
permission_name VARCHAR(30 ) NOT NULL
);

--Create role_permissions table
CREATE TABLE role_permissions (
role_id INT,
permission_id INT,
PRIMARY KEY (role_id, permission_id)
);

--Create user_roles table
CREATE TABLE user_roles (
user_id INT,
role_id INT,
PRIMARY KEY (user_id, role_id)
);

-- Fill in sample data
INSERT INTO roles (role_name) VALUES ('Administrator'), ('Editor'), ('Reader');
INSERT INTO permissions (permission_name) VALUES ( 'Create article'), ('Edit article'), ('Delete article');
INSERT INTO role_permissions (role_id, permission_id) VALUES (1, 1), (1, 2), (1, 3), (2, 2), (3, 3);
INSERT INTO users (username, password, role_id) VALUES ('admin', 'password', 1), ('editor', 'password', 2), ('reader', 'password', 3);
INSERT INTO user_roles (user_id, role_id) VALUES (1, 1), (2, 2), (3, 3);

Through the above With table structure and sample data, we can implement the following permission control functions:

1. Administrators have the permission to create, edit and delete articles;
2. Editors have the permission to edit articles;
3. Readers do not have any special permissions.

In the application, after the login verification is successful, the permissions the user has can be queried based on the user's role ID, as shown below:

SELECT p.permission_name
FROM users AS u
INNER JOIN user_roles AS ur ON u.id = ur.user_id
INNER JOIN roles AS r ON ur.role_id = r.id
INNER JOIN role_permissions AS rp ON r.id = rp.role_id
INNER JOIN permissions AS p ON rp.permission_id = p.id
WHERE u.username = 'admin';

Through the above query, we can get the permissions of the current user, so as to Permissions control user access to data and functionality.

It should be noted that when designing the table structure, a reasonable design should be carried out based on actual needs. In addition to the basic table structure in the above example, you can add more fields and table relationships based on specific business needs.

In short, by rationally designing the MySQL table structure to realize the permission control function, the security of the system can be effectively protected. Hope the above content is helpful to you.

The above is the detailed content of How to design a secure MySQL table structure to implement permission control functions?. For more information, please follow other related articles on the PHP Chinese website!