History of TP-DB-Permissions
Version 6
TP-DB-Permissions
Thoughts on TikiPro Database Permissions and Content Access Control
Created by: William Leibzon, Last modification: 29 Jan 2005 (22:24 UTC) by William Leibzon
WARNING - THIS PAGE IS NOT YET READY, STILL WORKING ON THE DESIGN ASPECTS
The table's primary key is permissoin name itself which is often long text. Having primary key that is often referenced to be non-numerical value is considered a bad design and slows database access as well as increases size of database tables and indices. So it is best to untroduce new numerical id for each permission.
In order to not make it necessary to lookup the id for each access to page and for use of the permission name in php pages (where its all done by name), it might be good to add php id and configurations file for each package that can be updated whenever permissions are added. Then php function will convert from real name to numerical id without requirying database access based on that configuration file.
Also it might be good idea to rename this table from user_permissions to tiki_permissions as in reality it represents tiki-specific information and not user-specific data
users_objectpermissions
This table needs to be replaced to allow permissions control on per-content (rather then per object) and per group
tiki_permissions (primary key is perm_id, unique constraint on perm_name):
This table is used to hold information about each permission
perm_id - integer - Numerical ID of the permission, assigned when package is added
perm_name - varchar - Actual name of the permission
perm_desc - varchar - Full description
level= - varchar - This is might have been better if it was called "permission type". It specifies appropriate control level needed to use this permission, typical values are "admin", "editors", "basic". This closely corresponds to what group is typically this permission assigned to.
package - varchar - Name of the package which uses this permission
maintainer_url - varchar - This is used to point to whoever supports use of this permission. Normally this would be "tikipro.org", but for custom permissions the value would be something else
users_groups (primary key is group_id):
This holds information about each group
group_id - ingeger - Numeric id of the group
user_id - integer - This is an id of the user that added the group and can change it
is_default varchar(1) - not 100% sure but it seems to be a pointer to which group new users are added to. NOTE: If this is so, it has no place in users_group table as you might expect only one group to have this property, the actual default group_id should instead be entered in some general tikipro config table like tiki_defaults.
group_name - varchar - This is the actual name of the group as seen by end-users
group_desc - varchar - Longer description of what kind of users are going to be in the group
access_question - varchar - Additional question to ask before granting user access as member of this group
access_answer - varchar - Expected answer to access question (i.e. password). Note that it might be good idea to have this be MD5 hash of actual answer
tiki_access_permissions
This table has information about each specific access type. Access is a grouping of one or more permissions as it applies to the content.
perm_id - integer - what permission is in this access
group_id - integer -
content_id - integer -
users_group_permissions_ (primary key is combination of group_id and perm_id)
This table has information on what kind of permissions does a group has by default
group_id - integer - Which user
perm_id - integer - Which permission
value - varchar(1) - Unsure what this is for....
Current Tables
users_permissionsThe table's primary key is permissoin name itself which is often long text. Having primary key that is often referenced to be non-numerical value is considered a bad design and slows database access as well as increases size of database tables and indices. So it is best to untroduce new numerical id for each permission.
In order to not make it necessary to lookup the id for each access to page and for use of the permission name in php pages (where its all done by name), it might be good to add php id and configurations file for each package that can be updated whenever permissions are added. Then php function will convert from real name to numerical id without requirying database access based on that configuration file.
Also it might be good idea to rename this table from user_permissions to tiki_permissions as in reality it represents tiki-specific information and not user-specific data
users_objectpermissions
This table needs to be replaced to allow permissions control on per-content (rather then per object) and per group
New Permission/Access Tables
tiki_permissions (primary key is perm_id, unique constraint on perm_name):
This table is used to hold information about each permission
perm_id - integer - Numerical ID of the permission, assigned when package is added
perm_name - varchar - Actual name of the permission
perm_desc - varchar - Full description
level= - varchar - This is might have been better if it was called "permission type". It specifies appropriate control level needed to use this permission, typical values are "admin", "editors", "basic". This closely corresponds to what group is typically this permission assigned to.
package - varchar - Name of the package which uses this permission
maintainer_url - varchar - This is used to point to whoever supports use of this permission. Normally this would be "tikipro.org", but for custom permissions the value would be something else
users_groups (primary key is group_id):
This holds information about each group
group_id - ingeger - Numeric id of the group
user_id - integer - This is an id of the user that added the group and can change it
is_default varchar(1) - not 100% sure but it seems to be a pointer to which group new users are added to. NOTE: If this is so, it has no place in users_group table as you might expect only one group to have this property, the actual default group_id should instead be entered in some general tikipro config table like tiki_defaults.
group_name - varchar - This is the actual name of the group as seen by end-users
group_desc - varchar - Longer description of what kind of users are going to be in the group
access_question - varchar - Additional question to ask before granting user access as member of this group
access_answer - varchar - Expected answer to access question (i.e. password). Note that it might be good idea to have this be MD5 hash of actual answer
tiki_access_permissions
This table has information about each specific access type. Access is a grouping of one or more permissions as it applies to the content.
perm_id - integer - what permission is in this access
group_id - integer -
content_id - integer -
users_group_permissions_ (primary key is combination of group_id and perm_id)
This table has information on what kind of permissions does a group has by default
group_id - integer - Which user
perm_id - integer - Which permission
value - varchar(1) - Unsure what this is for....