动手操作Oracle细粒度访问控制（FGAC）一则

环境：sys@ORCL> select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

sys@ORCL> ！uname -a
Linux localhost.localdomain 2.6.18-308.el5xen #1 SMP Fri Jan 27 17:59:00 EST 2012 i686 i686 i386 GNU/Linux实验过程如下：scott@ORCL> create table tvpd （name varchar2（20）,salary number（8,2）,department_id number（5））;

Table created.

scott@ORCL> insert into tvpd values（"张三",5000,10）;

1 row created.

scott@ORCL> insert into tvpd values（"李四",250,20）;

1 row created.

scott@ORCL> commit;

Commit complete.

sys@ORCL> grant connect to zhangsan identified by zhangsan;

Grant succeeded.

sys@ORCL> grant select on scott.tvpd to zhangsan;

Grant succeeded.

sys@ORCL> grant connect to lisi identified by lisi;

Grant succeeded.

sys@ORCL> grant select on scott.tvpd to lisi;

Grant succeeded.

sys@ORCL> conn zhangsan/zhangsan
Connected.
zhangsan@ORCL> select * from scott.tvpd;

NAME SALARY DEPARTMENT_ID
-------------------- ---------- -------------
张三 5000 10
李四 250 20

zhangsan@ORCL> conn scott/tiger
Connected.
scott@ORCL> create or replace function func_vpd
（owner varchar2,objname varchar2）
return varchar2
is
v_where_clause varchar2（2000）;
begin
v_where_clause :="name=initcap（sys_context（""userenv"",""session_user""））";
return v_where_clause;
end; 2 3 4 5 6 7 8 9
10 /

Function created.

scott@ORCL> conn / as sysdba
Connected.
sys@ORCL> select * from dba_policies where object_owner="SCOTT";

no rows selected

sys@ORCL> BEGIN
dbms_rls.add_policy（object_schema => "SCOTT",
object_name => "TVPD",
policy_name => "scott_policy123",
function_schema =>"SCOTT",
policy_function => "func_vpd",
statement_types =>"select",
sec_relevant_cols=>"salary"）;
END; 2 3 4 5 6 7 8 9
10 /

PL/SQL procedure successfully completed.

sys@ORCL> select * from dba_policies where object_owner="SCOTT";

OBJECT_OWNER OBJECT_NAME POLICY_GROUP
------------------------------ ------------------------------ ------------------------------
POLICY_NAME PF_OWNER PACKAGE
------------------------------ ------------------------------ ------------------------------
FUNCTION SEL INS UPD DEL IDX CHK ENA STA POLICY_TYPE LON
------------------------------ --- --- --- --- --- --- --- --- ------------------------ ---
SCOTT TVPD SYS_DEFAULT
SCOTT_POLICY123 SCOTT
FUNC_VPD YES NO NO NO NO NO YES NO DYNAMIC NO
scott@ORCL> conn zhangsan/zhangsan
Connected.
zhangsan@ORCL> select * from scott.tvpd;

no rows selected

zhangsan@ORCL> select name from scott.tvpd;

NAME
--------------------
张三
李四 在本测试中，我们只是对列salary作精细化控制，如果不查工资还是可以全部看到的，正如上面所示。但是请注意，sys仍然不受影响，因为他有个权限叫“exempt access policy”，这个的性质和sysdba一样。zhangsan@ORCL> conn / as sysdba
Connected.
sys@ORCL> grant exempt access policy to zhangsan;

Grant succeeded.

sys@ORCL> conn zhangsan/zhangsan
Connected.
zhangsan@ORCL> select * from scott.tvpd;

NAME SALARY DEPARTMENT_ID
-------------------- ---------- -------------
张三 5000 10
李四 250 20 同时，受策略保护的表若被drop是不进recyclebin，也就无法用flashback ... to before drop。zhangsan@ORCL> conn scott/tiger
Connected.
scott@ORCL> show recyclebin
scott@ORCL> drop table tvpd;

Table dropped.

scott@ORCL> show recyclebin
scott@ORCL> flashback table tvpd to before drop;
flashback table tvpd to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN LVM上实施ASMCentOS下用Memcached缓存MySQL数据相关资讯 Oracle数据库基础教程 Oracle FGAC 细粒度访问

在Oracle数据库中插入含有&符号的（03/06/2013 09:20:14）
Oracle 执行计划更改导致数据加工（02/13/2013 14:45:04）
判断Oracle Sequence是否存在（02/13/2013 14:32:26）

Oracle数据库中无法对数据表进行（02/26/2013 14:24:58）
Oracle 在同一台主机上建立用户管（02/13/2013 14:40:58）
Oracle em 无法启动,报not found错（02/13/2013 14:29:48）

本文评论查看全部评论（0）