How to crreate a view where all data are collected concerning the person who uses the view in ORACLE-CodePudding

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

CodePudding user response：

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".