1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle database vault query

Discussion in 'General' started by sbtraj, Oct 11, 2013.

  1. sbtraj

    sbtraj Guest

    Hi,

    I have been asked to test if below scenario is possible in Database Vault

    Scenario : I have a database called ORCL (11.2.0.3) hosted on a server db1.abc.com having IP address 192.168.10.10. I have a database user TEST which has a table say TBL_SENSITIVE_DATA, which hold sensitive data. I used Database Vault to protect this table from DBA etc.,
    There are number of application servers say appserver1.abc.com (192.168.20.20), appserver2.abc.com etc(192.168.30.30) which run JBoss applicaiton to the database server.

    I'm using web browser to administer database vault from client machine say dbaclient.abc.com 150.150.15.15.

    My requirement :
    1) Database connection to TEST user should be allowed ONLY from application servers (192.168.20.20 & 192.168.30.30). SQL*Pluse, SQL Developer tools etc should not be able to access from application servers.
    2) SYS, SYSTEM, DBVOWNER, DBVACCTMGR users should be accessible from any client within the IP range from 150.150.15.0 to 150.150.15.255. The client can use SQL*PLUS, SQL Developer, toad etc tools to connect to the database and DBVOWNER uses URL to access Vault.

    Is is possible to acheive this using Database Vault ONLY? If yes, how.

    Any replies much appreciated.

    Cheers,
     
  2. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    To enable connection on db ONLY from specified client IP Address

    You need to create the Rule using this Expression:

    UPPER(SYS_CONTEXT('USERENV','IP_ADDRESS'))='192.168.20.20'



    Code (SQL):

    BEGIN
    DVSYS.DBMS_MACADM.DELETE_COMMAND_RULE(command => 'CONNECT',object_owner => '%',object_name => '%');
    END;
    /


    BEGIN
    DVSYS.DBMS_MACADM.DELETE_RULE_SET(rule_set_name => 'RULE_SET_U1');
    END;
    /

    BEGIN
    DVSYS.DBMS_MACADM.DELETE_RULE(rule_name => 'Rule_U1');
    END;
    /


    BEGIN
    DVSYS.DBMS_MACADM.CREATE_RULE_SET(
    rule_set_name => 'RULE_SET_U1',
    description => 'Rule Set enabled for the U1 user',
    enabled => DVSYS.DBMS_MACUTL.G_YES,
    eval_options => DBMS_MACUTL.G_RULESET_EVAL_ALL, -- all rules must be true,
    audit_options => DBMS_MACUTL.G_RULESET_AUDIT_OFF, -- no audit
    fail_options => DBMS_MACUTL.G_RULESET_FAIL_SILENT,
    fail_message => '',
    fail_code => NULL,
    handler_options => DBMS_MACUTL.G_RULESET_HANDLER_OFF,
    handler => NULL
    );
    END;
    /





    BEGIN
    DVSYS.DBMS_MACADM.CREATE_RULE(
    rule_name => 'Rule_U1',
    rule_expr => ' UPPER(SYS_CONTEXT(''USERENV'',''IP_ADDRESS''))=''192.168.20.20''or UPPER(SYS_CONTEXT(''USERENV'',''IP_ADDRESS'')) is null'

    );
    END;
    /
    --UPPER(SYS_CONTEXT(''USERENV'',''IP_ADDRESS''))=''192.168.20.20' to allow only this IP Address to connect to the DB.
    --is null is used to allow local connection.

    -- Add rule to rule set.

    BEGIN
    DBMS_MACADM.ADD_RULE_TO_RULE_SET(
    rule_set_name => 'RULE_SET_U1',
    rule_name => 'Rule_U1'
    );
    END;
    /

    BEGIN
    DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
    command => 'CONNECT',
    rule_set_name => 'RULE_SET_U1',
    object_owner => '%',
    object_name => '%',
    enabled => DVSYS.DBMS_MACUTL.G_YES
    );
    END;
    /
     


    How To Allow Some SYSDBA Users To Connect And Ban Others

    The rule should check the identity of the user. This is provided via the AUTHENTICATED_IDENTITY userenv variable:-

    Code (SQL):


    SQL> conn / AS sysdba
    Connected.
    SQL> SELECT SYS_CONTEXT ('USERENV', 'AUTHENTICATED_IDENTITY') FROM dual;

    SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
    --------------------------------------------------------------------------------
    oracle

    SQL> CREATE USER test IDENTIFIED BY test;

    USER created.

    SQL> GRANT CONNECT, sysdba TO test;

    GRANT succeeded.

    SQL> conn test/test AS sysdba
    Connected.
    SQL> SELECT SYS_CONTEXT ('USERENV', 'AUTHENTICATED_IDENTITY') FROM dual;

    SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
    --------------------------------------------------------------------------------
    TEST

    SQL>
    SQL> conn sys/a AS sysdba
    Connected.
    SQL> SELECT SYS_CONTEXT ('USERENV', 'AUTHENTICATED_IDENTITY') FROM dual;

    SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
    --------------------------------------------------------------------------------
    SYS


    --If one wants to ban "/ as sysdba" and "sys/passwd as sysdba" connections but wants to allow "test/test as sysdba" connections the following Database Vault objects can be created, logon with the DV_OWNER account and issue:

    EXEC DVSYS.DBMS_MACADM.CREATE_RULE_SET -
    ( rule_set_name => 'check_sysdba_user', -
    description => 'for_test_rule_set', -
    enabled => 'YES', -
    eval_options => '2', -
    audit_options => POWER(2,0), -
    fail_options => 1, -
    fail_message => NULL, -
    fail_code => NULL, -
    handler_options => 0, -
    handler => NULL );


    EXEC DBMS_MACADM.CREATE_RULE -
    (rule_name=>'check_sysdba_user2', -
    rule_expr => 'UPPER(SYS_CONTEXT (''USERENV'', ''AUTHENTICATED_IDENTITY'')) not in (''SYS'',''ORACLE'')');

    EXEC DBMS_MACADM.ADD_RULE_TO_RULE_SET -
    (rule_set_name=>'check_sysdba_user', -
    rule_name=>'check_sysdba_user2');

    EXEC DBMS_MACADM.CREATE_COMMAND_RULE -
    ( command => 'CONNECT', -
    rule_set_name => 'check_sysdba_user', -
    object_owner => '%', -
    object_name => '%', -
    enabled => 'Y');



    --When the SYSDBA users will try to connect this is what they will get :

    SQL> conn / AS sysdba
    Connected TO an idle instance.

    SQL> conn sys/a AS sysdba
    Connected TO an idle instance.

    SQL> conn test/test AS sysdba
    Connected.


    --If they try to connect from the command promt they will get:

    $ sqlplus "/ as sysdba"

    SQL*Plus: Release 10.2.0.4.0 - Production ON Thu Feb 4 10:39:33 2010

    Copyright (c) 1982, 2007, Oracle. ALL Rights Reserved.

    Connected.
    SQL> SELECT * FROM dual;
    SELECT * FROM dual
    *
    ERROR at line 1:
    ORA-01012: NOT logged ON

     

    DV rule are most of the time using SYS_CONTEXT and userenv, you can find the possible Parameter here:
    http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions184.htm#g1513460

    Best Regards,
    Prashant
     
  3. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Unfortunately using SYS_CONTEXT and userenv, it is not possible to detect the program used, so you can use a after logon trigger to achieve the same. The PROGRAM column in V$SESSION can be used to discriminate between allowed and disallowed tools, if appropriately set.

    Sample Script to create the trigger under SYS user to forbid access by TOAD:
    ---------------------------------------------------------------------------

    Code (SQL):
     CREATE OR REPLACE TRIGGER ban_toad after logon ON DATABASE
        DECLARE
         v_sid NUMBER;
         v_isdba varchar2(10);
         v_program varchar2(30);
        BEGIN
         EXECUTE immediate
           'select distinct sid from sys.v_$mystat' INTO v_sid;
         EXECUTE immediate
           'select program from sys.v_$session where sid = :b1'
            INTO v_program USING v_sid;
         SELECT sys_context('userenv','ISDBA') INTO v_isdba FROM dual;
         IF UPPER(v_program) = 'TOAD.EXE' AND v_isdba = 'FALSE' THEN
              raise_application_error
                (-20001,'TOAD Access for non DBA users restricted',TRUE);
         END  IF;
        END;
       /        

       Example
       -------

          SQL> conn scott/tiger
          ERROR:
          ORA-00604: error occurred at recursive SQL level 1
          ORA-20001: TOAD Access FOR non DBA users restricted
          ORA-06512: at line 13

          Warning: You are no longer connected TO ORACLE.
     
    Note that TOAD populates the MODULE column of V$SESSION :

    Code (SQL):
      SQL> SELECT username, module FROM v$session WHERE UPPER(program) = 'TOAD.EXE';

       USERNAME                       MODULE
       ------------------------------ ---------------------------------------
       SYSTEM                         TOAD 8.0.0.47
     
    However, these are only populated after the logon trigger fires. It cannot be used inside the trigger but later in V$SESSION to detect rogue clients.

    Best Regards,
    Prashant