Oracle Fine-Grained Access Control Lab
来源:互联网 发布:闽江学院网络教学平台 编辑:程序博客网 时间:2024/05/19 16:06
本文根据Oracle官方文档整理,不到之处还望指出。
---------------------------------------------------------------------
Oracle精细化访问控制实验
一 介绍
1.、强制用户只能查询属于“他们”的数据。
2、通过在指定表上增加安全策略实现。
3、通过动态修改用户查询语句以达到与预定义策略一致。
下图很好的诠释了其原理:
二 实现过程
1. 建立application context。
SQL> conn sys/admin as sysdba已连接。SQL> create context sales_orders_ctx 2 using oe.sales_orders_pkg;上下文已创建。
2.创建与context对应的package。
SQL> conn oe/oe已连接。SQL> CREATE OR REPLACE PACKAGE sales_orders_pkg 2 IS 3 PROCEDURE set_app_context; 4 FUNCTION the_predicate 5 (p_schema VARCHAR2, p_name VARCHAR2) 6 RETURN VARCHAR2; 7 END sales_orders_pkg; -- package spec 8 /程序包已创建。SQL> CREATE OR REPLACE PACKAGE BODY sales_orders_pkg 2 IS 3 c_context CONSTANT VARCHAR2(30) := 'SALES_ORDERS_CTX'; 4 c_attrib CONSTANT VARCHAR2(30) := 'SALES_REP'; 5 6 PROCEDURE set_app_context 7 IS 8 v_user VARCHAR2(30); 9 BEGIN 10 SELECT user INTO v_user FROM dual; 11 DBMS_SESSION.SET_CONTEXT 12 (c_context, c_attrib, v_user); 13 END set_app_context; 14 15 FUNCTION the_predicate 16 (p_schema VARCHAR2, p_name VARCHAR2) 17 RETURN VARCHAR2 18 IS 19 v_context_value VARCHAR2(100) := 20 SYS_CONTEXT(c_context, c_attrib); 21 v_restriction VARCHAR2(2000); 22 BEGIN 23 IF v_context_value LIKE 'SR%' THEN 24 v_restriction := 25 'SALES_REP_ID = 26 SUBSTR(''' || v_context_value || ''', 3, 3)'; 27 ELSE 28 v_restriction := null; 29 END IF; 30 RETURN v_restriction; 31 END the_predicate; 32 33 END sales_orders_pkg; -- package body 34 /程序包主体已创建。
3.定义策略。
SQL> conn sys/admin as sysdba已连接。SQL> DECLARE 2 BEGIN 3 DBMS_RLS.ADD_POLICY ( 4 'OE', 5 'ORDERS', 6 'OE_ORDERS_ACCESS_POLICY', 7 'OE', 8 'SALES_ORDERS_PKG.THE_PREDICATE', 9 'SELECT, INSERT, UPDATE, DELETE', 10 FALSE, 11 TRUE); 12 END; 13 /
4.创建登录触发器。
SQL> CREATE OR REPLACE TRIGGER set_id_on_logon 2 AFTER logon on DATABASE 3 BEGIN 4 oe.sales_orders_pkg.set_app_context; 5 END; 6 /触发器已创建
5.测试策略。
SQL> conn / as sysdba已连接。SQL> create user SR153 identified by oracle;用户已创建SQL> create user SR154 identified by oracle;用户已创建SQL> grant create session to SR153;授权成功。SQL> grant create session to SR154;授权成功。SQL> conn oe/oe已连接。SQL> grant select, update, delete on orders to SR153,SR154;授权成功。SQL> CONNECT SR153/oracle已连接。SQL> SELECT sales_rep_id, COUNT(*) 2 FROM oe.orders 3 GROUP BY sales_rep_id;
SALES_REP_ID COUNT(*)------------ ---------- 153 5
CONNECT SR154/oracle已连接。SQL> SELECT sales_rep_id, COUNT(*) 2 FROM oe.orders 3 GROUP BY sales_rep_id;SALES_REP_ID COUNT(*)------------ ---------- 154 10
----------------------------
present by dylan.
- Oracle Fine-Grained Access Control Lab
- (转)Fine-Grained Access to Network Services in Oracle Database 11g Release 1
- fine-grained & coarse grained
- (转)oracle审计—精细审计(Fine-Grained Audit)
- Oracle FGA(Fine-Grained Audit)细粒度审计的用法
- Fine-Grained Crowdsourcing for Fine-Grained Recognition(精读)
- OCP-042 fine-grained Auditing
- 粗粒度(Coarse-grained)vs细粒度(fine-grained)
- BTS PenTesting Lab - A7 Missing Function Level Access Control
- Fine-Grained Classification之车型识别
- Oracle ACL(Access Control List)
- Oracle ACL(Access Control List)
- Oracle ACL(Access Control List)
- ORA-39181:Only Partial Table Data Exported Due To Fine Grain Access Control [ID 422480.1]
- expdp ORA-39181:Only Partial Table Data Exported Due To Fine Grain Access Control
- Fine-Grained Auditing test (精细审计:FGA测试)
- Learning Fine-grained Image Similarity with Deep Ranking(泛读)
- Part-based R-CNNs for Fine-grained Category Detection(精读)
- 网络流题目
- UCOS 硬件初始化注意
- presence_privacy--FS
- hdu 3062 2-SAT
- 睡觉,吃饭,打豆豆,任务一
- Oracle Fine-Grained Access Control Lab
- sizeof完整解析
- linux学习篇4---关于目录配置 管理 及一些简单指令
- 《Head First设计模式》要点(六)
- JRE(version: 1.6.0_26-b03)的JVM自动挂掉
- 如何防止DNS被修改
- 排序小结
- 吃饭,睡觉,打豆豆任务二
- 寻找无序数组中的第K大数和前K大数