Re: Database schema [was: UU tools]

["Ilya A. Volynets-Evenbakh" <ilya@total-knowledge.com>]

Well, first of all, yes, we need read access explicitly granted as well:
for paid courses.
This does not mean ACL lists will overgrow though - not every user has
access for every single object. Furthermore - for a person to study a
course,
he should sign up for it. This means, that when he is done with course,
we can "unsign-up" him, and thus remove all his "study" ACL entries for
all the
objects (probably leaving some sort of log trail behind, so that we know
he did
study it and when).

#3 will not work too well since we don't have strict hierarchy of objects.

Alexey Parshin wrote:
> Ok, when I my current understanding of the situation, we should have
> an acl table for each of:
>
> course, topic, explanation, problem.. etc
>
> For any access other than read we should have an entry in the
> course_list_acl, topic_list_acl, etc.. Every entry should provide
> fields for view, modification, create, etc.. (to be detailed).
> We should have also a list of the tables for which we need an entry
> even for read (view).
>
> The alternative implementation requires a grant to every single object
> of the list above available in the database even for read. This isn't
> possible for any large database.
>
> The second alternative is to give read access based on the hierarchy
> of the objects. Say, a student has read access to the course-related
> objects, and that grants him read access down the hierarchy..
>
> 2006/9/23, Ilya A. Volynets-Evenbakh <ilya@total-knowledge.com
> <mailto:ilya@total-knowledge.com>>:
>
>     Alexey Parshin wrote:
>     > It's a C++ approach, with no database support for integrity :(
>     > Object id can be anything, and database can't prevent using
>     course id
>     > for say, topic id. Foreign keys to the actual tables wouldn'd
>     work in
>     > such situation. :(
>     Yes, I realize that. And I'm not happy about that.
>     >
>     > The alternative approach is to have separate tables for different
>     > relations, like course_acl, topic_acl, etc.. It makes us to have
>     > multiple tables, but then we have foreign keys.
>     > In order to satisfy fluffy C++ programmers, however, we may also
>     have
>     > a view - a union of all the acl chunks looking as you defined..
>     Well, if we follow your idea, and all of this data access id done
>     through stored procedures, I really don't need to know if you have
>     single ACL table, multiple ACL tables, view that joins them or
>     anything
>     else ;-)
>
>     To be honest, I don't remember what prompted me to think about having
>     global ACL table. I don't think it is more efficient in
>     any way.
>
>     Ooh.. Yeah - if we want to go in other direction: show me all objects
>     for which given user is author. OTOH, we'd still have
>     to search all actual object tables. This is where LDAP queries win
>     over
>     SQL queries ;-)
>
>     Anyways, I'm definitely not married to the idea of having single
>     ACL table.
>     The important part is for each access action to be separate. i.e.
>     modify
>     doesn't imply study (write doesn't mean read ;-),
>     etc. Then we can have  set of roles which are just groupings of
>     permissions.
>
>     --
>     Ilya A. Volynets-Evenbakh
>     Total Knowledge. CTO
>     http://www.total-knowledge.com
>
>
>
>
> -- 
> Alexey Parshin,
> http://www.sptk.net 

-- 
Ilya A. Volynets-Evenbakh
Total Knowledge. CTO
http://www.total-knowledge.com