Question:
I am building a VB/SQL database app in which I need to control access to individual records in a table based on an EmployeeId. The front-end VB app requires the user to enter her ID/password and builds the appropriate query to retrieve only the records associated with this employee. I am storing the ID/password in a table in the SQL db. The problem is that any db admin can view this table and see the passwords. Do I need to write my own encrytion algorithm before I store the passwords? Is there any ActiveX controls that already do this? Or is there a better scheme to implement record-based security?
Answer:
You certainly shouldn’t have your own table with users and passwords. You should create a separate user for each person in SQL Server. SQL Server takes care of encrypting those passwords.
As to how to protect rows, as a simple example I will use the situation where only department heads can see the information in their departments.
There are two ways to implement such a scheme. One way is to create a table that lists the user names of the departments and the department IDs. Then create a view with the following SQL.
create view vwEmployeeasselect e.*from temployee e, deptheads dhwheredh.deptid = e.deptid anddh.username = user_name()
This gives rights only to the view and not to the underlying table. The second way is to implement a similiar strategy by using a stored procedure instead of a view.
