General discussion


Schema mapping in Oracle Database

By krcshanthi ·
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

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Schema mapping in Oracle Database

by rmrSalisbury In reply to Schema mapping in Oracle ...

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.

Related Discussions

Related Forums