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
er.... (too english! - moving on.....)..
Sounds like a PLAN - You could post it here daisho?
Nick
The problem - you now need to use inner joins :( its messy trying to write them manually, but something like this will give a list of editors and thier categories:
select editor.name, category.name from
editior inner join isAnEditorOf
on editor.id = iaAnEditorOf.Editor_id
inner join category on
isAnEditorOf.category_id = category.id
daisho
So, in my case, which we'll assume is to make a system that's kind of a cross between DMOZ and just about any forum software you'd care to mention, which would be more appropriate?
Nick
Though my model definatly adds some complication to the mix and takes some additional setup.
Being a MOD here yourself Nick whats the best way for me to post my security model. It consists of about 5 source files and a table definition file. I can tar.gz it and put it up at my site. Though how do I go about sharing the link?
daisho
Bah, not that bad. ;)
Ok, so what would an example compound key look like?
>> which would be more appropriate?
Again - I'll default to your decision. daisho's idea is more flexible I think but with aspdaddy's ideas you also gain flexibility. I think the latter is the easier to implement despite the inner joins.
Can we get an full explanation Aspdaddy?
And thanks again everyone, this is a real education!
Nick
Well thats it , both columns together are the key, because each editor-category pair is unique.
You could add a primary key as well but no need really.
btw- this is no solution for the hierachy/inheritance thing, thats another big and separate problem. This just gets around the problem of allowing multiple records per entity.
It was an ideal ;) - Another thing I've learnt today is that sometimes balancing realism against the ideaL goal is painful but needed ;)
Nick
Why would it need one? - Surely the editor id would be good right?
Nick
editor id cat id
========= ======
1 14
1 15
2 12
2 19
SELECT edid FROM table WHERE edid = 4 AND catid = 3
So what would be the advantage with a compound key?
Just dont see the theory, I've no doubt you're right ;)
Nick
Now I get it, you posted at the same time as I was ;-)
Superb!
Added - Of course theres no need for there to be a primary key though is there?
Nick
[edited by: Nick_W at 5:56 pm (utc) on April 30, 2003]
This says essentially that I want the editor's name and the category name where the editor's ID exists in the isAnEditorOf table AND that record contains the category ID.
Much easier than using
SELECT edid FROM table WHERE edid = 4 AND catid = 3
as the point is to get all categories the single editor has permissions to and not just to check if the editor can access one.
Well, can we summarize? - My heads spinning!
[b]editors[/b]
edid ¦ status ¦ name[b]cats[/b]
catid ¦ title ¦ desc ¦ cat_parent
[b]isEdidtorOf[/b]
edid ¦ catid
That look right for the Schema?
Nick
Yours and mine both - and Pubcon finished a few days ago. :)
Let's get the specs down first. It's not a true heirarchy if you want to be able to pick and choose categories. So which is it - top down heirarchy or random categorical assignment?
Nick
I think (in a relational model at least) you would have to make those 100 inserts, or how else are you storing the fact that you have permission to edit all those cats.
But it wouldnt be often would it? And its only two integers being inserted. Maybe it could be part of the db setup script, rather than production.
Daisho's model is the better of the two for that version. ShawnR's and mine or more for the top down approach but they don't allow for categories on the same level.
The nice thing that I see with my model is that you can add oddball permissions that almost sit outside your hiearchy. Ie if you want moderators to be able to see all IP addresses for submissions in all categories. Just add that permission.
Because of the abstraction you can secure anything. Like I said it wouldn't be to hard to add the hiearchy model to mine which would also let you have you 1000 groups deep model.
daisho.
Before writing off the 100 inserts scenario, look at the recursion as that can be expensive too.
<added>
I would follow it through, data models one thing, but you need to write many queries too, inserts, updates, deletes..One design may be easy now but give you trauma when writing the deletes..:(
</added>
[edited by: aspdaddy at 7:32 pm (utc) on April 30, 2003]
As you can see by my initial inserts at the bottom even the rights manager uses it's self to granting access to manage rights :)
daisho.
CREATE TABLE tblaccess_rights (
right_idVARCHAR2(100) NOT NULL,
right_descVARCHAR2(100),
right_detailsVARCHAR2(4000),
right_statusNUMBER(1) DEFAULT 0 NOT NULL,
CONSTRAINT pk_access_right_id PRIMARY KEY (right_id)
);
CREATE TABLE tbladmin_user_rights (
admin_idNUMBER DEFAULT 0 NOT NULL,
right_idVARCHAR2(100) NOT NULL,
CONSTRAINT pk_admin_user_rights PRIMARY KEY (admin_id,right_id)
);
CREATE TABLE tbladmin_group_rights (
group_idNUMBER DEFAULT 0 NOT NULL,
right_idVARCHAR2(100) NOT NULL,
CONSTRAINT pk_admin_group_rights PRIMARY KEY (group_id,right_id)
);
CREATE TABLE tbladmin_user_group (
admin_idNUMBER DEFAULT 0 NOT NULL,
group_idNUMBER DEFAULT 0 NOT NULL,
CONSTRAINT pk_admin_user_group PRIMARY KEY (admin_id,group_id)
);
CREATE TABLE tbladmin_user (
admin_idNUMBER DEFAULT 0 NOT NULL,
admin_loginVARCHAR2(15) NOT NULL,
admin_passwordVARCHAR2(15) NOT NULL,
admin_nameVARCHAR2(50),
CONSTRAINT pk_admin_user_id PRIMARY KEY (admin_id)
);
CREATE UNIQUE INDEX idx_admin_user_login ON tbladmin_user (admin_login);
CREATE INDEX idx_admin_user_password ON tbladmin_user (admin_password);
CREATE SEQENCE seq_admin_id;
CREATE TABLE tbladmin_group (
group_idNUMBER DEFAULT 0 NOT NULL,
group_nameVARCHAR2(50),
CONSTRAINT pk_group_id PRIMARY KEY (group_id)
);
CREATE SEQUENCE seq_group_id;
INSERT INTO tbladmin_group VALUES (seq_group_id.nextval,'Administrators');
INSERT INTO tbladmin_user VALUES (seq_user_id.nextval,'admin','adminpw','The First Administrator');
INSERT INTO tbladmin_user_group values (1,1);
INSERT INTO tblaccess_rights VALUES ('rightsmanager','Rights Manager Access','Granted this access will allow the user to manage (add/edit/delete) access permisstions across the system. Do not remove this access right from yourself or you will no longer be able to use this manager!',0);
INSERT INTO tbladmin_group_rights VALUES (1,'rightsmanager');
COMMIT;
My mysql don't like it ;(
Also, I don't understand half of those commands, contraint etc.... - I need this to work sooner than it would take to learn it all I think although it looks facinaitng ;)
Nick
This script is an Oracle script. Slightly different things than MYSQL. Change "NUMBER" to "BIGINT" and "VARCHAR2" to "VARCHAR".
The Constraint lines are simplay a different form of doing:
admin_id BIGINT NOT NULL DEFAULT 0 PRIMARY KEY
The reason I use a constraint line rather than putting "PRIMARY KEY" inline is that I can name the key. Easier to look at what's what down the road.
Also for mysql you will want to take out the "CREATE SEQUENCE" commands and then add "AUTO_INCREMENT" command to admin_id and group_id on tbladmin_user and tbladmin_group respectivly. Oracle does not have auto increment columns they have sequences.
Also then with the inserts take out the "seq_admin_id.nextval" and "seq_group_id.nextval" and replace with "NULL" to trigger the autoincrement.
Sounds like a lot but it shouldn't be that much.
daisho.
two tables, two looks ups, unlimited heirarchy.
editor
editorID ¦ catID
relationships
catID ¦ parent
So for a heirarchy like:
/grandparent/parent/child
...the relationship table would look like (using names just for example):
relationships
parent ¦ grandparent
child ¦ parent
child ¦ grandparent
you only edit the relationships table when you add a level. So if you add 'grandchild' making the heirarchy:
/grandparent/parent/child/grandchild
...then the relationship table looks like:
relationships
parent ¦ grandparent
child ¦ parent
child ¦ grandparent
grandchild ¦ child
grandchild ¦ parent
grandchild ¦ grandparent
So to find out who can edit the 'child' category, first you query the relationships table for all the parents to 'child' which are 'parent' and 'grandparent'.
Then you query the editor table for:
catID='child' OR catID='parent' OR catID='grandparent'
Just to give my thoughts on some of the posts that occured since I left...
msg#24, lorax: "...how far to go with normalization depends upon the number of levels required... I tend to err on the side of practicality in these cases..."
msg#25, Nick:"...is there a way to use Shawns approach AND allow a editor to have 2 or more cats?..."
msg#26, aspdaddy:"...But - categories-editors is a many-to-many relation..."
msg#50, aspdaddy:"...I think (in a relational model at least) you would have to make those 100 inserts, or how else are you storing the fact that you have permission to edit all those cats. ..."Disagree. However, the look-up would be more complex. Basically a recursive lookup, starting from the category where the editor has permissions, and doing a select to find all the other categories which have parent_catID equal to that category; then a select to find all their children, then a select to find all their children, etc... until you get to the leaves. As I said before, max lookups = number of levels in hierarchy. Similarly, if you want to find out whether an arbitrary editor has rights to an arbitrary cat, just test: Is the catID = to the editor's catID, if no, is the parent_catID = to the editor's catID, etc until you either find it or you are at the top and haven't found it. Again, max lookups = number of levels in hierarchy. Average lookups much less.
msg#58, jamesa:"...relationships
parent ¦ grandparent
child ¦ parent
child ¦ grandparent
..."
So, my recommendation: A hybrid of:
Thanks all for a fascinating thread. Hope my comments above don't come across as negative, and please accept my apology for terms like 'my suggestion'... In truth we were all saying very similar things, and very close to what was already in msg#1, so it was really a collaborative effort (well it was, until daisho hauled out actual code! ;) )
Shawn
...so it was really a collaborative effort (well it was, until daisho hauled out actual code! ;) )Shawn
Sorry about that Shawn. Just sounded like nick needed to get going on things. Sometimes I find it hard to just talk abstract and like seeing schema definitions. Please comment on anything you see. I am very excited in this thread and am anxious to expand my security class with some of the comments made here. Namely I will be adding some type of hiearchy support. Just have to figure out the way I want to do it.
daisho.
