The data should come from table Emp and from the other tables Dept and Salgrade. Assume that the user's identifier is available through the constant user (see the result of the query: SELECT User FROM Dual;) is the same as the value Ename. I want to create a row in table Emp with own identifier as Ename.
DB:
EMP
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM |DEPTNO
----- ------- --------- ---- --------- ----- ---- ------
7839 KING PRESIDENT NULL 17-NOV-81 5000 NULL 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 NULL 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 NULL 10
7566 JONES MANAGER 7839 02-APR-81 2975 NULL 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 NULL 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7902 FORD ANALYST 7566 03-DEC-81 3000 NULL 20
7369 SMITH CLERK 7902 17-DEC-80 800 NULL 20
7788 SCOTT ANALYST 7566 09-DEC-82 3000 NULL 20
7876 ADAMS CLERK 7788 12-JAN-83 1100 NULL 20
7934 MILLER CLERK 7782 23-JAN-82 1300 NULL 10
DEPT
DEPTNO | DNAME | LOC
------ ----------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SALGRADE
GRADE | LOSAL | HISAL
----- ----- -----
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
Those tables are owned by user SCOTT so - connect as it; Scott will create a view and grant other users select privileges on a public synonym.
SQL> connect scott/tiger
Connected.
SQL> create or replace view v_emp as
2 select d.deptno, d.dname, e.empno, e.ename, e.job
3 from emp e join dept d on e.deptno = d.deptno
4 where e.ename = user;
View created.
SQL> create public synonym psyn_v_emp for v_emp;
Synonym created.
SQL> grant select on psyn_v_emp to public;
Grant succeeded.
OK; so, what does the owner see?
SQL> show user
USER is "SCOTT"
SQL> select * from psyn_v_emp;
DEPTNO DNAME EMPNO ENAME JOB
---------- -------------- ---------- ---------- ---------
20 RESEARCH 7788 SCOTT ANALYST
-----
Scott sees only Scott's data
Connect as some other user (I previously created user named KING):
SQL> connect king/king
Connected.
SQL> show user
USER is "KING"
SQL> select * from psyn_v_emp;
DEPTNO DNAME EMPNO ENAME JOB
---------- -------------- ---------- ---------- ---------
10 ACCOUNTING 7839 KING PRESIDENT
-----
King sees only King's data
SQL>
Therefore, if I understood what you're asking, that would be "it".