how to grant access to subsets of tables updateable

[bookouri]

I have been asked to provide access to a table to two different groups of people. I have not had to do anything like this before and need someone to point me in the right direction. I have an employee table for example. In the employee table there is a column for dept. I need to be able to grant dept A users select, update access to all records where the dept col = A, and dept B users select, update access to all records where dept col = B. I want both A and B to be able to select each other's records but not update them. I would like to do this at the table level rather than have to code security into the forms themselves.

any suggestions would be appreciated.

 

[Dagon]

This sounds a bit like a coursework question, but I'll give you the benefit of the doubt. You need to create a view on the EMP table which restricts the rows returned to deptno = 'A'. You can then grant access to the view rather than the underlying table. You would also need to specify "with check option" when creating the view to ensure that users can't insert or update anything outside their assigned department.

 

[bookouri]

I can do an updateable view within certain restrictions. But what I create the form, I'd like to have both groups of users use the same form. When I create the form the first thing Ill do is select a table or view for the datablock used by the form. If I have to have a view for group a and a view for group b then I'll have to have two different forms. Im hoping that somebody has a trick I dont know for giving both groups access through the same form but with different levels of access to the underlying table/view. I expect it probably cant be done easily, but I was hoping somebody might have a trick to make it work.

 

[Dagon]

Your users could simply have private synonyms for the main EMP table which point them towards the appropriate view. The code for the form would then be identical.

 

[karluk]

This certainly looks like a candidate for fine grained access control. Take a look at the dbms_rls package for details. Dagon's suggestion also looks as if it would work.

 

[bookouri]

thanks, ill take a look at both those options.