How can database tables be structured in PHP to manage user roles and permissions effectively for an admin user?

To manage user roles and permissions effectively for an admin user in PHP, you can create database tables for users, roles, and permissions. Each user can be assigned a role, and each role can have specific permissions associated with it. The admin user can then be assigned a role with full permissions to manage other users and their roles. 

// Database tables structure
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(255) NOT NULL,
    role_id INT NOT NULL
);

CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

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

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

// Assigning admin role full permissions
INSERT INTO roles (name) VALUES ('admin');
INSERT INTO permissions (name) VALUES ('manage_users', 'manage_roles', 'manage_permissions');

INSERT INTO role_permissions (role_id, permission_id) VALUES (1, 1), (1, 2), (1, 3);

// Assigning admin role to admin user
INSERT INTO users (username, password, role_id) VALUES ('admin', 'adminpass', 1);

Keywords

database tables user roles permissions admin user PHP structure

Related Questions