Discussion Locked

Schema mapping in Oracle Database

0 helpful
1 reply
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Can anyone advise me on mapping of schema objects to oracle users,say for example we have emp and dept tables in database,from an front-end application we collect username and deptno then using this information the front-end application should able to access only those data having deptno what he has provided in front-end application

1 reply

by rmrsalisbury

Create a view on the table(s) you want to restrict, filtering on a 'where deptno = user_deptno' clause, and use the view(s) as the basis for the app. If you want to restrict the user's view to only his/her own department, you can lookup the filtering deptno from a 'user_details' record:

SELECT details FROM emp WHERE deptno =
(SELECT deptno FROM user_details
WHERE username = user) ;

You can extend this approach to hide specific columns (eg salary) simply by not SELECTing into the view.

Note that if the Application needs to modify rather than simply query data, the view must be updatable - simple 'filtering' views based on single tables will be: joins, computed fields, aggregate functions (max, min, avgetc) may render the viewnot updatable. See Oracle documentation for more details.

This discussion is closed to new replies.