(See also 8)
This permission model will be group-based; individual users will have a personal “group”, but can exist in any number of groups. “Guests” will have their own special group, and permissions can be assigned to them like any other group.
By default, no groups will have permission to do anything, other than the administrator, who has full access to everything. Permissions are explicitly granted on an image-by-image basis, so if no specific permission is set on the image for a particular group, access will be denied.
This allows “group ownership” of images, folders, etc, and allows for individual user control while allowing them to share a common set of images.
-- as of 4/26/2007 @12:15pm create sequence groups_sequence start 0; -- 'date_added' is set via trigger. create table groups ( identifier integer not null primary key, description text, password text, -- group password, if any... date_added timestamp without time zone ); create table group_memberships ( group_id integer not null references groups(identifier), user integer not null references users(identifier), owner boolean not null default 'f', -- aka can change permissions date_added timestamp without time zone ); -- setting 'owner' to 't' implicitly sets 'view' to 't' (via trigger) -- setting 'edit' to 't' implicitly sets 'caption' to 't' (via trigger) create table photo_permissions ( photo integer not null references photo(identifier), group_id integer not null references groups(identifier), -- basic permissions per-image owner boolean not null, -- aka can change permissions view boolean not null, -- aka thumbnail/preview & basic info details boolean not null, -- view all detailed info on image original boolean not null, -- access original edit boolean not null, -- modify caption boolean not null, -- modify keywords/captions/etc delete boolean not null -- remove ); create table folder_permissions ( folder integer not null references folder(identifier), group_id integer not null references groups(identifier), -- default image permissions for this folder owner boolean not null, -- aka can change permissions edit boolean not null, -- modify delete boolean nut null, -- remove list boolean not null, -- display folder & view photo list modify boolean not null -- add/remove entries, (photos and/or subfolders) ); create table album_permissions ( album integer not null references album(identifier), group_id integer not null references groups(identifier), -- default image permissions for this album owner boolean not null, -- aka can change permissions edit boolean not null, -- modify delete boolean nut null, -- remove list boolean not null, -- display album & view photo list modify boolean not null -- add/remove entries, (photos and/or subalbums) ); -- Lookup functions go here! -- post-migration we need to: drop table client; drop table client_status; alter table photo drop users; alter table folder drop users; alter table album drop users; alter table photo drop hide_original; alter table photo drop access_rights; alter table folder drop access_rights; alter table album drop access_rights; ------- not sure about these: -- alter table users drop type; -- drop table user_type; -- drop sequence user_type_sequence; -- Don't forget about camera/film/filter/flash/support/scanner/location/clients -- as they also have user/owners.. -- We also need Indices!
This code is roughly what we need to do to see if an image/folder/album has the appropriate permissions. Before it goes into production it'll need to be implemented on the database using pl/pgsql.
// type == photo, group, album // identifier == photo#, group#, album# // user == userid // permission == view/original/edit/caption/delete/details list/modify // returns true if allowed, or false if no match found or denied. function detail_permission(user, type, identifier, permission) { "SELECT g.group_id, p.$permission FROM $type_permissions p, WHERE p.$type = $identifier AND p.group_id in (SELECT 1 UNION SELECT g.group_id from group_memberships g WHERE g.user = $user);" foreach ($results as $result) { if ($result == true) { return true; } } return false; } function user_in_admin_grp($user) { $res = "SELECT userid from group_memberships where user = $user and group_id = 0"; return (num_rows($res) > 0)); } // view == "folder", "album", "photo", "search", etc... function permission(user, photo, permission, view) { if ($user == NULL) user = "null"; // members of admin group get all permissions implicitly. if (user_in_admin_grp($user)) return TRUE; // explicit permission on the photo trumps everything else. $stored = detail_permission($user, 'photo', $photo, $permission); return $stored; }
A few comments:
The goal of this migration is to transform all existing permissions to the new model.
Note – the 'hide_original' setting will be respected for these entries