Proposal for multi-user permissions scheme

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

The proposal seems very elaborate, yet as far as I can tell it fails
the simple use case I have:

“Like today, except only registered users with accounts can post
comments.”

Am I missing something?

mathew

On Jul 8, 2006, at 10:35 AM, mathew wrote:

The proposal seems very elaborate, yet as far as I can tell it fails
the simple use case I have:

“Like today, except only registered users with accounts can post
comments.”

Am I missing something?

This is all determined by the contents of the userroles_permission
table, and it can be adjusted however you want. The sql that I’ve
provided for insertions reflects a default value in which all users
can comment. But that’s easily changed on a database level.

All you’d have to do to remedy this is delete the following row from
the userroles_permission table:

user_role_id: 1 # anonymous user
permission_zone_id: 1001 # comment zone
permission_type_id: 30 # create
permission_zone_id: 1 # own

And insert the following row in its place:

user_role_id: 3 # user
permission_zone_id: 1001 # comment zone
permission_type_id: 30 # create
permission_zone_id: 1 # own

This could be effected in the “advanced/expert” level permissions
adjustment user-interface that allows fine tuning, and it could also
be effected by offering it as on option activated with a checkbox in
the “beginner” level interface. But it is a permissions level that is
easily allowable, and that can be given expression in any number of
user interface approaches.


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

public key available upon request