Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Sep 18, 1998

Controlling access to individual records in a DB.

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 vwEmployee
as
select e.*
from temployee e, deptheads dh
where
dh.deptid = e.deptid and
dh.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.
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap