使用DBV的命令规则和规则集强化数据库安全

几个场景示例：
1. 指定时间维护数据库
2. 指定不能直接使用DML命令，而必须调用指定存储过程
3. 指定必须使用密码认证方式（而不是OS认证）连接数据库
4. 指定必须从指定客户端IP连接数据库
5. 将做指定动作的尝试记录下来
6. 结合业务数据制定访问规则

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

点击(此处)折叠或打开

1. --创建规则
2. 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;
   /
   
3. --创建规则集
4. 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;
   /
5. --将规则加入规则集，可以将多个规则加入规则集
6. BEGIN
      dbms_macadm.add_rule_to_rule_set (
        rule_set_name => 'Is System Maintenance Allowed'
      , rule_name     => 'Is Maintenance Timeframe'
      );
   END;
   /
   
7. --创建命令规则，其中指定了规则集
8. BEGIN
     dbms_macadm.create_command_rule (
       command       =>  'DROP TABLE'
       ,rule_set_name => 'Is System Maintenance Allowed'
       ,object_owner  => 'SOE'
       ,object_name   => '%'
       ,enabled       => 'Y'
   );
   END;
   /
   

验证：

点击(此处)折叠或打开

1. 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. 只能调用存储过程来更新表数据
创建存储过程

点击(此处)折叠或打开

1. CREATE OR REPLACE PACKAGE soe.cards_transaction is
   
2.     PROCEDURE disable_card(card_no in PLS_INTEGER);
   
3. END cards_transaction; --没有schema名soe
   
4. /
   
5. 
   
6. CREATE OR REPLACE PACKAGE BODY soe.cards_transaction AS
   
7.     PROCEDURE disable_card(card_no in PLS_INTEGER) IS
   
8.     BEGIN
   
9.         -- show the output of the DBMS_UTILITY function
   
10.         DBMS_OUTPUT.PUT_LINE ( DBMS_UTILITY.FORMAT_CALL_STACK );
    
11.         -- perform the SQL UPDATE
    
12.         UPDATE soe.card_details SET is_valid = 'N'
    
13.             WHERE card_number = card_no;
    
14.     END;
    
15. END cards_transaction; --没有schema名soe
    
16. /

验证存储过程

点击(此处)折叠或打开

1. set serverout on
   
2. begin
   
3.   cards_transaction.disable_card(
   
4.     card_no => 1374047923
   
5.   );
   
6. end;
   
7. /

创建规则，验证是否使用了存储过程

点击(此处)折叠或打开

1. --创建规则
2. 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;
   /
3. --创建规则集
   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;
   /
   
   
4. --将规则加入
5. BEGIN
      dbms_macadm.add_rule_to_rule_set (
        rule_set_name => 'Using Cards Application'
      , rule_name     => 'Called From Cards Transaction Package'
      );
   END;
   /
6. --创建命令规则
7. 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命令：

点击(此处)折叠或打开

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

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

用存储过程

点击(此处)折叠或打开

1. begin
   
2.   cards_transaction.disable_card(
   
3.     card_no => 1374047923
   
4.   );
   
5. end;
   
6. /

PL/SQL procedure successfully completed.

---

示例3. 指定必须使用密码认证方式（而不是OS认证）连接数据库

点击(此处)折叠或打开

1. --使用密码认证连接数据库，其他方式还有OS, SSL
2. BEGIN
        dbms_macadm.create_rule(
            rule_name => 'Is Secure Authentication Method'
          , rule_expr => 'SYS_CONTEXT(''USERENV'',
              ''AUTHENTICATION_METHOD'') IN (''PASSWORD'')'
        );
   END;
   /
3. 
   
4. --客户端IP，如果是OS登录，IP为IS NULL
5. BEGIN
        dbms_macadm.create_rule(
            rule_name => 'Is Console Client'
          , rule_expr =>
   'SYS_CONTEXT(''USERENV'', ''IP_ADDRESS'') = ''192.168.226.31'''
        );
   END;
   /
   
   
6. --使用默认创建的规则集Allow Sessions，因此只要update
7. 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;
   /
8. --将规则加入规则集
9. 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;
   /
   
10. --加入CONNECT命令规则
11. BEGIN
      dbms_macadm.create_command_rule (
        command       =>  'CONNECT'
        ,rule_set_name => 'Allow Sessions'
        ,object_owner  => '%'
        ,object_name   => '%'
        ,enabled       => 'Y'
    );
    END;
    /
    
    
    
    

验证：使用oracle的系统用户以OS方式连接数据库，此时应该无法连接上

点击(此处)折叠或打开

1. [oracle@vault ~]$ sqlplus / as sysdba
   
2. 
   
3. SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 12 15:47:37 2015
   
4. 
   
5. Copyright (c) 1982, 2013, Oracle. All rights reserved.
   
6. 
   
7. Connected.
   
8. SQL> show user
   
9. USER is "SYS"
   
10. SQL> desc v$instance;
    
11. ERROR:
    
12. ORA-01012: not logged on
    
13. 
    
14. 
    
15. SQL> connect / as sysdba
    
16. Connected to an idle instance.
    
17. SQL> exit
    
18. Disconnected

可以在规则集的选项
eval_options    => dbms_macutl.g_ruleset_eval_all,
指定多个规则要ALL TRUE或ANY TRUE

---

示例5. 使用自定义过程处理程序

以下摘抄原书：

点击(此处)折叠或打开

1. 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. 使用验证函数确认是否允许执行命令
摘抄原书：

点击(此处)折叠或打开

1. --以下函数表示只有在周六、周日的8-16点才能验证通过。实际业务环境中，可以使用业务数据、函数来自定义
2. 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.
3. --
4. 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.
   
   

查询规则集

点击(此处)折叠或打开

1. dbvowner@aos>SELECT name,rule_expr
   
2. from dvsys.dv$rule
   
3. 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22621861/viewspace-1396261/