LIMITING ACCESS TO MAIN DATA TABLE

[jeannelc]

I am developing a Personnel system for our State Police in SQL Server 7.0 with a Visual Basic 6 front end.

We have over 20 posts and labs that will be responsible for entering background information on existing employees. Once these data are in, the posts will have no access to the database -- only our headquarters Personnel dept will be allowed in.

I am trying to ensure that each post or lab will only be able to see limited information on their employees and need some advice on the best way.

There is a master data table that holds all pertinent data, including salary. There is also a career path transaction table that holds the employee's position transactions -- appointments, transfers, etc. This table includes a work assignment id that links the employee to the post or work location.

The remote offices/posts will be adding transaction records on what we are calling skills modules -- education, military, job skills, and language. They will need to verify that they have the correct employee before entering the data into the transaction tables (1 table for each type of transaction above).

My thought was
[ol][li]to set up database roles for each post that includes those NT user ids who will be entering the data[/li]
[li]create a view for each post that will select only the data needed for verification on only the employees in their post -- name, ssn, badge number. This will be displayed when they enter an employee_id or get a search result on ssn or badge.[/li]
[li]write a stored procedure to insert the records from the Visual Basic DE forms to the Skills Modules data tables[/li][/ol]

(I thought about just asking for a post number as a sign on and using that as part of a select query for the employee data set, but I am afraid that people will enter post #'s different from their own just to peruse other posts' data -- our employees would do that!!)

Does anyone know a better, more effective way to get this done?

Thanks, Jeanne Clotfelter
Database Analyst IT
Computer Technologies
Kentucky State Police

 

[fluteplr]

I think you are basically on the right track. Instead of creating a view for each post, you could create a single view that only selects entries in the same post as the user.

I did something similar to this by creating a view the user's company(post) called v_company from their user login (retrieved by using suser_name(). The view was then set up as

select * from mytable where exists
(select * from v_company where v_company.companyname = mytable.companyname)

 

[jeannelc]

That's it -- I wasn't sure how I could pass the company/post identifyer to the view to filter the records.

Thanks! Jeanne Clotfelter
Database Analyst IT
Computer Technologies
Kentucky State Police