Complex security setting on SQL 7.0

Hi,
I am running a financial database on SQL 7.0. For several reasons I cannot
upgrade to another version shortly. My problem is that I need to create
database users that have limited access to a number of tables.
Example:
tableX:
code, value, type
a, 100, x
a, 200, y
b, 300, x
b, 400, x
tableY:
code, secgroup
a, confidential
b, nonconfidential
relation between tables:
tablex.code=tableY.code
I want to setup a database user that has read-rights on tableY and limited
read-rights on tableX.
On tableX the user may only see the records for which the group in tableY
the secgroup is "nonconfidential" or for which the type in tableX is set to
"y".
Possible solution:
The only possibility I came up with so far, is to create a view on tableX
like:
create view viewX as
select tableX.code, tableX.value, tableX.type
from tableX inner join tableY on (tableX.code=tableY.code)
where tableX.type='y' or tableY.secgroup='nonconfidential'
If I now create a database user with read-rights to the view only, I think
it will work. My questions are:
1. Is this the best solution for achieving my goal?
2. What will this mean to the performance of my queries?
Any insight will be helpful.
Michel LaanFrom your description, a view appears to be the best (and possibly only)
solution. As far as performance, you will have to benchmark this yourself
because there are too many factors involved to determine this.
Rand
This posting is provided "as is" with no warranties and confers no rights.