 | |
Label security - Beginners question...
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Label security - Beginners question... -
02-04-2008
, 02:26 PM
#2
| |||
| |||
|
|
Hi! 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 wouldbe 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! Volker -- For email replies, please substitute the obvious. |
which is an extra cost item?
A coordinated subquery always has to know the outer table_name or
alias used in the SQL so why is that a problem? Is you application
based on dynamic SQL? From the description given I see no reason why
dynamic SQL would be necessary.
What is the full version of Oracle?
HTH -- Mark D Powell --
#3
| |||
| |||
|
|
Hi! 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 wouldbe 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! Volker -- For email replies, please substitute the obvious. |
which is an extra cost item?
A coordinated subquery always has to know the outer table_name or
alias used in the SQL so why is that a problem? Is you application
based on dynamic SQL? From the description given I see no reason why
dynamic SQL would be necessary.
What is the full version of Oracle?
HTH -- Mark D Powell --
#4
| |||
| |||
|
|
Hi! 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 wouldbe 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! Volker -- For email replies, please substitute the obvious. |
which is an extra cost item?
A coordinated subquery always has to know the outer table_name or
alias used in the SQL so why is that a problem? Is you application
based on dynamic SQL? From the description given I see no reason why
dynamic SQL would be necessary.
What is the full version of Oracle?
HTH -- Mark D Powell --
#5
| |||
| |||
|
|
Hi! 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 wouldbe 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! Volker -- For email replies, please substitute the obvious. |
which is an extra cost item?
A coordinated subquery always has to know the outer table_name or
alias used in the SQL so why is that a problem? Is you application
based on dynamic SQL? From the description given I see no reason why
dynamic SQL would be necessary.
What is the full version of Oracle?
HTH -- Mark D Powell --
#6
| |||||
| |||||
|
|
On Feb 4, 3:26 pm, Volker Hetzer <firstname.lastn... (AT) ieee (DOT) org> wrote: Hi! 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. [rest skipped] |
[reordered your answer a bit]
Quote:
|
What is the full version of Oracle? Thanks for reminding me of the customs here! :-) |
starting with that.
Quote:
|
Is the label a column in one of your tables or Oracle Label security, We think about using label security, although "row level security" |
than it solves. I'm still reading up on it and yesterday evening
discovered something called "fine grained access control". It looks like a
much better fit, with the predicate functions. I still have to play
around with it a bit. But how does it work with DML? How would the policy
modify an insert statement? Or does it evaluate the predicate with the
values from the insert? Can I use
ld and :new like with triggers?Quote:
|
which is an extra cost item? From what I've seen in the documentation it's part of the enterprise |
stuff as far as I see. But we'll check explicitly with our contract guys.
Anyway I'll ask about FGA too.
Quote:
|
A coordinated subquery always has to know the outer table_name or alias used in the SQL so why is that a problem? Is you application based on dynamic SQL? From the description given I see no reason why dynamic SQL would be necessary. You are right, it's not really necessary. During development errors are |
up sqlplus can't see the rows.
Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.
#7
| |||||
| |||||
|
|
On Feb 4, 3:26 pm, Volker Hetzer <firstname.lastn... (AT) ieee (DOT) org> wrote: Hi! 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. [rest skipped] |
[reordered your answer a bit]
Quote:
|
What is the full version of Oracle? Thanks for reminding me of the customs here! :-) |
starting with that.
Quote:
|
Is the label a column in one of your tables or Oracle Label security, We think about using label security, although "row level security" |
than it solves. I'm still reading up on it and yesterday evening
discovered something called "fine grained access control". It looks like a
much better fit, with the predicate functions. I still have to play
around with it a bit. But how does it work with DML? How would the policy
modify an insert statement? Or does it evaluate the predicate with the
values from the insert? Can I use
ld and :new like with triggers?Quote:
|
which is an extra cost item? From what I've seen in the documentation it's part of the enterprise |
stuff as far as I see. But we'll check explicitly with our contract guys.
Anyway I'll ask about FGA too.
Quote:
|
A coordinated subquery always has to know the outer table_name or alias used in the SQL so why is that a problem? Is you application based on dynamic SQL? From the description given I see no reason why dynamic SQL would be necessary. You are right, it's not really necessary. During development errors are |
up sqlplus can't see the rows.
Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.
#8
| |||||
| |||||
|
|
On Feb 4, 3:26 pm, Volker Hetzer <firstname.lastn... (AT) ieee (DOT) org> wrote: Hi! 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. [rest skipped] |
[reordered your answer a bit]
Quote:
|
What is the full version of Oracle? Thanks for reminding me of the customs here! :-) |
starting with that.
Quote:
|
Is the label a column in one of your tables or Oracle Label security, We think about using label security, although "row level security" |
than it solves. I'm still reading up on it and yesterday evening
discovered something called "fine grained access control". It looks like a
much better fit, with the predicate functions. I still have to play
around with it a bit. But how does it work with DML? How would the policy
modify an insert statement? Or does it evaluate the predicate with the
values from the insert? Can I use
ld and :new like with triggers?Quote:
|
which is an extra cost item? From what I've seen in the documentation it's part of the enterprise |
stuff as far as I see. But we'll check explicitly with our contract guys.
Anyway I'll ask about FGA too.
Quote:
|
A coordinated subquery always has to know the outer table_name or alias used in the SQL so why is that a problem? Is you application based on dynamic SQL? From the description given I see no reason why dynamic SQL would be necessary. You are right, it's not really necessary. During development errors are |
up sqlplus can't see the rows.
Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.
#9
| |||||
| |||||
|
|
On Feb 4, 3:26 pm, Volker Hetzer <firstname.lastn... (AT) ieee (DOT) org> wrote: Hi! 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. [rest skipped] |
[reordered your answer a bit]
Quote:
|
What is the full version of Oracle? Thanks for reminding me of the customs here! :-) |
starting with that.
Quote:
|
Is the label a column in one of your tables or Oracle Label security, We think about using label security, although "row level security" |
than it solves. I'm still reading up on it and yesterday evening
discovered something called "fine grained access control". It looks like a
much better fit, with the predicate functions. I still have to play
around with it a bit. But how does it work with DML? How would the policy
modify an insert statement? Or does it evaluate the predicate with the
values from the insert? Can I use
ld and :new like with triggers?Quote:
|
which is an extra cost item? From what I've seen in the documentation it's part of the enterprise |
stuff as far as I see. But we'll check explicitly with our contract guys.
Anyway I'll ask about FGA too.
Quote:
|
A coordinated subquery always has to know the outer table_name or alias used in the SQL so why is that a problem? Is you application based on dynamic SQL? From the description given I see no reason why dynamic SQL would be necessary. You are right, it's not really necessary. During development errors are |
up sqlplus can't see the rows.
Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.
#10
| |||
| |||
|
|
Hi! 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! Volker |
select true
where exists (select vendor from projects)
or whatever your exact constellation is.
--
Regards,
Frank van Bortel
Top-posting in UseNet newsgroups is one way to shut me up
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.