complex security on SQL 7

[Michel Laan]

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 Laan

[Simon Hayes]

"Michel Laan" <ml***@compuserve.com> wrote in message news:<bt**********@ngspool-d02.news.aol.com>...

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 Laan

This solution looks perfectly reasonable to me, and is a common way to
use views. As you said, you can GRANT SELECT on the view plus tableY
only, and that should give you the access you want. An alternative (or
indeed additional) possibility is to allow access to the tables only
through a stored procedure, which may be useful if you need to bring
in more complex permissions logic.

As for performance, that is a separate question, but a normal view is
transparent to the query optimizer (it essentially replaces the view
name with its definition when you submit a query), so using a view
doesn't raise any special performance issues as such.

Simon