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;
