Re: Database schema [was: UU tools]

["Alexey Parshin" <alexeyp@gmail.com>]

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>:

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