动手操作oracle细粒度访问控制(FGAC)一则
来源:互联网 发布:java logger级别 编辑:程序博客网 时间:2024/05/20 01:45
环境:
sys@ORCL> select * from v$version where rownum=1;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prodsys@ORCL> !uname -aLinux 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/zhangsanConnected.zhangsan@ORCL> select * from scott.tvpd;NAME SALARY DEPARTMENT_ID-------------------- ---------- -------------张三 5000 10李四 250 20zhangsan@ORCL> conn scott/tigerConnected.scott@ORCL> create or replace function func_vpd(owner varchar2,objname varchar2)return varchar2is 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 sysdbaConnected.sys@ORCL> select * from dba_policies where object_owner='SCOTT';no rows selectedsys@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_DEFAULTSCOTT_POLICY123 SCOTTFUNC_VPD YES NO NO NO NO NO YES NO DYNAMIC NOscott@ORCL> conn zhangsan/zhangsanConnected.zhangsan@ORCL> select * from scott.tvpd;no rows selectedzhangsan@ORCL> select name from scott.tvpd;NAME--------------------张三李四
在本测试中,我们只是对列salary作精细化控制,如果不查工资还是可以全部看到的,正如上面所示。
但是请注意,sys仍然不受影响,因为他有个权限叫“exempt access policy”,这个的性质和sysdba一样。
zhangsan@ORCL> conn / as sysdbaConnected.sys@ORCL> grant exempt access policy to zhangsan;Grant succeeded.sys@ORCL> conn zhangsan/zhangsanConnected.zhangsan@ORCL> select * from scott.tvpd;NAME SALARY DEPARTMENT_ID-------------------- ---------- -------------张三 5000 10李四 250 20
同时,受策略保护的表若被drop是不进recyclebin,也就无法用flashback ... to before drop。
zhangsan@ORCL> conn scott/tigerConnected.scott@ORCL> show recyclebinscott@ORCL> drop table tvpd;Table dropped.scott@ORCL> show recyclebinscott@ORCL> flashback table tvpd to before drop;flashback table tvpd to before drop*ERROR at line 1:ORA-38305: object not in RECYCLE BIN
- 动手操作oracle细粒度访问控制(FGAC)一则
- oracle FGAC(细粒度访问控制)介绍
- Oracle VPD实现数据细粒度访问(更好的权限控制)
- Oracle 细粒度审计监控表操作记录
- Oracle 细粒度审计(FGA)之DBMS_FGA包的实验案例一则
- Oracle FGAC与FGA的区别
- 权限控制案例(细粒度)
- 数据库细粒度访问
- oracle的细粒度审计
- oracle 细粒度审计使用
- Oracle FGA细粒度审计
- 在云计算中实现安全、可伸缩、细粒度数据的访问控制
- 强叔侃墙 VPN篇 URL地址隐藏改写各千秋,细粒度资源访问控制有方法
- Oracle数据库访问控制
- SVN目录权限细粒度控制
- oracle 细粒度审计的开启
- 应用ORACLE 10G以上细粒度审计功能实现操作审计
- synchronized 同步操作应该是细粒度
- 《面向对象程序设计》第12章在线测试
- sgu 127 Telephone directory
- LAMP基础环境搭建
- CenterOS 6.3 64位mysql5.5.25安装及配置四部曲
- 测试MBP的手写功能
- 动手操作oracle细粒度访问控制(FGAC)一则
- MFC下全屏播放flash
- APK反破解之一:Android Java混淆(ProGuard)
- PV,EV和AC
- HDU 4445 Crazy Tank 三分+枚举+二分
- APK反破解之二:Android APK 签名比对
- 《面向对象程序设计》第07章在线测试
- 《面向对象程序设计》第11章在线测试
- OpenGL 4.3 (Core Profile) - August 6, 2012 spec Fundamental 3 Dataflow Model