How to design an optimized MySQL table structure to implement data permissions function?

How to design an optimized MySQL table structure to implement the data permission function?

Overview:
In many applications, data permissions are an important functional requirement. Data permissions are used to restrict users' access to and operations on data to ensure data security and compliance. In MySQL, we can achieve this function through appropriate table structure design and data permission control. This article will introduce how to design an optimized MySQL table structure to implement data permissions, and give specific code examples.

Table structure design:
When designing the MySQL table structure, you need to consider the following key factors:

1. User table (user_table): This table is used in the storage system User information, including user ID, user name, password, etc.
   CREATE TABLE user_table (
   user_id INT PRIMARY KEY,
   username VARCHAR(50) NOT NULL,
   password VARCHAR(50) NOT NULL
   );
2. Role table (role_table): This table is used to store role information in the system, including role ID and role name.
   CREATE TABLE role_table (
   role_id INT PRIMARY KEY,
   role_name VARCHAR(50) NOT NULL
   );
3. Resource table (resource_table): This table is used to store resource information in the system, including resource ID and resource name.
   CREATE TABLE resource_table (
   resource_id INT PRIMARY KEY,
   resource_name VARCHAR(50) NOT NULL
   );
4. Permission table (permission_table): This table is used to store the relationship between roles and resources, that is, which resource permissions the role has.
   CREATE TABLE permission_table (
   role_id INT,
   resource_id INT,
   PRIMARY KEY (role_id , resource_id),
   FOREIGN KEY (role_id) REFERENCES role_table(role_id),
   FOREIGN KEY (resource_id) REFERENCES resource_table(resource_id)
   );

Code example:
The following is a simple example code , used to demonstrate how to associate users, roles and resources, and implement data permission control through permission tables.

1. Insert user, role and resource data:
   INSERT INTO user_table (user_id, username, password )
   VALUES (1, '张三', '123456');

INSERT INTO role_table (role_id, role_name)
VALUES (1, 'Administrator');

INSERT INTO resource_table (resource_id, resource_name )
VALUES (1, 'File 1'), (2, 'File 2'), (3, 'File 3');

2. Assign roles to users:
   INSERT INTO permission_table (role_id, resource_id)
   VALUES (1, 1), (1, 2), (1, 3);
3. Query the user's permissions on resources:
   SELECT r.role_name, res.resource_name
   FROM user_table u
   JOIN permission_table p ON u.role_id = p.role_id
   JOIN role_table r ON p. role_id = r.role_id
   JOIN resource_table res ON p.resource_id = res.resource_id
   WHERE u.username = 'Zhang San';

Conclusion:
Through the above design, We can implement role-based data permission control. By reasonably associating users, roles, and resources, and using permission tables for permission assignment, we can flexibly control users' access to and operations on data. The above is just a simple example. The actual situation may be more complicated, but the overall idea is the same. I hope this article has provided some help for you to design an optimized MySQL table structure to implement the data permission function.

The above is the detailed content of How to design an optimized MySQL table structure to implement data permissions function?. For more information, please follow other related articles on the PHP Chinese website!