How can I grant each employee read-only access to their own data?

Question

I have a table called Employee (with columns like name, ssn, age, etc..)

Suppose you wanted to give each employee read-only access to his/her own personal row [the data describing them in the Employee table].

How exactly this might be achieved?

Employees normally don't access the database directly. This belongs into an application. — Daniel Hilgarth, Dec 01 '11 at 18:00
@Ryan - Which database is this? If oracle, you can take a look at Fine Grained Access Control http://www.orafusion.com/art_fgac.htm. What about the employee's Boss and the Boss' Boss. Shouldn't they be able to access the employee's details as well? — Rajesh Chamarthi, Dec 01 '11 at 18:17

score 0 · Accepted Answer · answered Dec 01 '11 at 18:24

Depending on your DBMS, you might be able to create a view that selects only the logged-in user's row from the Employee table, and grant access to that. For example, in Oracle, you can do something like:

 create view current_employee as
 (select * from employee e where e.user_id = uid);

('uid' identifies the currently logged-in user).

Of course, as others have said, the more usual scenario is not to have end-users logging in directly to the database, but to have an application that's retrieving the data on their behalf, and is responsible for limiting access.

How can I grant each employee read-only access to their own data?

1 Answers1