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.

complex security on SQL 7

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"Michel Laan" <mlaan@.compuserve.com> wrote in message news:<bttvfk$85r$1@.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

Compilation

Hi
I want to know how to compiled a report in crystal report 8.5 or 9.0??
In the previous version of crystal Report (Version 8.0) I could do this task, but in the newest version I can find the option...
Please help meHi
This file contains an add-in that enables you to compile and distribute reports in the English version of Crystal Reports 8 and 8.5. This is the instalation File scr8_distr_expert.exe

Thanks

Compatible versions of Ms SQL Server, ODBC driver, Driver Manager and ODBC API s

Hi,

Please help share with me if you know the version compatibility matrix of Ms SQL Server, ODBC driver (sqlsrv32.dll), Driver Manager (odbc32.dll) and ODBC API spec. For instance, how can I know Ms SQL Server 2000 can work with which version of sqlsrv32.dll, a particular version of sqlsrv32.dll can work with which version of odbc32.dll and a certain version of sqlsrv32.dll/odbc32.dll conforms to which version of ODBC API spec (e.g. 3.5).

Any help will be appreciated.

Thanks,

vtluu.Instead of sweating bullets trying to juggle chainsaws, why not just use the current MDAC (http://msdn.microsoft.com/data/mdac/)?

-PatP

Compatibility with sql 2000

Can I Install Sql 2005 in a machine with sql 2000? Will I have problems when the final version of sql 2005 comes out?At RTM of SQL Server 2005 we will support installing it on a machine that already has SQL Server 2000 installed. We have put a lot of work in this area to insure they can install harmoniously on the same machine. We don't recommend installing SQL Server 2000 after SQL Server 2005 is already installed - but the reverse is okay.

Dan|||1. RTM is still a few months in the future, what about right now, will it break 2000?
2. If not, will it be OK if 2000 has Report Server installed?

Thanks.