Hi,

I am designing a Firebird database that is going to store
hierachical data. For example:

+ Group 1
| + Group 2
| | |- Item 1
| | |- Item 2
| + Group 3
| | |- Item 3
| | |- Item 4
. . .
etc

Currently I have 2 key fields:

ITEM_NAME VARCHAR(30) NOT NULL,
PARENT_ITEM VARCHAR(30) NOT NULL,
.
.

This format allows me to keep track of the hieracrchy quite nicely.

The problem I am having is how to grant privileges to users to
securely allow them to see only the records

in selected groups and their children (in much the same way as a
secure file system works). Whenever

groups/items are moved, the privileges must reflect the changes. For
example, if "Item 4" is moved to

"Group 2", users with privileges for "Group 2" must be able to see
it, but users with privileges for "Group

3" must not.


I have thought about this problem a lot and every 'solution' I come
up with seems to have a hidden flaw :o(

Has anybody done something like this before? Are there any
standard 'databasey' ways of doing it?

Thanks for your help,
Paul