Database Vault step by step example....

Violation of following should be captured :-

1) CDR_ADMIN should be able to perform DDL only but not DML on CDR_16_12_2011
2) CDR_USR should be able to perform DML on CDR_16_12_2011
3) OTH_USR should not be able to perform DML on CDR_16_12_2011

Steps followed :-

create user CDR_ADMIN identified by CDR_ADMIN TEMPORARY TABLESPACE temp;
grant create session, create table, unlimited tablespace to CDR_ADMIN;

create user CDR_USR identified by CDR_USR TEMPORARY TABLESPACE temp;
grant create session, unlimited tablespace to CDR_USR;

create user OTH_USR identified by OTH_USR TEMPORARY TABLESPACE temp;
grant create session, unlimited tablespace to OTH_USR;

a) Prepare REALM :-

BEGIN
DVSYS.DBMS_MACADM.CREATE_REALM(
realm_name =>'TEST_CDR_REALM',
description =>'Realm to protect tables cdr_data_1, app_data_1 in CDR_ADMIN schema' ,
enabled =>DBMS_MACUTL.G_YES, --realm enabled
audit_options =>DBMS_MACUTL.G_REALM_AUDIT_FAIL); --audit enabled
END;
/

b) Add tables to above realm

BEGIN
DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM(
realm_name =>'TEST_CDR_REALM',
object_owner => 'CDR_ADMIN',
object_name => '%',
object_type =>'TABLE');
END;
/


c) Add users authorized to use the objects in the realm

BEGIN

DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(
   realm_name =>'TEST_CDR_REALM',
   grantee =>'CDR_USR',
   auth_options =>DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT);

-- set as participant

DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(
   realm_name =>'TEST_CDR_REALM',
   grantee =>'OTH_USR',
   auth_options =>DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT);

-- set as participant

DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(
   realm_name =>'TEST_CDR_REALM',
   grantee =>'CDR_ADMIN',
   auth_options =>DBMS_MACUTL.G_REALM_AUTH_OWNER);

-- set as owner, to be able to grant, revoke (only owners can grant, revoke on realm protected objects)

END;
/

d) Create rule set for CDR_USR

BEGIN

DVSYS.DBMS_MACADM.CREATE_RULE_SET(
rule_set_name => 'RULE_SET_CDR_USR',
description => 'Rule Set enabled for CDR_USR',
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_FAIL, -- 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;
/

e) create rule to check username CDR_USR

BEGIN
DVSYS.DBMS_MACADM.CREATE_RULE(
rule_name => 'Rule_CDR_USR',
rule_expr => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''CDR_USR'''
);
END;
/


f) Add rule to rule set

BEGIN
DBMS_MACADM.ADD_RULE_TO_RULE_SET(
rule_set_name => 'RULE_SET_CDR_USR',
rule_name => 'Rule_CDR_USR'
);
END;
/

g) Create command rules for SELECT, INSERT, UPDATE AND DELETE (DML for Tables) for CDR_USR :-

BEGIN
   DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
   command => 'SELECT',
   rule_set_name => 'RULE_SET_CDR_USR',
   object_owner => 'CDR_ADMIN',
   object_name => '%',
   enabled => DVSYS.DBMS_MACUTL.G_YES
);

DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
   command => 'INSERT',
   rule_set_name => 'RULE_SET_CDR_USR',
   object_owner => 'CDR_ADMIN',
   object_name => '%',
   enabled => DVSYS.DBMS_MACUTL.G_YES
);

DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
   command => 'UPDATE',
   rule_set_name => 'RULE_SET_CDR_USR',
   object_owner => 'CDR_ADMIN',
   object_name => '%',
   enabled => DVSYS.DBMS_MACUTL.G_YES
);

DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
   command => 'DELETE',
   rule_set_name => 'RULE_SET_CDR_USR',
   object_owner => 'CDR_ADMIN',
   object_name => '%',
   enabled => DVSYS.DBMS_MACUTL.G_YES
);

END;
/

h) Create rules for CREATE TABLE, ALTER TABLE, DROP TABLE AND TRUNCATE TABLE (DDL for Tables) for CDR_ADMIN :-

BEGIN
DVSYS.DBMS_MACADM.CREATE_RULE_SET(
rule_set_name => 'RULE_SET_CDR_ADMIN',
description => 'Rule Set enabled for the CDR_ADMIN 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_FAIL, -- 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;
/


i) Create rule for rule set - check if username is CDR_ADMIN

BEGIN
DVSYS.DBMS_MACADM.CREATE_RULE(
rule_name => 'Rule_CDR_ADMIN',
rule_expr => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''CDR_ADMIN'''
);
END;
/

j) Add rule to rule set.

BEGIN
DBMS_MACADM.ADD_RULE_TO_RULE_SET(
rule_set_name => 'RULE_SET_CDR_ADMIN',
rule_name => 'Rule_CDR_ADMIN'
);
END;
/

k) add command to rule set.

BEGIN
DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
command => 'CREATE TABLE',
rule_set_name => 'RULE_SET_CDR_ADMIN',
object_owner => 'CDR_ADMIN',
object_name => '%',
enabled => DVSYS.DBMS_MACUTL.G_YES
);
DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
command => 'ALTER TABLE',
rule_set_name => 'RULE_SET_CDR_ADMIN',
object_owner => 'CDR_ADMIN',
object_name => '%',
enabled => DVSYS.DBMS_MACUTL.G_YES
);
DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
command => 'DROP TABLE',
rule_set_name => 'RULE_SET_CDR_ADMIN',
object_owner => 'CDR_ADMIN',
object_name => '%',
enabled => DVSYS.DBMS_MACUTL.G_YES
);
DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
command => 'TRUNCATE TABLE',
rule_set_name => 'RULE_SET_CDR_ADMIN',
object_owner => 'CDR_ADMIN',
object_name => '%',
enabled => DVSYS.DBMS_MACUTL.G_YES
);
END;
/


set lines 132 pages 2300
col os_username format a15
col username format a15
col userhost format a15
col ACTION_COMMAND format a45
col audit_option format a10
alter session set nls_date_format = 'DD.MM.YYYY HH24:MI:SS';

select audit_option,os_username,username,userhost,timestamp,action_command from dvsys.audit_trail$;