使用DBV的命令規則和規則集強化資料庫安全

hooca發表於2015-01-09

幾個場景示例：
1. 指定時間維護資料庫
2. 指定不能直接使用DML命令，而必須呼叫指定儲存過程
3. 指定必須使用密碼認證方式（而不是OS認證）連線資料庫
4. 指定必須從指定客戶端IP連線資料庫
5. 將做指定動作的嘗試記錄下來
6. 結合業務資料制定訪問規則

示例1：只能在週五17-23點做DROP TABLE命令

點選(此處)摺疊或開啟

--建立規則
BEGIN
dbms_macadm.create_rule(
rule_name => 'Is Maintenance Timeframe'
, rule_expr => 'TRIM(TO_CHAR(SYSDATE,''DAY'')) = ''FRIDAY'' AND
TO_CHAR(SYSDATE,''HH24'') BETWEEN 17 AND 23'
);
END;
/
--建立規則集
BEGIN
dbms_macadm.create_rule_set(
rule_set_name => 'Is System Maintenance Allowed',
description =>
'Checks to determine if the system maintenance is allowed',
enabled =>dbms_macutl.g_yes,
eval_options =>dbms_macutl.g_ruleset_eval_all,
audit_options =>dbms_macutl.g_ruleset_audit_fail,
fail_options =>dbms_macutl.g_ruleset_fail_show,
fail_message =>NULL,
fail_code =>NULL,
handler_options =>dbms_macutl.g_ruleset_handler_off,
handler =>NULL);
END;
/
--將規則加入規則集，可以將多個規則加入規則集
BEGIN
dbms_macadm.add_rule_to_rule_set (
rule_set_name => 'Is System Maintenance Allowed'
, rule_name => 'Is Maintenance Timeframe'
);
END;
/
--建立命令規則，其中指定了規則集
BEGIN
dbms_macadm.create_command_rule (
command => 'DROP TABLE'
,rule_set_name => 'Is System Maintenance Allowed'
,object_owner => 'SOE'
,object_name => '%'
,enabled => 'Y'
);
END;
/

驗證：

點選(此處)摺疊或開啟

drop table soe.customers3;

drop table soe.customers3;
drop table soe.customers3
*
ERROR at line 1:
ORA-47400: Command Rule violation for DROP TABLE on SOE.CUSTOMERS3

示例2. 只能呼叫儲存過程來更新表資料
建立儲存過程

點選(此處)摺疊或開啟

CREATE OR REPLACE PACKAGE soe.cards_transaction is
PROCEDURE disable_card(card_no in PLS_INTEGER);
END cards_transaction; --沒有schema名soe
/
CREATE OR REPLACE PACKAGE BODY soe.cards_transaction AS
PROCEDURE disable_card(card_no in PLS_INTEGER) IS
BEGIN
-- show the output of the DBMS_UTILITY function
DBMS_OUTPUT.PUT_LINE ( DBMS_UTILITY.FORMAT_CALL_STACK );
-- perform the SQL UPDATE
UPDATE soe.card_details SET is_valid = 'N'
WHERE card_number = card_no;
END;
END cards_transaction; --沒有schema名soe
/

驗證儲存過程

點選(此處)摺疊或開啟

set serverout on
begin
cards_transaction.disable_card(
card_no => 1374047923
);
end;
/

建立規則，驗證是否使用了儲存過程

點選(此處)摺疊或開啟

--建立規則
BEGIN
dbms_macadm.create_rule(
rule_name => 'Called From Cards Transaction Package'
, rule_expr => 'INSTR(UPPER(DBMS_UTILITY.FORMAT_CALL_STACK),
''PACKAGE BODY SOE.CARDS_TRANSACTION'') > 0'
);
END;
/
--建立規則集
BEGIN
dbms_macadm.create_rule_set(
rule_set_name => 'Using Cards Application',
description => 'Checks to verify commands
are executed from trusted cards packages',
enabled => dbms_macutl.g_yes,
eval_options => dbms_macutl.g_ruleset_eval_all,
audit_options => dbms_macutl.g_ruleset_audit_fail,
fail_options => dbms_macutl.g_ruleset_fail_show,
fail_message => NULL,
fail_code => NULL,
handler_options => dbms_macutl.g_ruleset_handler_off,
handler => NULL
);
END;
/
--將規則加入
BEGIN
dbms_macadm.add_rule_to_rule_set (
rule_set_name => 'Using Cards Application'
, rule_name => 'Called From Cards Transaction Package'
);
END;
/
--建立命令規則
BEGIN
dbms_macadm.create_command_rule (
command => 'UPDATE'
,rule_set_name => 'Using Cards Application'
,object_owner => 'SOE'
,object_name => 'CARD_DETAILS'
,enabled => 'Y'
);
END;
/

驗證
直接用update命令：

點選(此處)摺疊或開啟

update card_details set is_valid='N' where card_number='1374047923';

*
ERROR at line 1:
ORA-01031: insufficient privileges

用儲存過程

點選(此處)摺疊或開啟

begin
cards_transaction.disable_card(
card_no => 1374047923
);
end;
/

PL/SQL procedure successfully completed.

示例3. 指定必須使用密碼認證方式（而不是OS認證）連線資料庫

點選(此處)摺疊或開啟

--使用密碼認證連線資料庫，其他方式還有OS, SSL
BEGIN
dbms_macadm.create_rule(
rule_name => 'Is Secure Authentication Method'
, rule_expr => 'SYS_CONTEXT(''USERENV'',
''AUTHENTICATION_METHOD'') IN (''PASSWORD'')'
);
END;
/
--客戶端IP，如果是OS登入，IP為IS NULL
BEGIN
dbms_macadm.create_rule(
rule_name => 'Is Console Client'
, rule_expr =>
'SYS_CONTEXT(''USERENV'', ''IP_ADDRESS'') = ''192.168.226.31'''
);
END;
/
--使用預設建立的規則集Allow Sessions，因此只要update
BEGIN
dbms_macadm.update_rule_set(
rule_set_name => 'Allow Sessions',
description => 'Rule set that controls the ability to create a
session in the database.',
enabled => dbms_macutl.g_yes,
eval_options => dbms_macutl.g_ruleset_eval_all,
audit_options => dbms_macutl.g_ruleset_audit_fail,
fail_options => dbms_macutl.g_ruleset_fail_show,
fail_message => NULL,
fail_code => NULL,
handler_options => dbms_macutl.g_ruleset_handler_off,
handler => NULL);
END;
/
--將規則加入規則集
BEGIN
dbms_macadm.add_rule_to_rule_set (
rule_set_name => 'Allow Sessions'
, rule_name => 'Is Secure Authentication Method'
);
END;
/
BEGIN
dbms_macadm.add_rule_to_rule_set (
rule_set_name => 'Allow Sessions'
, rule_name => 'Is Console Client'
);
END;
/
--加入CONNECT命令規則
BEGIN
dbms_macadm.create_command_rule (
command => 'CONNECT'
,rule_set_name => 'Allow Sessions'
,object_owner => '%'
,object_name => '%'
,enabled => 'Y'
);
END;
/

驗證：使用oracle的系統使用者以OS方式連線資料庫，此時應該無法連線上

點選(此處)摺疊或開啟

[oracle@vault ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 12 15:47:37 2015
Connected.
SQL> show user
USER is "SYS"
SQL> desc v$instance;
ERROR:
ORA-01012: not logged on
SQL> connect / as sysdba
Connected to an idle instance.
SQL> exit
Disconnected

可以在規則集的選項
eval_options => dbms_macutl.g_ruleset_eval_all,
指定多個規則要ALL TRUE或ANY TRUE

示例5. 使用自定義過程處理程式

以下摘抄原書：

點選(此處)摺疊或開啟

mary@aos> -- First create a table to hold the alerts
mary@aos> create table sh.alerts ( msg varchar2(4000)
, msgdate date default sysdate);
Table created.
mary@aos> -- next create a package to process the alerts
mary@aos> CREATE OR REPLACE package sh.sales_alerts as
PROCEDURE sales_update_alert(ruleset_name IN VARCHAR2,
ruleset_result IN VARCHAR2);
end;
/
Package created.
mary@aos> CREATE OR REPLACE PACKAGE BODY sh.sales_alerts AS
PROCEDURE sales_update_alert(ruleset_name IN VARCHAR2,
ruleset_result IN VARCHAR2) is
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT into sh.alerts (msg )
VALUES ('Alert for Rule Set:'
|| ruleset_name || ', result is ' || ruleset_result);
COMMOT;
END;
END;
/
Package created.
mary@aos> -- GRANT EXECUTE on the handler package to DVSYS
mary@aos> GRANT EXECUTE ON sh.sales_alerts TO dvsys;
Grant succeeded.
mary@aos> -- Update the rule set to use the handler package
mary@aos> -- on rule set failure (failed access attempt)
dbvowner@aos> BEGIN
dbms_macadm.update_rule_set(
rule_set_name =>'Using Financials Application',
description =>'Checks to verify commands came are
executed from trusted financials packages',
enabled =>dbms_macutl.g_yes,
eval_options =>dbms_macutl.g_ruleset_eval_all,
audit_options =>dbms_macutl.g_ruleset_audit_fail,
fail_options =>dbms_macutl.g_ruleset_fail_show,
fail_message =>NULL,
fail_code =>NULL,
handler_options =>dbms_macutl.g_ruleset_handler_fail,
handler =>'sh.sales_alerts.sales_update_alert'
);
END;
/
PL/SQL procedure successfully completed.
mary@aos> -- Attempt to update the table outside the policy
mary@aos> UPDATE sh.sales
SET amount_sold = 200
WHERE cust_id = 305;
UPDATE sh.sales
*
ERROR at line 1:
ORA-01031: insufficient privileges
mary@aos> -- View our alerts
mary@aos> SELECT * from sh.alerts;
MSG
----------------------------------------------------------------
MSGDATE
---------
Alert for Rule Set:Using Financials Application, result is FALSE
12-JUL-08

示例6. 使用驗證函式確認是否允許執行命令
摘抄原書：

點選(此處)摺疊或開啟

--以下函式表示只有在週六、週日的8-16點才能驗證通過。實際業務環境中，可以使用業務資料、函式來自定義
mary@aos> CREATE OR REPLACE FUNCTION sh.can_perform_sales_summary RETURN NUMBER AS
l_day VARCHAR2(10) := TRIM(TO_CHAR(SYSDATE,'DAY'));
l_hour VARCHAR2(2) := TO_CHAR(SYSDATE,'HH24');
BEGIN
-- allow sales summary logic to run on weekends or week nights
-- weekend
IF l_day IN ( 'SATURDAY', 'SUNDAY' ) THEN
RETURN 1;
-- weekday - business hours
ELSIF l_hour BETWEEN '08' AND '16' THEN
RETURN 0;
-- week night
ELSE
RETURN 1;
END IF;
END;
/
Function created.
mary@aos> GRANT EXECUTE ON sh.can_perform_sales_summary TO dvsys;
Grant succeeded.
--
dbvowner@aos> -- Create the DBV Rule
BEGIN
dbms_macadm.create_rule(
rule_name => 'Is Sales Summary Allowed'
, rule_expr => 'sh.can_perform_sales_summary = 1'
);
END;
/
PL/SQL procedure successfully completed.
dbvowner@aos> -- Create the DBV Rule Set
dbvowner@aos> BEGIN
dbms_macadm.create_rule_set(
rule_set_name =>'Can Execute Financials Summary',
description =>
'Checks to see if summary job for financials can be run.',
enabled =>dbms_macutl.g_yes,
eval_options =>dbms_macutl.g_ruleset_eval_all,
audit_options =>dbms_macutl.g_ruleset_audit_fail,
fail_options =>dbms_macutl.g_ruleset_fail_show,
fail_message =>NULL,
fail_code =>NULL,
handler_options =>dbms_macutl.g_ruleset_handler_off,
handler =>NULL);
END;
/
PL/SQL procedure successfully completed.
dbvowner@aos> -- Associate the DBV Rule to the DBV Rule Set
BEGIN
dbms_macadm.add_rule_to_rule_set (
rule_set_name => 'Can Execute Financials Summary'
, rule_name => 'Is Sales Summary Allowed'
);
END;
/
PL/SQL procedure successfully completed.
dbvowner@aos> BEGIN
dbms_macadm.create_command_rule (
command => 'EXECUTE'
,rule_set_name => 'Can Execute Financials Summary'
,object_owner => 'SH'
,object_name => 'SALES_SUMMARY.RUN_SUMMARY'
,enabled => 'Y'
);
END;
/
PL/SQL procedure successfully completed.

查詢規則集

點選(此處)摺疊或開啟

dbvowner@aos>SELECT name,rule_expr
from dvsys.dv$rule
WHERE id# = 5045;

NAME RULE_EXPR
------------------------- --------------------------------
Is Sales Summary Allowed sh.can_perform_sales_summary = 1
1 row selected.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22621861/viewspace-1396261/，如需轉載，請註明出處，否則將追究法律責任。

資料庫建庫時字符集和排序規則的選擇
2024-09-13
資料庫排序
java 規則引擎資料彙集
2017-07-28
Java
資料庫設計和基本備份規則
2007-04-01
資料庫
更改系統資料庫的排序規則
2010-05-02
資料庫排序
【MySQL】資料庫字元校對規則
2014-05-11
MySql資料庫字元
管理規則和基於規則的轉換——流
2010-08-18
11 個重要的資料庫設計規則
2012-04-12
資料庫
資料庫SQL開發命名規則 (轉)
2010-08-18
資料庫SQL
List資料多重規則排序
2017-05-03
排序
資料預處理規則
2015-01-06
Oracle索引的使用規則
2011-08-25
Oracle索引
SSIS 資料庫排序規則衝突問題
2009-03-16
資料庫排序
MySQL字符集和校對規則（Collation）
2017-09-04
MySql
快速整合和使用 drools 規則引擎
2024-04-09
tcpdump抓包規則命令大全
2014-04-30
TCP
主資料之編碼規則
2020-07-06
資料探勘之關聯規則
2018-01-03
Golang的方法集繼承規則示例
2016-11-11
Golang繼承
mysql的字符集校對規則
2010-03-08
MySql
react中的 Hook 使用規則
2024-09-10
ReactHook
資料庫中的正規化和反正規化詳解！
2021-08-16
資料庫
【java規則引擎】之規則引擎解釋
2017-01-08
Java
正確使用資料架構的五條規則 - infoworld
2021-12-22
架構
Makefile基本規則和原理
2019-03-03
正則匹配規則2
2024-04-19
資料的集合處理，有哪些規則？
2018-10-30
第2-4-4章規則引擎Drools規則屬性-業務規則管理系統-元件化-中臺
2022-11-25
元件化
使用IPSec策略和規則提升網路安全性（二）
2009-04-01
識別符號的命名規則和規範
2024-03-19
符號
使用DDD規格Specification模式構建資料驅動規則引擎 - jonblankenship
2020-03-25
模式
IT職場規則
2021-04-27
url規則
2024-04-28
1、Shell命令列書寫規則
2013-06-21
命令列
LINUX命令列書寫規則
2024-04-30
Linux命令列
C語言的本質（37）——makefile之隱含規則和模式規則
2014-07-24
C語言模式
安卓應用安全指南4.5.2使用SQLite規則書
2018-03-22
安卓SQLite
庫存批量規則(原創)
2007-02-10
Laravel的unique和exists驗證規則的優化
2019-02-28
Laravel優化

使用DBV的命令規則和規則集強化資料庫安全

相關文章