Oracle Database Vault(マルチテナント)

(19c)
-- 前提: パスワードファイル作成済
orapwd file=orapworcl password=oracle format=12

-- 1. インストール(CDB)

conn / as sysdba

@$ORACLE_HOME/rdbms/admin/catols.sql
@$ORACLE_HOME/rdbms/admin/catmac.sql SYSAUX TEMP

-- 2. 初期設定(CDB)
conn / as sysdba

GRANT CREATE SESSION, SET CONTAINER TO c##dbv_owner IDENTIFIED BY oracle CONTAINER = ALL;
GRANT CREATE SESSION, SET CONTAINER TO c##dbv_owner_backup IDENTIFIED BY oracle CONTAINER = ALL;
GRANT CREATE SESSION, SET CONTAINER TO c##dbv_acctmgr IDENTIFIED BY oracle CONTAINER = ALL;
GRANT CREATE SESSION, SET CONTAINER TO c##dbv_acctmgr_backup IDENTIFIED BY oracle CONTAINER = ALL;

BEGIN
 CONFIGURE_DV (
   dvowner_uname         => 'c##dbv_owner',
   dvacctmgr_uname       => 'c##dbv_acctmgr',
   force_local_dvowner   => FALSE);
 END;
/

-- 無効化されたオブジェクトを再コンパイル

@?/rdbms/admin/utlrp.sql

-- 関連するすべてのPDBがこのデータベースでDatabase Vaultを有効にする

conn c##dbv_owner/oracle
EXEC DBMS_MACADM.ENABLE_DV (strict_mode => 'y');

conn / as sysdba
SHUTDOWN IMMEDIATE
STARTUP

SELECT * FROM SYS.DBA_DV_STATUS;
SELECT * FROM DBA_OLS_STATUS;

conn c##dbv_owner/oracle

GRANT DV_OWNER TO c##dbv_owner_backup WITH ADMIN OPTION CONTAINER=ALL;

conn c##dbv_acctmgr/oracle

GRANT DV_ACCTMGR TO c##dbv_acctmgr_backup WITH ADMIN OPTION CONTAINER=ALL;

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

-- 3. インストール(PDB)
CONNECT SYS@pdb1 AS SYSDBA

@$ORACLE_HOME/rdbms/admin/catols.sql

@$ORACLE_HOME/rdbms/admin/catmac.sql SYSAUX TEMP

-- 4. 初期設定(PDB)

CONNECT SYS@pdb1 AS SYSDBA

GRANT CREATE SESSION, SET CONTAINER,RESTRICTED SESSION TO dbv_owner IDENTIFIED BY oracle;
GRANT CREATE SESSION, SET CONTAINER,RESTRICTED SESSION TO dbv_owner_backup IDENTIFIED BY oracle;
GRANT CREATE SESSION, SET CONTAINER,RESTRICTED SESSION TO dbv_acctmgr IDENTIFIED BY oracle;
GRANT CREATE SESSION, SET CONTAINER,RESTRICTED SESSION TO dbv_acctmgr_backup IDENTIFIED BY oracle;

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

-- 無効化されたオブジェクトを再コンパイル

@?/rdbms/admin/utlrp.sql

conn dbv_owner/oracle@pdb1

EXEC DBMS_MACADM.ENABLE_DV;

conn / as sysdba
ALTER PLUGGABLE DATABASE pdb1 CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE pdb1 OPEN;

CONNECT SYS@pdb1 AS SYSDBA

SELECT * FROM DBA_DV_STATUS;

conn dbv_owner/oracle@pdb1

GRANT DV_OWNER TO dbv_owner_backup WITH ADMIN OPTION;

conn dbv_acctmgr/oracle@pdb1

GRANT DV_ACCTMGR TO dbv_acctmgr_backup WITH ADMIN OPTION;

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

-- 5. 動作確認

-- テスト用ユーザ作成
conn dbv_acctmgr/oracle@pdb1

create user user1 identified by user1;
create user user2 identified by user2;
create user user3 identified by user3;

CONNECT SYS@pdb1 AS SYSDBA

grant dba to user1;
grant dba to user2;
grant dba to user3;

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

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

-- レルムの作成

conn dbv_owner/oracle@pdb1

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 where NAME='realm1';
select * from DVSYS.DBA_DV_REALM_OBJECT where REALM_NAME='realm1';
select * from DVSYS.DBA_DV_REALM_AUTH where REALM_NAME='realm1';

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

conn user1/user1@pdb1
select * from user1.tab1;

conn user2/user2@pdb1
select * from user1.tab1;

conn user3/user3@pdb1
select * from user1.tab1;

-- レルムの削除

conn dbv_owner/oracle@pdb1

BEGIN
 DBMS_MACADM.DELETE_REALM(
  realm_name    => 'realm1');
END; 
/

select * from DVSYS.DBA_DV_REALM where NAME='realm1';
select * from DVSYS.DBA_DV_REALM_OBJECT where REALM_NAME='realm1';
select * from DVSYS.DBA_DV_REALM_AUTH where REALM_NAME='realm1';