new_permission_model
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
new_permission_model [2007/02/13 21:08] – one more to-do item. pizza | new_permission_model [2007/04/26 19:45] (current) – rewrote the last part. pizza | ||
---|---|---|---|
Line 8: | Line 8: | ||
* Allow individual " | * Allow individual " | ||
* Allow finer-grained control over original images | * Allow finer-grained control over original images | ||
- | * ... | ||
- | This permission model will be group-based; | + | This permission model will be group-based; |
- | By default, no groups will have permission to do anything, other than the administrator, | + | By default, no groups will have permission to do anything, other than the administrator, |
This allows "group ownership" | This allows "group ownership" | ||
Line 18: | Line 17: | ||
==== Schema ==== | ==== Schema ==== | ||
- | create sequence groups_sequence start 0; | + | < |
- | + | -- as of 4/26/2007 @12:15pm | |
- | create table groups ( | + | |
- | identifier integer not null primary key, | + | |
- | owner_id references users(identifier), | + | |
- | description text | + | |
- | password text -- group password, if any. | + | |
- | ); | + | |
- | + | ||
- | create table group_memberships ( | + | |
- | group_id integer not null references groups(identifier), | + | |
- | user integer not null references users(identifier) | + | |
- | ); | + | |
- | + | ||
- | create table photo_permissions ( | + | |
- | photo integer not null references photo(identifier), | + | |
- | group_id integer not null references groups(identifier), | + | |
- | -- basic permissions per-image | + | |
- | 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 | + | |
- | remove boolean nut 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 | + | |
- | 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 | + | |
- | delete boolean nut null -- remove | + | |
- | -- folder permissions | + | |
- | 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 folder | + | |
- | view boolean not null, -- aka thumbnail/ | + | |
- | details boolean not null, -- view all detailed info on image | + | |
- | original boolean not null, -- access original | + | |
- | edit boolean not null, -- modify | + | |
- | delete boolean nut null -- remove | + | |
- | -- album permissions | + | |
- | list boolean not null, -- display album & view photo list | + | |
- | modify boolean not null -- add/remove entries, (photos and/or subalbums) | + | |
- | ); | + | |
- | + | ||
- | alter table photo add owner integer references groups(identifier); | + | |
- | alter table folder add owner integer references groups(identifier); | + | |
- | alter table album add owner integer references groups(identifier); | + | |
- | + | ||
- | -- post-migration we need to: | + | |
- | alter table photo drop users; | + | |
- | alter table folder drop users; | + | |
- | alter table album drop users; | + | |
- | alter table photo alter column owner set not null; | + | |
- | alter table folder alter column owner set not null; | + | |
- | alter table album alter column owner set not null; | + | |
- | alter table photo drop hide_original; | + | |
- | alter table photo drop access_rights; | + | |
- | alter table folder drop access_rights; | + | |
- | alter table album drop access_rights; | + | |
- | alter table users drop type; | + | |
- | drop table user_type; | + | |
- | drop sequence user_type_sequence; | + | |
- | + | ||
- | -- Don't forget about camera/ | + | |
- | -- as they also have user/ | + | |
- | -- We also need Indices! | + | |
- | ==== Migration ==== | + | create sequence groups_sequence start 0; |
- | The goal of this migration | + | -- ' |
- | == Set things up == | + | create table groups ( |
+ | identifier integer not null primary key, | ||
+ | description text, | ||
+ | password text, -- group password, if any... | ||
+ | date_added timestamp without time zone | ||
+ | ); | ||
- | * Create new tables. | + | create table group_memberships |
+ | group_id integer not null references groups(identifier), | ||
+ | user integer not null references users(identifier), | ||
+ | owner boolean not null default ' | ||
+ | date_added timestamp without time zone | ||
+ | ); | ||
- | * Create group 0, "Admin Users", | + | -- setting ' |
+ | -- setting ' | ||
- | INSERT INTO groups(identifier, | + | create table photo_permissions ( |
- | VALUES (0, 0 , ' | + | photo integer not null references photo(identifier), |
+ | group_id integer not null references | ||
+ | -- basic permissions per-image | ||
+ | owner boolean not null, -- aka can change permissions | ||
+ | view boolean not null, -- aka thumbnail/ | ||
+ | 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/ | ||
+ | delete boolean not null -- remove | ||
+ | ); | ||
- | INSERT INTO group_memberships(group_id, user) | + | create table folder_permissions |
- | SELECT $_admin_grp_id as group_id, u.identifier | + | folder integer not null references folder(identifier), |
- | FROM users u, user_type t | + | group_id |
- | WHERE u.type = t.identifier | + | -- default image permissions for this folder |
- | AND t.value = ' | + | owner boolean not null, -- aka can change permissions |
+ | edit boolean not null, -- modify | ||
+ | | ||
+ | list boolean not null, -- display folder & view photo list | ||
+ | modify boolean not null -- add/remove entries, (photos and/or subfolders) | ||
+ | ); | ||
- | * Create group 1, " | + | 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) | ||
+ | ); | ||
- | INSERT INTO groups(identifier, | + | -- Lookup functions go here! |
- | | + | |
- | * Create group 2, " | ||
- | INSERT INTO groups(identifier, | + | -- post-migration we need to: |
- | | + | |
- | == The following list needs to be repeated for each user == | + | drop table client; |
+ | drop table client_status; | ||
- | * Create a new group of the same name, make only that user a member. | + | alter table photo drop users; |
+ | alter table folder drop users; | ||
+ | alter table album drop users; | ||
- | $_grp_id = SELECT nextval(groups_sequence); | + | alter table photo drop hide_original; |
- | + | alter table photo drop access_rights; | |
- | | + | alter table folder drop access_rights; |
- | | + | alter table album drop access_rights; |
- | | + | |
- | | + | |
- | * Update ownerships to point to the new group: | + | ------- not sure about these: |
- | UPDATE folder f | + | -- alter table users drop type; |
- | SET f.owner = (SELECT g.group_id | + | -- drop table user_type; |
- | FROM group_memberships g | + | -- drop sequence user_type_sequence; |
- | WHERE g.user = f.users) , | + | |
- | WHERE f.users = $_userid; | + | |
- | + | ||
- | UPDATE album a | + | |
- | SET a.owner = (SELECT g.group_id | + | |
- | FROM group_memberships g | + | |
- | WHERE g.user = a.users) , | + | |
- | WHERE a.users = $_userid; | + | |
- | + | ||
- | UPDATE photo p | + | |
- | SET p.owner = (SELECT g.group_id | + | |
- | FROM group_memberships g | + | |
- | WHERE g.user = p.users) , | + | |
- | WHERE p.users = $_userid; | + | |
- | * Add user into the " | + | -- Don't forget about camera/ |
+ | -- as they also have user/owners.. | ||
+ | -- We also need Indices! | ||
+ | </ | ||
+ | ==== Psuedocode ==== | ||
- | INSERT INTO group_memberships(group_id, | + | This code is roughly what we need to do to see if an image/ |
- | | + | |
- | * Create a new group called | + | < |
+ | // type == photo, | ||
+ | // identifier == photo#, group#, album# | ||
+ | // user == userid | ||
+ | // permission == view/ | ||
+ | // returns true if allowed, or false if no match found or denied. | ||
+ | function detail_permission(user, | ||
+ | | ||
+ | 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);" | ||
- | INSERT INTO groups(identifier, description) | + | foreach |
- | VALUES | + | |
- | | + | return true; |
- | SELECT $_userid_clients_grpid as group_id, c.client | + | } |
- | FROM clients c | + | } |
- | WHERE c.users = $_userid; | + | |
+ | } | ||
- | == For each folder == | + | function user_in_admin_grp($user) { |
+ | $res = " | ||
- | * Add row granting owner all rights, including defaults: | + | return (num_rows($res) > 0)); |
+ | } | ||
- | INSERT INTO folder_permissions (folder, | + | // view == "folder", " |
- | VALUES | + | function permission(user, photo, permission, view) { |
+ | | ||
+ | // members of admin group get all permissions implicitly. | ||
+ | if (user_in_admin_grp($user)) return TRUE; | ||
- | * if access_rights is private, stop here. | + | // explicit permission on the photo trumps everything else. |
+ | $stored = detail_permission($user, | ||
+ | $photo, $permission); | ||
+ | return $stored; | ||
+ | } | ||
+ | </ | ||
- | * if access_rights is protected (ie clients-only) | + | A few comments: |
- | | + | |
- | VALUES | + | * Guest users have a NULL userid, which has to be handled cleanly. |
+ | * All users (and guests) are implicitly members of the 'guests' | ||
- | * if access_rights is public (ie everyone) | + | ==== Migration ==== |
- | INSERT INTO folder_permissions (folder, group_id, view, details, original, edit, delete, list, modify) | + | The goal of this migration is to transform all existing permissions to the new model. |
- | | + | |
- | == For each album: | + | === Set things up === |
- | | + | |
+ | - Create group 0, "Admin Users", | ||
+ | - Create group 1, " | ||
+ | - Create group 2, " | ||
+ | |||
+ | === The following list needs to be repeated for each user === | ||
- | | + | |
- | VALUES ($_albumid, $_usergrp, 't', 't', ' | + | - Add user to " |
+ | - Create a new "$user's private" | ||
+ | - Create a new "$user's clients" | ||
+ | - Create a group for each individual client and add user to it as the owner, then add the client to it. | ||
- | * if access_rights is private, stop here. | + | == For each folder == |
- | | + | |
+ | - if access_rights is private, stop here. | ||
+ | - if access_rights is protected (ie clients-only) | ||
+ | - if access_rights is public (ie everyone) add a row granting read access to guest group. | ||
- | INSERT INTO album_permissions (album, group_id, view, details, original, edit, delete, list, modify) | + | == For each album: == |
- | | + | |
- | | + | |
- | + | - if access_rights is private or album_type is ' | |
- | | + | - if access_rights is protected |
- | | + | |
== For each photo: == | == For each photo: == | ||
- | | + | |
+ | - if access_rights is protected (ie clients-only) add a row granting read access to the client group. | ||
+ | - if access_rights is public (ie everyone) add a row granting read access to guest group. | ||
+ | - If access_rights is private, stop here. | ||
- | INSERT INTO photo_permissions (photo, group_id, view, details, original, edit, delete) | + | //Note -- the 'hide_original' |
- | | + | |
- | * If access_rights is private, stop here. | + | === Finally === |
- | | + | - Drop all obseleted |
- | + | ||
- | INSERT INTO photo_permissions (photo, group_id, view, details, original, edit, delete) | + | |
- | | + | |
- | + | ||
- | //Note; we may want to forego this step if the photo implicitly has identical permissions as we're granting// | + | |
- | + | ||
- | * If access_rights is public (ie guest access): | + | |
- | + | ||
- | INSERT INTO photo_permissions (photo, group_id, view, details, original, edit, delete) | + | |
- | | + | |
- | + | ||
- | //Note; we may want to forego this step if the photo implicitly has identical permissions as we're granting.// | + | |
- | + | ||
- | // | + | |
- | + | ||
- | == Finally == | + | |
- | + | ||
- | | + | |
- | - ??? | + | |
- | - Profit | + | |
- | + | ||
- | ==== Psuedocode ==== | + | |
- | + | ||
- | This code is roughly what we need to do to see if an image has the appropriate permissions. | + | |
- | + | ||
- | // type == photo, group, album | + | |
- | // identifier == photo#, group#, album# | + | |
- | // user == userid | + | |
- | // permission == view/ | + | |
- | // returns true if match found, false if denied, null if no match found. | + | |
- | + | ||
- | function detail_permission(user, | + | |
- | " | + | |
- | FROM $type_permissions p, | + | |
- | WHERE p.$type = $identifier | + | |
- | AND p.group_id in (SELECT g.group_id from group_memberships g | + | |
- | WHERE g.user = $user);" | + | |
- | + | ||
- | // If we have no hits, we must defer to parent permission and return NULL. | + | |
- | // if we get any " | + | |
- | + | ||
- | $best = null; | + | |
- | foreach ($results as $result) { | + | |
- | if ($result == true) { | + | |
- | return $result; | + | |
- | } else { | + | |
- | $best = false; | + | |
- | } | + | |
- | } | + | |
- | return null; | + | |
- | } | + | |
- | + | ||
- | // view == " | + | |
- | + | ||
- | function permission(user, | + | |
- | // explicit permission on the photo trumps everything else. | + | |
- | $stored = detail_permission($user, | + | |
- | $photo, $permission); | + | |
- | if ($stored != null) | + | |
- | return $stored; | + | |
- | + | ||
- | if ($view != ' | + | |
- | $folder = folder_for_photo($identifier); | + | |
- | // make the lookup deal with parents too. | + | |
- | while($folder != null) { | + | |
- | | + | |
- | | + | |
- | if ($view == ' | + | |
- | | + | |
- | } | + | |
- | if ($stored == true) return true; | + | |
- | + | ||
- | | + | |
- | } | + | |
- | } | + | |
- | + | ||
- | if (view != ' | + | |
- | foreach (albums_for_photo($photo) as $album) { | + | |
- | while ($album != null) { | + | |
- | $auth = detail_permission($user, | + | |
- | | + | |
- | if ($auth != null) { | + | |
- | if ($auth == true) return true; | + | |
- | | + | |
- | } | + | |
- | | + | |
- | } | + | |
- | } | + | |
- | } | + | |
- | + | ||
- | return false; | + | |
- | } | + | |
- | ==== Source Hax0r ==== | + | ==== Roadmap |
- | | + | |
- | | + | |
- | | + | - Write PL/pgsql permission lookup code and any necessary triggers. |
- | | + | |
- | | + | |
- | | + | |
- | | + | |
- | | + | - Port photo view/description/edit pages |
- | | + | |
- | | + | |
- | | + | |
- | | + | |
+ | | ||
+ | |
new_permission_model.1171400912.txt.gz · Last modified: 2007/02/13 21:08 by pizza