Oracle 同义词和视图

A_oe用户访问A_hr下的员工表

/*
===========================================================
|          将访问员工表的权限授予A_oe用户
============================================================
*/   
GRANT SELECT ON employee TO A_oe;


--以A_oe用户登录   
SELECT * FROM A_hr.employee;

--是否有更好的解决方案？

 

视图

/*
===========================================================
|         在订单表中，只允许当前员工查看自己的订单记录
============================================================
*/
--当前用户A_oe
--获得create view权限

CREATE OR REPLACE VIEW v_myOrders
AS
SELECT * 
  FROM orders 
 WHERE sales_rep_id=(SELECT empno
                       FROM employee 
                      WHERE ename=(SELECT USER FROM dual));

SELECT * FROM v_myOrders;

/*
===========================================================
|         在员工表中，普通职员只允许看姓名、部门列
============================================================
*/
--当前用户A_hr
CREATE OR REPLACE VIEW v_employee
AS
SELECT empno,ename,e.deptno,dname 
  FROM employee e INNER JOIN dept d 
    ON e.deptno=d.deptno;

 

同义词

/*
===========================================================
|          创建私有同义词
============================================================
*/
CREATE OR REPLACE SYNONYM emp FOR employee;

/*
===========================================================
|          创建公有同义词
============================================================
*/     
CREATE PUBLIC SYNONYM employee FOR A_hr.employee;

GRANT SELECT ON employee TO A_oe;   

----以A_oe用户登录   
--SELECT * FROM A_hr.employee;
SELECT * FROM employee;
/*
===========================================================
|          删除同义词
============================================================
*/   
DROP SYNONYM emp; 
DROP PUBLIC SYNONYM employee; 

/*
===========================================================
|          其他用户都可以访问A_hr用户下的employee表
============================================================
*/   
GRANT SELECT ON A_hr.employee TO public;
CREATE PUBLIC SYNONYM employee FOR A_hr.employee;

--以其他用户登录
SELECT * FROM employee;