How to prevent users from seeing payroll info

  • Thread starter Thread starter Hedi
  • Start date Start date
H

Hedi

I am using and adp project with SQL Server 2000 and Access
2000.

I have a payroll table, I want to hide the salary field
from other users. I can go to the column permission (in
SQL Server) and prevent them from selecting the Salary
column.
However, I am using views and stored procedures that use
the Salary column for calculation and if I prevent them
from selecting the Salary column those view and stored
procedures will break under those users.

Any suggestions on how to solve this?
Your help is greatly appreciated.
Hedi
 
Hello Hedi:
You wrote in conference microsoft.public.access.adp.sqlserver on Fri, 5 Mar
2004 14:56:22 -0800:

H> I am using and adp project with SQL Server 2000 and Access
H> 2000.

H> I have a payroll table, I want to hide the salary field
H> from other users. I can go to the column permission (in
H> SQL Server) and prevent them from selecting the Salary
H> column.
H> However, I am using views and stored procedures that use
H> the Salary column for calculation and if I prevent them
H> from selecting the Salary column those view and stored
H> procedures will break under those users.

H> Any suggestions on how to solve this?

I would create a UDF like this:

if app_name()=SalaryApp then
select @x=salary from payrollTable where id=@id
else
set @x=null
end if
return @x

and use it instead of the salary column. Give users execute permission on
the function, but deny the column. Specify application name in connection
properties of the adp. Of course everyone with Access can make their own
adp and give it the same name, so the key factor would be users' not
knowing which function to use and how. For that, create the function with
encryption, plus manipulate the @id parameter before the query.

Another way is having separate salary table with numeric key, which value
only your Access knows how to calculate from the regular user id.


Vadim
 
I am using and adp project with SQL Server 2000 and Access
2000.

I have a payroll table, I want to hide the salary field
from other users. I can go to the column permission (in
SQL Server) and prevent them from selecting the Salary
column.
However, I am using views and stored procedures that use
the Salary column for calculation and if I prevent them
from selecting the Salary column those view and stored
procedures will break under those users.

Any suggestions on how to solve this?
Your help is greatly appreciated.
Hedi
Click to expand...

That's a hard one with ADPs because you can make stored procedures run with
owner permissions, but then Access/ADO choke when they try to read the
metadata, then manipulate the underlying objects. The best solution I've
found is to use views with the WITH VIEW METADATA option, so DAO doesn't try
to look behind them, then use INSTEAD OF triggers on those to enable updating.
 
Back
Top