Re: Implementing row-level security.

Suwat Kan. wrote:

<snip>  

> I have 3 users : admin can select all data and own the table EMP_SALARY and
> grant the select privilege on table EMP_SALARY to user1 and user2
> user1 can select only data with dept_code = 'ACCOUNT' and
> own the synonym EMP_SALARY reference to ADMIN.EMP_SALARY
> user2 can select only data with dept_code = 'FINANCE' and
> own the synonym EMP_SALARY reference to ADMIN.EMP_SALARY
>
> The condition that I need is :
> If I login as user1, using the select statement : select * from
> emp_salary;
> the displayed data should be : 00001, ACCOUNT, 15000
> 00004, ACCOUNT, 18000
>
> And if I login as user2, using the select statement : select * from
> emp_salary;
> the displayed data should be : 00002, FINANCE, 12000
> 00003, FINANCE, 20000
> In the situation above, if I don't want to create view to filter the row
> for user1 and user2, is there any
> other options to achieve this task. If you have any suggestions, please
> mail to me,

Here's my opinion:
You can give the select permision either to a table or a column but not to the value
of a specified column.
In your case both users need the same table and the same columns, so i see no other
way to use different views to do the restriction unless you use stored procedures or
a programming language.
If you can't use procedures or code then create for each user a view and create a
private synonym for each user wich is called emp_salary and points to his view
(in case of a 100's of users think about roles).

HtH
eugen Received on Mon Jun 15 1998 - 07:54:05 CDT