Label security - Beginners question...

From: Volker Hetzer <>
Date: Mon, 04 Feb 2008 21:26:41 +0100
Message-ID: <fo7se2$bn$>

After having seen a lot of three tier applications managing security outside the database we'd like, when designing our own app, use the database for this. So, every user is a database user, belongs to a department and can read and insert documents.
What we've got then is a small number of CAD locations, another small number of manufacturing locations and a lot of projects. Each project is designed at one CAD location and manufactured at one manufacturing location. What we've thought about is to have a master table containing the CAD and manufacturing location for each project. Then, all document and other tables reference that table.
When someone from CAD inserts or updates project data, the corresponding manufacturer should be able to see it. But we don't want CAD to think about the manufacturer for each document. In theory, the fact that document X references project Y should be enough for security. In our opinion that would lead to

  • one compartment per project
  • each manufacturing location having a lot of compartments in their permissions (do several hundred compartments have a performance penalty?)
  • moving manufacturing of a project from one location to another would be easy

On the other hand we could have the manufacturing location in the group part of the label and set it via a labeling function. In that case, every insert would fetch the manufacturing location from the project master table and create the appropriate label. However, I see no way to change all labels if the manufacturing location gets changed in the project master table. I would like to avoid doing this with a trigger.

Is there any other way? Maybe with a predicate? But as far as I can see it would have to use a correlated subquery and it would have to know the alias of the outer table.

Any other ideas?

Lots of Greetings!

For email replies, please substitute the obvious.
Received on Mon Feb 04 2008 - 14:26:41 CST

Original text of this message