How to use MySQL to create permission tables to implement permission management functions

How to use MySQL to create permission tables to implement permission management functions

Overview:
Permission management is an indispensable part of modern application development. Through permission management, we can restrict users' access to and operations on system resources. This article will introduce how to use MySQL to create a permission table to implement permission management functions, and provide corresponding code examples.

Step 1: Create user table
First, we need to create a user table to store user information of the system. You can create a user table named "users" through the following code example:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(255) NOT NULL,
  password VARCHAR(255) NOT NULL,
  enabled BOOLEAN NOT NULL
);

The user table structure includes user ID (id), user name (username), password (password) and whether it is enabled (enabled), etc. field.

Step 2: Create a permission table
Next, we need to create a permission table to store the access permissions for various resources in the system. You can create a permission table named "permissions" through the following code example:

CREATE TABLE permissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description VARCHAR(255) NOT NULL
);

The permission table structure includes fields such as permission ID (id), permission name (name), and permission description (description).

Step 3: Create a user permissions table
In order to associate users with permissions, we also need to create a user permissions table. You can create a user permission table named "user_permissions" through the following code example:

CREATE TABLE user_permissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  permission_id INT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (permission_id) REFERENCES permissions(id)
);

The user permission table structure includes fields such as user permission ID (id), user ID (user_id) and permission ID (permission_id), And related to the user table and permission table through foreign keys.

Step 4: Insert initial data
For the convenience of demonstration, we can insert some initial data into the user table and permission table. The following is a code example for inserting initial data:

INSERT INTO users (username, password, enabled)
VALUES ('admin', 'password', true);

INSERT INTO permissions (name, description)
VALUES ('create', 'Create resources'),
       ('read', 'Read resources'),
       ('update', 'Update resources'),
       ('delete', 'Delete resources');

INSERT INTO user_permissions (user_id, permission_id)
VALUES (1, 1),
       (1, 2),
       (1, 3),
       (1, 4);

The above example code inserts a user named "admin" into the user table, sets the password to "password", and enables it. At the same time, four permissions are inserted into the permission table, namely the permissions to create resources, read resources, update resources and delete resources. Finally, associate the user "admin" with these four permissions.

Step 5: Query user permissions
Now, we can query the permission list of the specified user by querying the user permissions table. The following is a code example for querying the user permission list:

SELECT p.name
FROM users u
JOIN user_permissions up ON u.id = up.user_id
JOIN permissions p ON up.permission_id = p.id
WHERE u.username = 'admin';

This sample code queries the permission list of the user "admin" by connecting the user table, user permission table and permission table. The result will return the permission names that the user has.

Conclusion:
Through the above steps, we successfully created the user table, permission table and user permission table using MySQL, and realized the association between users and permissions. By querying the user permission list, we can easily manage user access permissions. In actual applications, the permission management function of the system can be continued to be improved as needed.

In short, it is a simple and effective method to implement permission management functions by creating permission tables. This method can easily manage user access rights and can be associated with other system components (such as user tables) to improve system security and maintainability. I hope the introduction in this article will be helpful to readers in terms of permission management.

The above is the detailed content of How to use MySQL to create permission tables to implement permission management functions. For more information, please follow other related articles on the PHP Chinese website!