利用vpd实现只读表

1。构建策略函数：
CREATE OR REPLACE FUNCTION no_records (
p_schema IN VARCHAR2 DEFAULT NULL,
p_object IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2
AS
BEGIN
RETURN '1=0';
END;

2。添加策略
BEGIN
DBMS_RLS.add_policy
(object_schema => 'SCOTT',
object_name => 'PEOPLE_RO',
policy_name => 'PEOPLE_RO_IUD',
function_schema => 'SEC_MGR',
policy_function => 'No_Records',
statement_types => 'INSERT,UPDATE,DELETE',
update_check => TRUE);
END;

3。测试：
sec_mgr@KNOX10G> scott@KNOX10g> -- User can read all records
scott@KNOX10g> SELECT COUNT (*) FROM people_ro;

COUNT(*)
----------
14
scott@KNOX10g> -- Cannot update any records
scott@KNOX10g> UPDATE people_ro
2 SET ename = NULL;

0 rows updated.

scott@KNOX10g> -- Cannot delete records
scott@KNOX10g> DELETE FROM people_ro;

0 rows deleted.

scott@KNOX10g> -- Cannot insert new records
scott@KNOX10g> INSERT INTO people_ro (ename) VALUES ('KNOX');
INSERT INTO people_ro (ename) VALUES ('KNOX')
*
ERROR at line 1:
ORA-28115: policy with check option violation

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9599/viewspace-472988/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9599/viewspace-472988/