Oracle Database Vault

https://docs.oracle.com/cd/E57425_01/121/DVADM/toc.htm

(12cR1)

-- インストール

conn / as sysdba
@/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catols.sql
@/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catmac.sql SYSAUX TEMP oracle


-- 初期設定
conn / as sysdba

GRANT CREATE SESSION TO dbv_owner IDENTIFIED BY oracle;
GRANT CREATE SESSION TO dbv_owner_backup IDENTIFIED BY oracle;
GRANT CREATE SESSION TO dbv_acctmgr IDENTIFIED BY oracle;
GRANT CREATE SESSION TO dbv_acctmgr_backup IDENTIFIED BY oracle;


BEGIN
DVSYS.CONFIGURE_DV (
dvowner_uname => 'dbv_owner',
dvacctmgr_uname => 'dbv_acctmgr');
END;
/

@?/rdbms/admin/utlrp.sql


conn dbv_owner/oracle
EXEC DBMS_MACADM.ENABLE_DV;

conn / as sysdba
SHUTDOWN IMMEDIATE
STARTUP

conn dbv_owner/oracle

GRANT DV_OWNER TO dbv_owner_backup;

conn dbv_acctmgr/oracle

GRANT DV_ACCTMGR TO dbv_acctmgr_backup;

SELECT PARAMETER,VALUE FROM V$OPTION WHERE PARAMETER in ( 'Oracle Label Security','Oracle Database Vault' );

-- テスト用ユーザ作成
conn dbv_acctmgr/oracle
create user user1 identified by user1;
create user user2 identified by user2;
create user user3 identified by user3;

conn / as sysdba
grant dba to user1;
grant dba to user2;
grant dba to user3;

conn user1/user1
create table tab1(col1 int);
insert into tab1 values(1);
commit;
select * from tab1;

conn user2/user2
create table tab2(col1 int);
insert into tab2 values(1);
commit;
select * from tab2;


-- レルムの作成

conn dbv_owner/oracle

BEGIN
DBMS_MACADM.CREATE_REALM(
realm_name => 'realm1',
description => 'realm1',
enabled => DBMS_MACUTL.G_YES,
audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL + DBMS_MACUTL.G_REALM_AUDIT_SUCCESS,
realm_type => 1);
END;
/

BEGIN
DBMS_MACADM.ADD_OBJECT_TO_REALM(
realm_name => 'realm1',
object_owner => 'user1',
object_name => '%',
object_type => '%');
END;
/

BEGIN
DBMS_MACADM.ADD_AUTH_TO_REALM(
realm_name => 'realm1',
grantee => 'user1',
auth_options => DBMS_MACUTL.G_REALM_AUTH_OWNER);
END;
/

BEGIN
DBMS_MACADM.ADD_AUTH_TO_REALM(
realm_name => 'realm1',
grantee => 'user2',
auth_options => DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT);
END;
/

select * from DVSYS.DBA_DV_REALM order by NAME;
select * from DVSYS.DBA_DV_REALM_OBJECT order by REALM_NAME,OBJECT_NAME;
select * from DVSYS.DBA_DV_REALM_AUTH order by REALM_NAME,GRANTEE;

-- 動作確認
user1,user2はuser1.tab1を参照できるが、user3は参照できない

 

□二人制整合性(デュアル・キー・セキュリティ)の構成

conn / as sysdba

GRANT CREATE PROCEDURE TO dbv_owner;
GRANT SELECT ON V_$SESSION TO dbv_owner;


conn dbv_owner/oracle


CREATE OR REPLACE FUNCTION fun1
return varchar2
authid definer as

v_session_number number := 0;
v_allow varchar2(10) := 'TRUE';
v_deny varchar2(10) := 'FALSE';

BEGIN
SELECT COUNT(*) INTO v_session_number
FROM SYS.V_$SESSION
WHERE USERNAME = 'USER1';

IF v_session_number > 0
THEN RETURN v_allow;
ELSE
RETURN v_deny;
END IF;
END fun1;
/

GRANT EXECUTE ON fun1 to DVSYS;

conn dbv_owner/oracle

BEGIN
DBMS_MACADM.CREATE_RULE(
rule_name => 'rule11',
rule_expr => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''USER3'' and dbv_owner.fun1 = ''TRUE'' ');
END;
/

BEGIN
DBMS_MACADM.CREATE_RULE(
rule_name => 'rule12',
rule_expr => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''USER3''');
END;
/


BEGIN
DBMS_MACADM.CREATE_RULE_SET(
rule_set_name => 'rule_set1',
description => 'rule_set1',
enabled => DBMS_MACUTL.G_YES,
eval_options => DBMS_MACUTL.G_RULESET_EVAL_ANY,
audit_options => DBMS_MACUTL.G_RULESET_AUDIT_FAIL + DBMS_MACUTL.G_RULESET_AUDIT_SUCCESS,
fail_options => DBMS_MACUTL.G_RULESET_FAIL_SILENT,
fail_message =>'',
fail_code => NULL,
handler_options => DBMS_MACUTL.G_RULESET_HANDLER_OFF,
handler => '',
is_static => FALSE
);
END;
/

BEGIN
DBMS_MACADM.ADD_RULE_TO_RULE_SET(
rule_set_name => 'rule_set1',
rule_name => 'rule11',
rule_order => 1,
enabled => DBMS_MACUTL.G_YES
);
END;
/

BEGIN
DBMS_MACADM.ADD_RULE_TO_RULE_SET(
rule_set_name => 'rule_set1',
rule_name => 'rule12',
rule_order => 1,
enabled => DBMS_MACUTL.G_YES
);
END;
/


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

select * from DVSYS.DBA_DV_RULE order by NAME;
select * from DVSYS.DBA_DV_RULE_SET order by RULE_SET_NAME;
select * from DVSYS.DBA_DV_RULE_SET_RULE order by RULE_SET_NAME,RULE_NAME;
select * from DVSYS.DBA_DV_COMMAND_RULE order by COMMAND;


-- 動作確認
USER3はUSER1がログインしている場合だけログインできる
USER1,USER2はいつでもログインできる


-- クリーンアップ
conn dbv_owner/oracle

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

BEGIN
DBMS_MACADM.DELETE_RULE_FROM_RULE_SET(
rule_set_name => 'rule_set1',
rule_name => 'rule11');
END;
/


BEGIN
DBMS_MACADM.DELETE_RULE_FROM_RULE_SET(
rule_set_name => 'rule_set1',
rule_name => 'rule12');
END;
/

BEGIN
DBMS_MACADM.DELETE_RULE_SET('rule_set1');
END;
/


BEGIN
DBMS_MACADM.DELETE_RULE('rule11');
END;
/


BEGIN
DBMS_MACADM.DELETE_RULE('rule12');
END;
/


commit;


□セッション・データに基づくユーザー・アクティビティの制限

conn dbv_owner/oracle


BEGIN
DBMS_MACADM.CREATE_FACTOR(
factor_name => 'factor1',
factor_type_name => 'Application',
description => 'factor1',
rule_set_name => NULL,
validate_expr => NULL,
get_expr => 'UPPER(SYS_CONTEXT(''USERENV'',''CLIENT_PROGRAM_NAME''))',
identify_by => DBMS_MACUTL.G_IDENTIFY_BY_METHOD,
labeled_by => DBMS_MACUTL.G_LABELED_BY_SELF,
eval_options => DBMS_MACUTL.G_EVAL_ON_SESSION,
audit_options => DBMS_MACUTL.G_AUDIT_ON_GET_ERROR,
fail_options => DBMS_MACUTL.G_FAIL_SILENTLY);
END;
/

BEGIN
DBMS_MACADM.CREATE_RULE_SET(
rule_set_name => 'rule_set2',
description => 'rule_set2',
enabled => DBMS_MACUTL.G_YES,
eval_options => DBMS_MACUTL.G_RULESET_EVAL_ANY,
audit_options => DBMS_MACUTL.G_RULESET_AUDIT_OFF,
fail_options => DBMS_MACUTL.G_RULESET_FAIL_SHOW,
fail_message => 'SQL*Plus access not allowed',
fail_code => 20461,
handler_options => DBMS_MACUTL.G_RULESET_HANDLER_OFF,
handler => NULL,
is_static => FALSE);
END;
/

BEGIN
DBMS_MACADM.CREATE_RULE(
rule_name => 'rule21',
rule_expr =>'UPPER(DVF.F$FACTOR1) != ''SQLPLUS@MMM050 (TNS V1-V3)'' AND DVF.F$SESSION_USER IN (''USER3'')');
END;
/


BEGIN
DBMS_MACADM.CREATE_RULE(
rule_name => 'rule22',
rule_expr =>'DVF.F$SESSION_USER NOT IN (''USER3'')');
END;
/


BEGIN
DBMS_MACADM.ADD_RULE_TO_RULE_SET(
rule_set_name => 'rule_set2',
rule_name => 'rule21',
rule_order => 1,
enabled => DBMS_MACUTL.G_YES
);
END;
/

BEGIN
DBMS_MACADM.ADD_RULE_TO_RULE_SET(
rule_set_name => 'rule_set2',
rule_name => 'rule22',
rule_order => 1,
enabled => DBMS_MACUTL.G_YES
);
END;
/

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

COMMIT;


select * from DVSYS.DBA_DV_RULE order by NAME;
select * from DVSYS.DBA_DV_RULE_SET order by RULE_SET_NAME;
select * from DVSYS.DBA_DV_RULE_SET_RULE order by RULE_SET_NAME,RULE_NAME;
select * from DVSYS.DBA_DV_COMMAND_RULE order by COMMAND;
select * from DVSYS.DBA_DV_FACTOR order by NAME;

 

-- 動作確認

USER3はSQL*PlusでログインできないがSQL Developerでログインできる
USER1,USER2はSQL*PlusでもSQL Developerでもログインできる

 

-- クリーンアップ
conn dbv_owner/oracle

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

BEGIN
DBMS_MACADM.DELETE_RULE_FROM_RULE_SET(
rule_set_name => 'rule_set2',
rule_name => 'rule21');
END;
/


BEGIN
DBMS_MACADM.DELETE_RULE_FROM_RULE_SET(
rule_set_name => 'rule_set2',
rule_name => 'rule22');
END;
/

BEGIN
DBMS_MACADM.DELETE_RULE_SET('rule_set2');
END;
/


BEGIN
DBMS_MACADM.DELETE_RULE('rule21');
END;
/


BEGIN
DBMS_MACADM.DELETE_RULE('rule22');
END;
/

BEGIN
DBMS_MACADM.DELETE_FACTOR('factor1');
END;
/


commit;

 

□セキュア・アプリケーション・ロールによるアクセス権限の付与

conn dbv_owner/oracle


BEGIN
DBMS_MACADM.CREATE_RULE_SET(
rule_set_name => 'rule_set3',
description => 'rule_set3',
enabled => DBMS_MACUTL.G_YES,
eval_options => DBMS_MACUTL.G_RULESET_EVAL_ALL,
audit_options => DBMS_MACUTL.G_RULESET_AUDIT_FAIL + DBMS_MACUTL.G_RULESET_AUDIT_SUCCESS,
fail_options => DBMS_MACUTL.G_RULESET_FAIL_SILENT,
fail_message =>'',
fail_code => NULL,
handler_options => DBMS_MACUTL.G_RULESET_HANDLER_OFF,
handler => '',
is_static => FALSE
);
END;
/

BEGIN
DBMS_MACADM.CREATE_RULE(
rule_name => 'rule31',
rule_expr =>'DVF.F$CLIENT_IP in (''192.168.137.50'') ');
END;
/


BEGIN
DBMS_MACADM.CREATE_RULE(
rule_name => 'rule32',
rule_expr =>'DVF.F$SESSION_USER IN (''USER3'')');
END;
/


BEGIN
DBMS_MACADM.ADD_RULE_TO_RULE_SET(
rule_set_name => 'rule_set3',
rule_name => 'rule31',
rule_order => 1,
enabled => DBMS_MACUTL.G_YES
);
END;
/

BEGIN
DBMS_MACADM.ADD_RULE_TO_RULE_SET(
rule_set_name => 'rule_set3',
rule_name => 'rule32',
rule_order => 1,
enabled => DBMS_MACUTL.G_YES
);
END;
/

BEGIN
DBMS_MACADM.CREATE_ROLE(
role_name => 'role1',
enabled => DBMS_MACUTL.G_YES,
rule_set_name => 'rule_set3');
END;
/
COMMIT;

conn user2/user2

grant select on tab2 to role1;

conn dbv_owner/oracle

select * from DVSYS.DBA_DV_RULE order by NAME;
select * from DVSYS.DBA_DV_RULE_SET order by RULE_SET_NAME;
select * from DVSYS.DBA_DV_RULE_SET_RULE order by RULE_SET_NAME,RULE_NAME;
select * from DVSYS.DBA_DV_ROLE order by ROLE;

 

-- 動作確認

EXEC DBMS_MACSEC_ROLES.SET_ROLE('role1');

USER3はUSER2.TAB2を参照可能


-- クリーンアップ

conn dbv_owner/oracle


BEGIN
DBMS_MACADM.DELETE_ROLE('role1');
END;
/


BEGIN
DBMS_MACADM.DELETE_RULE_FROM_RULE_SET(
rule_set_name => 'rule_set3',
rule_name => 'rule31');
END;
/


BEGIN
DBMS_MACADM.DELETE_RULE_FROM_RULE_SET(
rule_set_name => 'rule_set3',
rule_name => 'rule32');
END;
/

BEGIN
DBMS_MACADM.DELETE_RULE_SET('rule_set3');
END;
/


BEGIN
DBMS_MACADM.DELETE_RULE('rule31');
END;
/


BEGIN
DBMS_MACADM.DELETE_RULE('rule32');
END;
/

 

commit;

 

□レルムの削除
conn dbv_owner/oracle

BEGIN
DBMS_MACADM.DELETE_AUTH_FROM_REALM(
realm_name => 'realm1',
grantee => 'user1');
END;
/

BEGIN
DBMS_MACADM.DELETE_AUTH_FROM_REALM(
realm_name => 'realm1',
grantee => 'user2');
END;
/

BEGIN
DBMS_MACADM.DELETE_OBJECT_FROM_REALM(
realm_name => 'realm1',
object_owner => 'user1',
object_name => '%',
object_type => '%');
END;
/

BEGIN
DBMS_MACADM.DELETE_REALM('realm1');
END;
/

commit;


□Vaultの無効化

conn dbv_owner/oracle
EXEC DBMS_MACADM.DISABLE_DV;

conn / as sysdba
SHUTDOWN IMMEDIATE
STARTUP


SELECT PARAMETER,VALUE FROM V$OPTION WHERE PARAMETER in ( 'Oracle Label Security','Oracle Database Vault' );