Hi guys,
Here’s the SQL that I referred to in the preceding email. Hopefully
it makes the whole setup seem more obvious.
Best,
Dave
CREATE TABLE user_roles (
id int(10) unsigned NOT NULL auto_increment,
name char(20) default NULL,
master_role_id int(10) unsigned default NULL,
level int(10) unsigned default NULL,
PRIMARY KEY (id),
KEY idx_level_masterRoleId (level, master_role_id),
KEY idx_masterRoleId (master_role_id),
CONSTRAINT userroles__master_role_id FOREIGN KEY (master_role_id)
REFERENCES user_roles (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into user_roles (id, name, master_role_id, level) values
(1, “Default Permission”, NULL, 10),
(2, “Anonymous User”, 1, 20),
(3, “User”, 1, 30),
(4, “Guest Author”, 1, 40),
(5, “Author 1”, 4, 50),
(6, “Author 2”, 4, 60),
(7, “Author 3”, 4, 70),
(8, “Editor 1”, 4, 80),
(9, “Editor 2”, 9, 90),
(10, “Editor 3”, 10, 100),
(11, “Sidebar Admin”, 1, 110),
(12, “Admin 1”, 10, 120),
(13, “Admin 2”, 12, 130),
(14, “Admin 3”, 12, 140),
(15, “Super User”, NULL, 1000);
CREATE TABLE permission_types (
id int(10) unsigned NOT NULL auto_increment,
name char(20) not null,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into permission_types (id, name) values
(10, “Read”),
(20, “Edit”),
(30, “Create”),
(40, “Delete”),
(50, “Configure”);
CREATE TABLE permission_zones (
id int(10) unsigned NOT NULL auto_increment,
name char(100) not null,
master_zone_id int(10) unsigned default NULL,
PRIMARY KEY (id),
KEY idx_masterZoneId (master_zone_id),
CONSTRAINT userroles__master_zone_id FOREIGN KEY (master_zone_id)
REFERENCES permission_zones (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into permission_zones (id, name, master_zone_id) values
(1000, “Default Content”, NULL),
(1001, “Comments”, 1000),
(1002, “Post Drafts”, 1000),
(1003, “Published Posts”, 1000),
(2000, “Default Blog settings”, NULL),
(2001, “Blog users”, 2000),
(2002, “Blog theme settings”, 2000),
(3000, “Default Sidebars”, NULL),
(3001, “Blog Roll Sidebar”, 3000);
CREATE TABLE permission_scopes (
id int(10) unsigned NOT NULL auto_increment,
name char(100) not null,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into permission_scopes (id, name) values
(1, “own”),
(2, “own & those below”),
(3, “own & all others”);
CREATE TABLE userroles_permissions (
id int(10) unsigned NOT NULL auto_increment,
user_role_id int(10) unsigned NOT NULL default 0,
permission_zone_id int(10) unsigned NOT NULL default 0,
permission_type_id int(10) unsigned NOT NULL default 0,
permission_scope_id int(10) unsigned NOT NULL default 0,
PRIMARY KEY (ID),
KEY idx_userRoleId_pZoneId_pTypeId_pScopeId (user_role_id,
permission_zone_id, permission_type_id, permission_scope_id),
KEY idx_pZoneId_pTypeId_pScopeId (permission_zone_id,
permission_type_id, permission_scope_id),
KEY idx_pTypeId_pScopeId (permission_type_id, permission_scope_id),
KEY idx_pScopeId (permission_type_id),
CONSTRAINT userroles_permissions__user_role_id FOREIGN KEY
(user_role_id) REFERENCES user_roles (id),
CONSTRAINT userroles_permissions__pzone_id FOREIGN KEY
(permission_zone_id) REFERENCES permission_zones (id),
CONSTRAINT userroles_permissions__ptype_id FOREIGN KEY
(permission_zone_id) REFERENCES permission_types (id),
CONSTRAINT userroles_permissions__pscope_id FOREIGN KEY
(permission_scope_id) REFERENCES permission_scopes (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into userroles_permissions
(user_role_id, permission_zone_id, permission_type_id,
permission_zone_id) values
– By default, ALL users can
(1, 1001, 10, 3), – view all comments
(1, 1001, 30, 1), – create comments
(1, 1003, 10, 3), – read all posts
(1, 2000, 10, 3), – read all sidebars
– guest authors can
(4, 1002, 10, 1), – read own drafts
(4, 1002, 20, 1), – edit own drafts
(4, 1002, 30, 1), – create own drafts
(4, 1002, 40, 1), – delete own drafts
– authors can
(5, 1001, 20, 2), – edit comments on own threads and those beneath
(5, 1002, 10, 3), – read all drafts
(5, 1003, 30, 1), – create their own published posts
(5, 1003, 30, 2), – edit own posts and those beneath them
– editors can do this plus
(8, 1001, 20, 2), – edit comments on all threads beneath them
(8, 1001, 40, 2), – delete comments on all threads beneath them
(8, 1002, 40, 2), – delete drafts of those beneath them
(8, 1003, 40, 3), – delete all posts beneath them
– sidebar admins can
(11, 3001, 20, 2), – create, edit, and delete their sidebar entries
(11, 3001, 30, 2),
(11, 3001, 40, 2),
(11, 3001, 50, 2),
– superuser can do everything
(15, 1000, 10, 3), – create, edit, delete, configure all content
(15, 1000, 20, 3),
(15, 1000, 30, 3),
(15, 1000, 40, 3),
(15, 1001, 50, 3),
(15, 2000, 10, 3), – create, edit, delete, configure all site admin
(15, 2000, 20, 3),
(15, 2000, 30, 3),
(15, 2000, 40, 3),
(15, 2000, 50, 3),
(15, 3000, 10, 3),-- create, edit, delete, configure all sidebars
(15, 3000, 20, 3),
(15, 3000, 30, 3),
(15, 3000, 40, 3),
(15, 3000, 50, 3);
alter table users add column user_role_id int(10) unsigned NOT NULL
default 1 after name,
add KEY idx_userRoleID (user_RoleID);
David King L.
(w) 617.227.4469x213
(h) 617.696.7133
One useless man is a disgrace, two
are called a law firm, and three or more
become a congress – John Adams