Let's say we have a website like ODP or indeed like WebmasterWorld. Some editors have higer permissions than others right?
Mods have perms in their forum
Admins in their section
BT on everything.
_________________________________________
Here's a simplified DB structure for the site categorie:
categories
id ¦ title ¦ desc
relationships
catid ¦ parent
and let's assume I have a very simple editor tables like:
editors
id ¦ status ¦ name
perms
editorid ¦ catid
______________________________________
So, here's my question (hope your with me so far!):
If I want to make myself have permissions for all categories and there are 100 of them. Do I have to put 100 entries in perms? - Course not, but I don't want to make dozens of calls to the DB just to find out if someone has permissions on a cat.
If you're not quite with me, then neither am I. But weather the above makes sense or not I hope you have an idea of what I'm trying to do.
I'd really appreciate it if someone could give me a shove in the right direction here.
The way I see the above is that I'd have to call up parent of each cat in some kind of recursive loop with many db calls to find out who the 'boss' of a section was (like BT or an Admin here). That seems silly.
Is there a better way, am I over complicating this?
Many thanks!
Nick
First up. Got the changes made and set it all up daisho but, do you have a class that handles this DB? - I just can't see how to work it, hate to be the weak link but I'm just not as good as you guys ;)
Secondly: Let's assume that Daisho has better things to do than add hirachy support just now: Anyone care to have a go at posting Schema for the other major DB here? - (based on my original)?
Cheers all ;)
Nick
>>> "...Please comment on anything you see..."
Looks good to me! Another of my bad habits is pointing out the flaws rather than dishing out the compliments; which probably explains why I didn't comment before. Anyhow, here are some ideas to consider:
Shawn
Interesting security model, looks very flexible.
Just one posible problem I spotted -
The tbladmin_group_rights and tbladmin_user_rights tables have composite keys, but also foreign keys for tbladmin_group?
The foreign keys work, but it cannot enforce referential integrity because they require unique index on the columns in the join.
So I am wondering how it would handle deletions from these tables.
Maybe I'v misunderstood the relationships, its very hard trying to discuss this without diagrams :(
<added>
If you can see this, is this linked in the way its meant to be?
Diagram [www34.brinkster.com]
</added>
Now I've taken daisho's and ShawnR's models to the white board. As soon as I wrap my head around both I'll have more questions but here's a few to start.
ShawnR,
In your first model you included a catID in the editors and in your second you didn't - why? Also, what was your plan for the unused_pkID?
"...ShawnR,
In your first model you included a catID in the editors and in your second you didn't - why? Also, what was your plan for the unused_pkID?..."
1st model
=========
Category (parent)
(1)
^
¦
¦
(n)
Category (1)<--------------------(n) Editor
(1)
^
¦
¦
(n)
Category (child)
2nd model
=========
Category (parent)
(1)
^
¦
¦
(n)
Category (1)<--------(k) security profile rule (m)<------------(n) Editor
(1)
^
¦
¦
(n)
Category (child)
The m-n relationship between security profile rule and editor might look strange at first, but it is because the 'Security_profileID' field in the Editors table is not the 'primary key' of the security profile. a bunch of these rules, all with the same 'Security_profileID' would constitute a security profile. So catID isn't required because the security profile that the ed has determines what cats the ed has permissions on. It is just a short-hand for what daisho had, which as more along the lines of:
Abbreviation of Daisho's model (With terminology changed to match the above)
===============================
Category (1)<--------------(k) individual security rule (m)------------>(1) Editor
and
Category (1)<---(k) sec profile rule (m)-->(1) security profile (1)<---(n) Editor
I didn't have any plan for unused_pkID. That is why I named it 'unused'. Maybe I put it there just to emphasize that neither Security_profileID nor catID were unique primary keys. Or maybe it was just because I stuffed up ;) It shouldn't be there, and Security_profileID & catID should form a compund PK.
Shawn
Say you had a new member, you could give them full permissions by putting them in a group that has thoose permissions assigned.
I think its better than individual permissions, this is how NT permissions work.
No, in Daisho's model admin_users can be members of more than 1 group, and in addition they can have individual privileges granted as well. The "tbladmin_user_group" is a join table between admin_users and admin_group, which makes the relationship between the admins and groups many-to-many.
Shawn
Understood. I've been approaching this from the angle where you can see only what you can edit/write/delete/read. But in daisho's model, the permissions are being used to control what you can do within a category - even though you may be able to see it all - doesn't mean you can do anything else. Right?
SO which set of permissions has authority? The Admin_group or the User? Do I give an admin_group blanket access to all of the "The Webmaster World" and then deny individual users edit/write/delete to "Perl and PHP" or do I deny users all permissions except read and use the groups to apply edit/write/delete permissions? It makes a difference how we approach this.
I'll give you my understanding of it, 'though Daisho may need to correct me re what he intended.
My understanding is that, as it stands per msg#54:
There is nothing in tbladmin_group to say whether the group is "deny all except the rights specifically assigned by tbladmin_group_rights" or "grant all except...". Hence my comment per msg#62. So the assumption is that all rights are 'assigned'. If it is not explicitly assigned, then it assumed to be denied. Also, there is no field in tblaccess_rights to specify if the right is a 'deny' or a 'grant'.
Depending on the application, there might be a minimal level of rights you get for free even though you haven't been assigned any rights. For example, in WebmasterWorld, everyone has read access to all the forums except the supporters forum. Additional rights are just granted, no rules are required to deny.
Given this sort of scheme, there is no need for precidence rules. You are granted permission for all the things your groups have been granted permissions for plus all the things your individual rules grant permissions for. Personally I would prefer to see it extended to allow the ability to 'grant all except... or deny all except. Once that is done, you need to define precidence rules. Then it would make sense that the individual rules take precidence over the group rules. You'd also need to define what is meant if a user belongs to 2 groups, one with a deny all except, and one with a grant all except. I think what would make sense would be to first apply all the 'deny all except' rules , then apply all the 'grant all except' rules on the result'.
It would also make sense to have a field in tblaccess_rights to specify the type of right being granted (r, w, d, etc)
Shawn
So, can anyone tell me what I need to do now based on msg1 please?
Thanks!
Nick
I've enjoyed the debate but 70 posts later I'm just more confused ;)
So, please. Can someone with a little more knowledge summarize the simpler solution for me?
Much obliged.
Nick
So I'm going to throw a wrench into the works and put forth yet another schema based on ShawnR's.
categories
catID ¦ parent_catID ¦ name ¦ desc
edit_permissions
edID ¦ catID ¦ permID
permissions
permID ¦ permissions
editors
edID ¦ name ¦ etc....
The categories table is based on what ShawnR put forth.
Permissions is a lookup table with the abilty to set permissions in any format you'd like: rwed or rd or just r.
Edit_permissions is the crossover. It allows the admin to assign specific users to categories. If you're given GOD access to a category then you have GOD access to that category and all of those below it UNLESS the admin puts a rule in this table that says that you don't. So permissions cascade down along category heirarchy.
So if you want to give an editor access to two categories on the same level - you can assign them and then they have access to all sub-cats under that level. Or you can selectively choose which categories to exclude them from.
More confusing than it's worth?
That looks like something I can understand! ;)
GIve me 1/2hr to digest....
(and thankyou :-)
Nick
So, if I want to find out if a member has edit perms on a cat. I go find his edid in edit_permissions and match that with the categories table right?
And if it doesn't match, I can go do some further querying to find out if he has perms on the parent of that cat.
Might have to be quite a few lookups but it's clean and understandable (i think..).
Is that how it works lorax?
Nick
Maybe it could be dealt with by a self join, that seems what it is really. This seems to select categories with a parent id=1 somewhere in the hierachy.
SELECT categories.name
FROM (Categories INNER JOIN Categories AS Categories_SELF ON Categories.Parent_Cat_id = Categories_SELF.Cat_id)
where Categories.Parent_Cat_id=1
So, if I want to find out if a member has edit perms on a cat. I go find his edid in edit_permissions and match that with the categories table right?
In a round about way yes. The edit_permissions table is designed to allow you to match the editor with their permissions for a particular category if it is assigned. If the category is a sub of one they already have access to - no need to assign anything - just check to see if the category is a sub of those they have permission for - this is the permissions cascade.
Always check edit_permissions first. If there is a sub-category under one they have permission for that you don't want them to have access to you need to record this in the edit_permissions table.
To use this schema I'd basically do a query for the edID and pull all of the relevant results back and put them into arrays. From there I would do my testing.
For example.
editors table
edID = NW1 ¦ name = NW
edID = GB2 ¦ name = GB
categories
catID = 1 ¦ parent_catID = NULL (GOD) ¦ name=top
catID = 2 ¦ parent_catID = 1 ¦ name=first tier (PHP Forum)
catID = 3 ¦ parent_catID - 1 ¦ name=first tier (Graphics Forum)
catID = 4 ¦ parent_catID = 2 ¦ name=3rd tier (SOAP for PHP Forum)
permissions
permID = 1 ¦ permissions = RWED
permID = 2 ¦ permissions = RWE
permID = 3 ¦ permissions = WD
edit_permissions
edID = 1 ¦ catID = 1 ¦ permID = 1
edID = 2 ¦ catID = 2 ¦ permID = 1
edID = 2 ¦ catID = 4 ¦ permID = 2
If I want to get all of the permissions for a particular editor I'd use:
SELECT editors.*, edit_permissions.*
FROM editors,edit_permissions,permission
LEFT JOIN editors ON editors.
edID=edit_permissions.edID
LEFT JOIN permissions ON permissions.permID=edit_permissions.permID
WHERE edID = 2
(I haven't really thought this code through so forgive me if I've got it wrong - a wee bit of a hurry as I can hear her stirring).
This should provide you all of the permissions and categories related to a particular editor.
It's late where I am so I'll set it up in the am. and post the results/questions though I see no reason to quibble or be greatly confused now you've explained what the english would call the bleeding obvious ;-)
One thing though, does your explanation of the cascade cover what aspdaddy was saying about self inner joins or is that 2 ways of doing the same thing?
Thanks guys!
Nick
ShawnR wrote:You could think in terms of 'permissions profile' instead of 'user group'. i.e. "I am going to set up new user xxx with the yyy user profile", rather than "I am going to add new user xxx to the yyy group". Possibly just a terminology thing, but I find it is useful to think in that sort of mindset...
I Think that is better terminology. That is basically how I think of it I'm just not very inventive on words. A group is just a collection of permissions rather than a specific role.
User ID could very well be the Primary Key. It's always a toss up for me (ie I used a VARCHAR for the rights description but only because you will be typeing them out all the time for security checks). In most cases I like the PK to be a NUMBER. Less storage space for linking tables and I believe would be quicker (though I have no direct data to back that one up ;) ).
I have been thinking about having an inherited rights filter type idea. Just haven't got around to building it but I love the idea. If I understand your precidence correctly (ie a right specificly given to a user superseeds a filter done on a group he belongs to) then I agree with your order.
aspdaddy wrote:The foreign keys work, but it cannot enforce referential integrity because they require unique index on the columns in the join...
I have not run into this as I am a little gun shy about referential integrity. I do all my consistancy checking manually and can easily find orphans with an outer join.
Although rather than foreign keys I simply name columns in such a way that makes it obvious (or somewhat at least) of what a field links to.
Lorax:SO daisho,
Bare with me as I work my way through this. In your model I see a limitation by making the admins members of groups. Is this so or am I not applying the structure correctly?...
SO which set of permissions has authority? The Admin_group or the User? Do I give an admin_group blanket access to all of the "The Webmaster World" and then deny individual users edit/write/delete to "Perl and PHP" or do I deny users all permissions except read and use the groups to apply edit/write/delete permissions? It makes a difference how we approach this.
What limitiation? I'm not sure I see one please explain.
Both. My thinking is that I have groups so you can make a "profile" of rights. For WebmasterWorld I may setup a profile for an Admin. The profile would list the different privileges for an admin (ie "delete" "update" "move"). They have a permission for each category/forum. You then have a "moderator permission" for each category (this is where hiearchy of some type should be added).
Then when someone becomes a mod/admin you add him to the Admin profile/group. At this point he/she still can't really do anything as they don't have rights to any forum yet. You then add the forum permission for all the forums they can do that action on.
Inside the actual code (not the security model) you ask the system can the user "edit" and "admin_this_category". If both are true access is granted. If not then not :)
Not sure if that's the best way to set this up but that's my first thoughts on it.
I'm going to end this message right now as it's getting quite long and keep reading the new threads ;)
daisho.
Another way to layer groups is to have a precidence number for a group so that a lower precidence group could not override permissions from a higher level group. This wouldn't be used much but would be handy for Admin type groups.
ShawnR wrote:It would also make sense to have a field in tblaccess_rights to specify the type of right being granted (r, w, d, etc)
That's a grey area since an argument could be made that it does not belong in a "generic" security model. I always thought about the model as being generic questions. In that case r, w, d etc are different permissions. ie "Can daisho delete message", "Can daisho read message".
Here is the code I use for my model again any suggestions would be appriciated as this thread is getting me fired up to expand this :).
www.skycomp.ca/security_module.tar.gz
daisho.
[edited by: jatar_k at 10:08 pm (utc) on May 1, 2003]
[edit reason] delinked [/edit]
How does the permissions coloum on pemissions table work?
I could see perhaps using numbers, like 0=no perms, 1=edit perms, 2=edit+move etc but how did YOU see that working?
Thanks!
Nick
>>> "...How does the permissions coloum on permissions table work..."
I'll give my understanding of what lorax intended. lorax can correct me if I get it wrong. The 'permissions' table is just a list of legal values. e.g. It would be used to fill the drop-down select box in the administration form. So it may contain records such as 'RWED', 'RWE', etc as per lorax's post msg# 78. It might contain a max of 4 or 5 records in the whole life of the database.
>>> "...I could see perhaps using numbers, like 0=no perms, 1= (etc)..."
It would be easier to use strings you can understand such as RWED, RWE, rather tahn numbers. The table already as a PK (permID) so using numbers would just be replicating that. In fact, the permID PK is not required, and permissions could be the PK. Note you don't need an entry for 'no perms', as that is the default if no permissions have been assigned.
>>> "...ahem, So, can anyone tell me what I need to do now based on msg1 please? ..."
Well, Nick, I agree it has been an interesting discussion. I guess we all abandoned good software engineering & project management practice and let enthusiasm take over. So bringing it back to good software engineering practice, lets first define the requirements:
So what you need to do depends on what the requirements are. If item 4 is a requirement, then lorax's suggestion msg#74 needs an additional 2 tables between editors and edit-permissions (One called say 'security_profile', and the other being a join table between 'editors' and 'security_profile') (or else you need to go with Daisho's model). If items 4 and 5 are requirements, then go with Daisho's model for the security stuff, and the hierarchy stuff from the other posts. If item 6 is a requirement then even Daisho's model needs some additions.
... So it really boils down to what the requirements are. If only 1, 2, and 3, then (ducks and hides) what you had in msg#1 is fine (except that you don't need the separate 'relationships' table). If you only require items 1 & 2, then what I suggested in msg#11 is the way to go.
Shawn
Spot on.
>> I guess we all abandoned good software engineering & project management practice and let enthusiasm take over.
Not really IMO though I'm sorry if I dragged it out a bit. I'm a bit anal about understanding things I don't and I wanted to be sure I understood ShawnR's and daisho's models. I'm not the brightest bulb in the pack but I can be the most tenacious.
BTW - We blew right over Jamesa's offering to the conversation and I apologize for not at least acknowledging it. I think it is again yet another example of how to approaching this project but is completely (as are all of our ideas) dependent upon your situation Nick.
ShawnR's right - we could go round and round and keep coming up with different ways to accomplish the same thing. Which one should you use? You need to implement it so I'd suggest you choose whichever one makes the most sense to you and fits your needs. Personally, I'd go with mine but that's mostly because it fits the way I've been thinking about your problem. If the other models make more sense to you and you can see they would fit your situation - then by all means - use them.
Hope we've been of some help albeit long winded :)
Aswell as the good design principle Shawn mentioned, there are, what is practical considerations aswell.
I think Daisho's model is superb but, I don't understand it as well as I'd like too and it needs hierachical support ideally.
I'm going with Lorax's last offering (and correct me if I'm wrong but it's based on Shawns previous right?).
I understand it, it does what I want and with really very little compromise on my part it fits right in with what I have in mind ;)
An enourmous thankyou to everyone that helped make this the best damn DB design theory class I've ever read!
I'll be back as soon as I get it set up and can't work out how to flip the on switch... ;-)
Nick
Sorry, I didn't mean to imply that at all. All I meant by 'good design practice' was 'Let's nail the requirements before setting out to find the solution'. But hey, sometimes you don't know what the requirements should be until you've explored the issues, so perhaps I'm wrong. And lorax, don't undersell yourself!
>>>"...Aswell as the good design principle Shawn mentioned, there are, what is practical considerations aswell..."
I guess by 'requirements' I meant the practical requirements as well. But again, sometimes you need discussion in order to understand the trade-offs, so perhaps I'm wrong.
Anyway, sorry my last post upset anyone. I didn't mean it that way.
>>>"...We blew right over Jamesa's offering ..."
I agree. I think it is a good suggestion. If the system had to scale beyond say 100 levels in the hierarchy, that is the sort of trade-off between speed and Normalisation that I'd go for.
Shawn
Not at all. :)
I've described it in Nicks other monster thread [webmasterworld.com] but I thought I better mention it here too in case anyone is ignoring the other thread :)
Behave, only YOU think that ;) - The rest of us are just IMPRESSED with your knowledge and happy you're here you daft bugger! ;)
>mention here
Cheers Graham!
Nick
Thanks :) & its great to be here! You guys are great for my mental stimulation, and I always learn heaps from reading your posts. BTW, It's just an act. Why do you think I didn't respond to "Let's assume that Daisho has better things to do than add hirachy support just now: Anyone care to have a go at posting Schema for the other major DB here?"... Didn't want to spoil the illusion... ;)
