动态视图使用

在oracle 默认数据库中有两个用户 sales 和research 两个用户 ，在创建一个视图的情况下 如何

实现

当以sales用户登陆时只能查询sales部门的员工信息

当以research用户登陆时只能查询research部门的员工信息

--首先创建动态视图 v_emp：

create or replace view v_emp 

as

select b.dname,a."EMPNO",a."ENAME",a."JOB",a."MGR",a."HIREDATE",a."SAL",a."COMM",a."DEPTNO" from
emp a,dept b
where a.deptno=b.deptno
and b.dname=upper(user);

--在sys用户下创建两个用户并授予查询权限

create user sales identified by sales;

create user research identified by research;

create role select _emp;

grant select on v_emp to select_emp;

grant create session to select_emp;

---这里使用role赋予权限 

grant select_emp to sales;

grant select_emp to research;

---以sales用户登录：

SQL> select * from scott.v_emp;
please input enter 


DNAME    EMPNO ENAME      JOB      MGR HIREDATE     SAL       COMM DEPTNO
-------------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
SALES     7499 ALLEN      SALESMAN     7698 1981-02-20 00:00:00    1600 300    30
SALES     7521 WARD     SALESMAN     7698 1981-02-22 00:00:00    1250 500    30
SALES     7654 MARTIN     SALESMAN     7698 1981-09-28 00:00:00    1250       1400    30
SALES     7698 BLAKE      MANAGER     7839 1981-05-01 00:00:00    2850    30
SALES     7844 TURNER     SALESMAN     7698 1981-09-08 00:00:00    1500  0    30
SALES     7900 JAMES      CLERK     7698 1981-12-03 00:00:00     950    30

6 rows selected.

---以research用户登录：

SQL> select * from scott.v_emp;
please input enter 


DNAME    EMPNO ENAME      JOB      MGR HIREDATE     SAL       COMM DEPTNO
-------------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
RESEARCH     7369 SMITH      CLERK     7902 1980-12-17 00:00:00     800    20
RESEARCH     7566 JONES      MANAGER     7839 1981-04-02 00:00:00    2975    20
RESEARCH     7788 SCOTT      ANALYST     7566 1987-04-19 00:00:00    3000    20
RESEARCH     7876 ADAMS      CLERK     7788 1987-05-23 00:00:00    1100    20
RESEARCH     7902 FORD     ANALYST     7566 1981-12-03 00:00:00    3000    20