本文主要是介绍Oracle LiveLabs实验：DB Security - Database Vault，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
概述
此实验申请地址在这里，时间为45分钟。
本实验也是DB Security Advanced研讨会的的第6个实验，即Lab 8。
实验帮助在这里。
本实验使用的数据库为19.13。
也推荐看一下这个实验Oracle Database Vault on an Autonomous Database，有简明的过程，特别是有创建用户的过程。
Introduction
本研讨会介绍了 Oracle Database Vault (DV) 的各种特性和功能。 它使用户有机会学习如何配置这些功能，以防止未经授权的特权用户访问敏感数据。
Task 1: Enable Database Vault
进入实验目录：
sudo su - oracle
cd $DBSEC_LABS/database-vault
首先在容器数据库 cdb1 中启用 Database Vault：
./dv_enable_on_cdb.sh
实际执行的命令和输出为：
==============================================================================Configure and Enable Database Vault for the container database CDB...
==============================================================================CON_NAME
------------------------------
CDB$ROOT-- . Show the DB Vault status
SQL> select * from dba_dv_status;NAME STATUS
------------------------- --------------------
DV_APP_PROTECTION NOT CONFIGURED
DV_CONFIGURE_STATUS FALSE
DV_ENABLE_STATUS FALSESQL> select a.name pdb_name, a.open_mode, b.name, b.statusfrom v$pdbs a, cdb_dv_status bwhere a.con_id = b.con_idorder by 1,2;PDB_NAME OPEN_MODE NAME STATUS
-------------------- -------------------- ------------------------- --------------------
PDB1 READ WRITE DV_APP_PROTECTION NOT CONFIGUREDREAD WRITE DV_ENABLE_STATUS FALSEREAD WRITE DV_CONFIGURE_STATUS FALSEPDB_NAME OPEN_MODE NAME STATUS
-------------------- -------------------- ------------------------- --------------------
PDB2 READ WRITE DV_APP_PROTECTION NOT CONFIGUREDREAD WRITE DV_ENABLE_STATUS FALSEREAD WRITE DV_CONFIGURE_STATUS FALSE6 rows selected.-- . Configure DB Vault
SQL>
BEGINDVSYS.CONFIGURE_DV (dvowner_uname => 'C##DVOWNER',dvacctmgr_uname => 'c##DVACCTMGR');END;
/PL/SQL procedure successfully completed.-- . Enable DB VaultCON_NAME
------------------------------
CDB$ROOT
USER is "C##DVOWNER"SQL> exec dvsys.dbms_macadm.enable_dv;
PL/SQL procedure successfully completed.. Reboot the DatabaseCON_NAME
------------------------------
CDB$ROOT
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.Total System Global Area 3674209872 bytes
Fixed Size 9141840 bytes
Variable Size 1996488704 bytes
Database Buffers 1660944384 bytes
Redo Buffers 7634944 bytes
Database mounted.
Database opened.-- . Show the DB Vault status
SQL> select * from dba_dv_status;NAME STATUS
------------------------- --------------------
DV_APP_PROTECTION NOT CONFIGURED
DV_CONFIGURE_STATUS TRUE
DV_ENABLE_STATUS TRUESQL> select a.name pdb_name, a.open_mode, b.name, b.statusfrom v$pdbs a, cdb_dv_status bwhere a.con_id = b.con_idorder by 1,2;PDB_NAME OPEN_MODE NAME STATUS
-------------------- -------------------- ------------------------- --------------------
PDB1 READ WRITE DV_APP_PROTECTION NOT CONFIGUREDREAD WRITE DV_ENABLE_STATUS FALSEREAD WRITE DV_CONFIGURE_STATUS FALSEPDB_NAME OPEN_MODE NAME STATUS
-------------------- -------------------- ------------------------- --------------------
PDB2 READ WRITE DV_APP_PROTECTION NOT CONFIGUREDREAD WRITE DV_ENABLE_STATUS FALSEREAD WRITE DV_CONFIGURE_STATUS FALSE6 rows selected.
接下来，在可插拔数据库上启用它。 目前，仅在 pdb1 上启用它：
./dv_enable_on_pdb.sh pdb1
此命令和上一个类似，只是连接到PDB中执行而已。输出为：
==============================================================================Configure and Enable Database Vault for the pluggable database ...
==============================================================================CON_NAME
------------------------------
CDB$ROOT. Show the DB Vault statusNAME STATUS
------------------------- --------------------
DV_APP_PROTECTION NOT CONFIGURED
DV_CONFIGURE_STATUS TRUE
DV_ENABLE_STATUS TRUEPDB_NAME OPEN_MODE NAME STATUS
-------------------- -------------------- ------------------------- --------------------
PDB1 READ WRITE DV_APP_PROTECTION NOT CONFIGUREDREAD WRITE DV_ENABLE_STATUS FALSEREAD WRITE DV_CONFIGURE_STATUS FALSEPDB_NAME OPEN_MODE NAME STATUS
-------------------- -------------------- ------------------------- --------------------
PDB2 READ WRITE DV_APP_PROTECTION NOT CONFIGUREDREAD WRITE DV_ENABLE_STATUS FALSEREAD WRITE DV_CONFIGURE_STATUS FALSE6 rows selected.. Connect to the pluggable database pdb1Session altered.CON_NAME
------------------------------
PDB1. Configure DB VaultPL/SQL procedure successfully completed.. Enable DB Vault
USER is "C##DVOWNER"PL/SQL procedure successfully completed.. Reboot the pluggable databaseCON_NAME
------------------------------
CDB$ROOTPluggable database altered.Pluggable database altered.. Show the DB Vault statusNAME STATUS
------------------------- --------------------
DV_APP_PROTECTION NOT CONFIGURED
DV_CONFIGURE_STATUS TRUE
DV_ENABLE_STATUS TRUEPDB_NAME OPEN_MODE NAME STATUS
-------------------- -------------------- ------------------------- --------------------
PDB1 READ WRITE DV_APP_PROTECTION NOT CONFIGUREDREAD WRITE DV_ENABLE_STATUS TRUEREAD WRITE DV_CONFIGURE_STATUS TRUEPDB_NAME OPEN_MODE NAME STATUS
-------------------- -------------------- ------------------------- --------------------
PDB2 READ WRITE DV_APP_PROTECTION NOT CONFIGUREDREAD WRITE DV_ENABLE_STATUS FALSEREAD WRITE DV_CONFIGURE_STATUS FALSE6 rows selected.
现在，在容器数据库和 pdb1 中启用了 Database Vault！
Task 2: Create a Simple Realm
在浏览器中启动Web应用：
- URL：http://<YOUR_DBSEC-LAB_VM_PUBLIC_IP>:8080/hr_prod_pdb1
- 用户名和口令：hradmin/Oracle123
- 单击Search Employee，然后单击Search
返回您的终端会话并运行命令以查看Schema中的数据：
./dv_query_employee_data.sh
执行的命令和输出如下：
==============================================================================Query on EMPLOYEESEARCH_PROD data...
==============================================================================
USER is "SYS"-- . Describe EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES from SYS user
SQL> desc employeesearch_prod.demo_hr_employees;Name Null? Type----------------------------------------------------------------------------------- -------- --------------------------------------------------------USERID NOT NULL NUMBER(4)FIRSTNAME NOT NULL VARCHAR2(25)LASTNAME NOT NULL VARCHAR2(35)EMAIL NOT NULL VARCHAR2(35)PHONEMOBILE VARCHAR2(15)PHONEFIX VARCHAR2(15)PHONEFAX VARCHAR2(15)EMPTYPE NOT NULL VARCHAR2(15)POSITION NOT NULL VARCHAR2(25)ISMANAGER NOT NULL NUMBER(1)MANAGERID NUMBER(4)DEPARTMENT NOT NULL VARCHAR2(15)CITY NOT NULL VARCHAR2(35)STARTDATE NOT NULL DATEENDDATE DATEACTIVE VARCHAR2(1)ORGANIZATION NOT NULL VARCHAR2(15)CREATIONDATE NOT NULL DATEMODIFICATIONDATE DATECOSTCENTER NUMBER(5)ISHEADOFDEPARTMENT NUMBER(1)DOB NOT NULL DATESSN VARCHAR2(15)SIN VARCHAR2(15)NINO VARCHAR2(15)ADDRESS_1 NOT NULL VARCHAR2(50)ADDRESS_2 VARCHAR2(35)STATE VARCHAR2(5)COUNTRY NOT NULL VARCHAR2(5)POSTAL_CODE NOT NULL VARCHAR2(15)CORPORATE_CARD VARCHAR2(25)CC_PIN NUMBER(4)CC_EXPIRE DATESALARY NUMBER(8,2)-- . Query EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES from SYS user
SQL> select userid, firstname, lastname, emptype, position, ssn, sin, ninofrom employeesearch_prod.demo_hr_employeeswhere rownum < 10;USERID FIRSTNAME LASTNAME EMPTYPE POSITION SSN SIN NINO
---------- ---------- ---------- --------- ---------------- ----------- ----------- -------------73 Craig Hunt Part-Time Administrator 102-20-499774 Fred Stewart Part-Time Project Manager MN 33 14 95 E75 Julie Reed Full-time Clerk 412-62-241776 Ruby James Full-time End-User 537-78-890277 Alice Harper Part-Time District Manager 170-042-12678 Marilyn Lee Part-Time District Manager 553-51-103179 Laura Ryan Full-time Project Manager 568-10-870980 William Elliott Full-time District Manager 787-89-228281 Martha Carpenter Full-time Administrator FZ 84 80 43 S9 rows selected.
现在，创建领域 PROTECT_EMPLOYEESEARCH_PROD 以保护 EMPLOYEESEARCH_PROD 模式中的对象免受恶意活动：
./dv_create_realm.sh
执行的命令和输出为：
==============================================================================Create the realm...
==============================================================================
USER is "C##DVOWNER"CON_NAME
------------------------------
PDB1-- . Show the current DV realm
SQL> select name, description, enabled from dba_dv_realm where id# >= 5000 order by 1;no rows selected-- . Create the "PROTECT_EMPLOYEESEARCH_PROD" DV realm
SQL>
beginDVSYS.DBMS_MACADM.CREATE_REALM(realm_name => 'PROTECT_EMPLOYEESEARCH_PROD',description => 'A mandatory realm to protect the EMPLOYEESEARCH_PROD schema.',enabled => DBMS_MACUTL.G_YES,audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL,realm_type => 1);
END;
/PL/SQL procedure successfully completed.-- . Show the current DV realm
SQL> select name, description, enabled from dba_dv_realm where id# >= 5000 order by 1;NAME DESCRIPTION ENABLED
------------------------------- ----------------------------------------------------------------- --------
PROTECT_EMPLOYEESEARCH_PROD A mandatory realm to protect the EMPLOYEESEARCH_PROD schema. Y
将对象添加到要保护的领域（在这里添加模式中所有的对象）：
./dv_add_obj_to_realm.sh
执行的命令和输出为：
==============================================================================Add an object to protect to the realm...
==============================================================================
USER is "C##DVOWNER"CON_NAME
------------------------------
PDB1-- . Show the objects protected by the DV realm
SQL>
select realm_name, owner, object_name, object_typefrom dvsys.dba_dv_realm_objectwhere realm_name in (select name from dvsys.dv$realm where id# >= 5000);no rows selected-- . Set all EMPLOYEESEARCH_PROD objects as protected by the DV realm "PROTECT_EMPLOYEESEARCH_PROD"
SQL>
beginDVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM(realm_name => 'PROTECT_EMPLOYEESEARCH_PROD',object_owner => 'EMPLOYEESEARCH_PROD',object_name => '%',object_type => '%');
end;
/PL/SQL procedure successfully completed.-- . Show the objects protected by the DV realm
SQL>
select realm_name, owner, object_name, object_typefrom dvsys.dba_dv_realm_objectwhere realm_name in (select name from dvsys.dv$realm where id# >= 5000);REALM_NAME OWNER OBJECT_NAME OBJECT_TYPE
---------------------------- ---------------------------- ------------------ ------------------
PROTECT_EMPLOYEESEARCH_PROD EMPLOYEESEARCH_PROD % %
确保您在领域中有授权用户。 在这一步中，我们将添加 EMPLOYEESEARCH_PROD（这是一个用户） 作为领域授权所有者：
./dv_add_auth_to_realm.sh
执行的命令和输出为：
==============================================================================Add EMPLOYEESEARCH_PROD as a real authorized owner...
==============================================================================
USER is "C##DVOWNER"CON_NAME
------------------------------
PDB1-- . Show the owner of the DV realms
SQL>
select realm_name, grantee, auth_optionsfrom dvsys.dba_dv_realm_authwhere realm_name in (select name from dvsys.dv$realm where id# >= 5000);no rows selected-- . Add EMPLOYEESEARCH_PROD as authorized owner of the DV realm "PROTECT_EMPLOYEESEARCH_PROD"
SQL>
beginDVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(realm_name => 'PROTECT_EMPLOYEESEARCH_PROD',grantee => 'EMPLOYEESEARCH_PROD',rule_set_name => '',auth_options => '1' );
end;
/PL/SQL procedure successfully completed.-- . Show the owner of the DV realms
SQL>
select realm_name, grantee, auth_optionsfrom dvsys.dba_dv_realm_authwhere realm_name in (select name from dvsys.dv$realm where id# >= 5000);REALM_NAME GRANTEE AUTH_OPTIONS
---------------------------- ---------------------- ----------------------------
PROTECT_EMPLOYEESEARCH_PROD EMPLOYEESEARCH_PROD Owner
重新执行 SQL 查询， SYS 现在收到了权限不足的错误信息：
$ ./dv_query_employee_data.sh
...
. Query EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES from SYS userfrom employeesearch_prod.demo_hr_employees*
ERROR at line 2:
ORA-01031: insufficient privileges
最后，删除领域：
./dv_drop_realm.sh
执行的命令与输出为：
==============================================================================Drop the realm...
==============================================================================
USER is "C##DVOWNER"CON_NAME
------------------------------
PDB1-- . Show the current DV realm
SQL> select name, description, enabled from dba_dv_realm where id# >= 5000 order by 1;NAME DESCRIPTION ENABLED
------------------------------- ----------------------------------------------------------------- --------
PROTECT_EMPLOYEESEARCH_PROD A mandatory realm to protect the EMPLOYEESEARCH_PROD schema. Y-- . Drop the "PROTECT_EMPLOYEESEARCH_PROD" DV realm
SQL>
beginDVSYS.DBMS_MACADM.DELETE_REALM_CASCADE(realm_name => 'PROTECT_EMPLOYEESEARCH_PROD');
END;
/PL/SQL procedure successfully completed.-- . Show the current DV realm
SQL> select name, description, enabled from dba_dv_realm where id# >= 5000 order by 1;no rows selectedTask 3: Create a Trusted Path / Multi-factor Authorization
重新在浏览器中启动Web应用：
- URL：http://<YOUR_DBSEC-LAB_VM_PUBLIC_IP>:8080/hr_prod_pdb1
- 用户名和口令：hradmin/Oracle123
- 单击Search Employee，然后单击Search
返回您的终端会话并运行此查询以查看与 Glassfish 应用程序关联的会话信息：
./dv_query_employeesearch_usage.sh
执行的命令和输出为：
==============================================================================Display the session associated to your Glassfish Appavailable here http://129.153.203.174:8080/hr_prod_pdb1 ...
==============================================================================
USER is "SYS"-- . Session information associated with your Glassfish App
SQL>
SELECT osuser, machine, module FROM v$session WHERE username = 'EMPLOYEESEARCH_PROD';OSUSER MACHINE MODULE
------------------ ------------------------------------------------------- -----------------------------------
oracle dbsec-lab JDBC Thin Client
oracle dbsec-lab JDBC Thin Client
oracle dbsec-lab JDBC Thin Client3 rows selected.
现在，使用所有者 EMPLOYEESEARCH_PROD 查询 EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES 表以证明它是可访问的：
./dv_query_employee_search.sh
执行的命令和输出为(原文中的提示有错，因为from EMPLOYEESEARCH_PROD user)：
==============================================================================Query on EMPLOYEESEARCH_PROD data...
==============================================================================
USER is "EMPLOYEESEARCH_PROD"-- . Describe EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES from EMPLOYEESEARCH_PROD user
SQL> desc employeesearch_prod.demo_hr_employees;Name Null? Type----------------------------------------------------------------------------------- -------- --------------------------------------------------------USERID NOT NULL NUMBER(4)FIRSTNAME NOT NULL VARCHAR2(25)LASTNAME NOT NULL VARCHAR2(35)EMAIL NOT NULL VARCHAR2(35)PHONEMOBILE VARCHAR2(15)PHONEFIX VARCHAR2(15)PHONEFAX VARCHAR2(15)EMPTYPE NOT NULL VARCHAR2(15)POSITION NOT NULL VARCHAR2(25)ISMANAGER NOT NULL NUMBER(1)MANAGERID NUMBER(4)DEPARTMENT NOT NULL VARCHAR2(15)CITY NOT NULL VARCHAR2(35)STARTDATE NOT NULL DATEENDDATE DATEACTIVE VARCHAR2(1)ORGANIZATION NOT NULL VARCHAR2(15)CREATIONDATE NOT NULL DATEMODIFICATIONDATE DATECOSTCENTER NUMBER(5)ISHEADOFDEPARTMENT NUMBER(1)DOB NOT NULL DATESSN VARCHAR2(15)SIN VARCHAR2(15)NINO VARCHAR2(15)ADDRESS_1 NOT NULL VARCHAR2(50)ADDRESS_2 VARCHAR2(35)STATE VARCHAR2(5)COUNTRY NOT NULL VARCHAR2(5)POSTAL_CODE NOT NULL VARCHAR2(15)CORPORATE_CARD VARCHAR2(25)CC_PIN NUMBER(4)CC_EXPIRE DATESALARY NUMBER(8,2)-- . Query EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES from EMPLOYEESEARCH_PROD user
SQL> select userid, firstname, lastname, emptype, position, ssn, sin, ninofrom employeesearch_prod.demo_hr_employeeswhere rownum < 10;USERID FIRSTNAME LASTNAME EMPTYPE POSITION SSN SIN NINO
---------- ---------- ---------- --------- ---------------- ----------- ----------- -------------73 Craig Hunt Part-Time Administrator 102-20-499774 Fred Stewart Part-Time Project Manager MN 33 14 95 E75 Julie Reed Full-time Clerk 412-62-241776 Ruby James Full-time End-User 537-78-890277 Alice Harper Part-Time District Manager 170-042-12678 Marilyn Lee Part-Time District Manager 553-51-103179 Laura Ryan Full-time Project Manager 568-10-870980 William Elliott Full-time District Manager 787-89-228281 Martha Carpenter Full-time Administrator FZ 84 80 43 S9 rows selected.
通过创建 Database Vault 规则开始保护应用程序凭据：
./dv_create_rule.sh
执行的命令和输出为：
==============================================================================Create a Database Vault Rule...
==============================================================================. We must update the script to have the fully-qualified hostname for your VMYour machine is: dbsec-lab. The default rule looks like this:
SYS_CONTEXT('USERENV','SESSION_USER') = 'EMPLOYEESEARCH_PROD'
AND SYS_CONTEXT('USERENV','OS_USER') = 'oracle'
AND SYS_CONTEXT('USERENV','MODULE') = 'JDBC Thin Client'
AND SYS_CONTEXT('USERENV','HOST') = 'dbsec-lab.dbsecvcn.oraclevcn.com'-- . Create the rule "Application Connection"
SQL>
beginDVSYS.DBMS_MACADM.CREATE_RULE(rule_name => 'Application Connection'
, rule_expr => ${RULE_EXPR});
end;
/PL/SQL procedure successfully completed.-- . Show the DV rule "Application Connection"
SQL>
SELECT name, rule_expr FROM DBA_DV_RULE where name = 'Application Connection';NAME RULE_EXPR
------------------------------ ------------------------------------------------------------------------------------------
Application Connection SYS_CONTEXT('USERENV','SESSION_USER') = 'EMPLOYEESEARCH_PROD' AND SYS_CONTEXT('USERENV','OS_USER') = 'oracle' AND SYS_CONTEXT('USERENV','MODULE') = 'JDBC Thin Client' AND SYS_CONTEXT('USERENV','HOST') = 'dbsec-lab'
我们仅授权作为可信路径应用程序从 Glassfish Web 应用程序（JDBC Thin Client）通过模式所有者 EMPLOYEESEARCH_PROD 进行访问！
我们通过将 Database Vault Rule 添加到 DV Rule Set 来使用它
- 您可以在规则集中拥有一个或多个规则
- 如果您有多个，您可以在评估所有规则必须为真或任何规则必须为真的规则集之间进行选择
- 把它想象成 IN 和 EXISTS 之间的区别 - IN 包括所有，而 EXISTS 一旦识别出一个结果匹配就停止
./dv_create_rule_set.sh
执行的代码和输出为：
==============================================================================Create a Rule Set from the Rule we created...
==============================================================================-- . Create the rule set "Trusted Application Path"
SQL>
beginDVSYS.DBMS_MACADM.CREATE_RULE_SET(rule_set_name => 'Trusted Application Path',description => 'Protecting the App User',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 => 'You cannot use the app account this way.',fail_code => -20000,handler_options => null,handler => null,is_static => TRUE);
end;
/PL/SQL procedure successfully completed.-- . Associate the rule set "Trusted Application Path" to the rule "Application Connection"
SQL>
beginDVSYS.DBMS_MACADM.ADD_RULE_TO_RULE_SET(rule_set_name => 'Trusted Application Path',rule_name => 'Application Connection',rule_order => 1,enabled => DBMS_MACUTL.G_YES);
end;
/PL/SQL procedure successfully completed.-- . Show the rule set "Trusted Application Path"
SQL>
SELECT rule_set_name, enabled, eval_options_meaning, audit_options, fail_message, fail_code, is_staticFROM DBA_DV_RULE_SETwhere rule_set_name = 'Trusted Application Path';RULE_SET_NAME E EVAL_OPTIONS_MEANING AUDIT_OPTIONS FAIL_MESSAGE FAIL_CODE IS_ST
------------------------------ - -------------------- ------------- --------------------------------------------- ---------- -----
Trusted Application Path Y All True 1 You cannot use the app account this way. -20000 TRUE
在“CONNECT”上创建命令规则以保护 EMPLOYEESEARCH_PROD 用户：
./dv_create_command_rule.sh
执行的命令和输出为：
==============================================================================Create a Command Rule on Connect to protect the EMPLOYEESEARCH_PROD user...
==============================================================================-- . Create the Command Rule on Connect for "Application Connection"
SQL>
beginDVSYS.DBMS_MACADM.CREATE_CONNECT_COMMAND_RULE(user_name => 'EMPLOYEESEARCH_PROD',rule_set_name => 'Trusted Application Path',enabled => DBMS_MACUTL.G_YES);
end;
/PL/SQL procedure successfully completed.. Show the Command Rule on Connect for "Application Connection"
SQL> select command, object_owner, object_name, rule_set_name from dba_dv_command_rule where command = 'CONNECT';COMMAND OBJECT_OWNER OBJECT_NAME RULE_SET_NAME
-------------------- ------------------------ ------------------------ ----------------------------------------
CONNECT EMPLOYEESEARCH_PROD % Trusted Application Path
现在，只有当匹配我们创建的规则集时，才能通过用户 EMPLOYEESEARCH_PROD连接（connect）！
返回您的 Glassfish Web应用程序并刷新几次并通过单击 [搜索] 运行一些查询并浏览员工数据
注意：因为您将 Glassfish Web应用程序用作可信路径应用程序，所以您可以访问数据！
返回您的终端会话并重新运行我们对应用程序使用情况的查询，以验证它是否仍然有效：
./dv_query_employeesearch_usage.sh
执行的命令和输出为：
==============================================================================Display the session associated to your Glassfish Appavailable here http://129.153.203.174:8080/hr_prod_pdb1 ...
==============================================================================
USER is "SYS"-- . Session information associated with your Glassfish App
SQL> SELECT osuser, machine, module FROM v$session WHERE username = 'EMPLOYEESEARCH_PROD';
OSUSER MACHINE MODULE
------------------ ------------------------------------------------------- -----------------------------------
oracle dbsec-lab JDBC Thin Client
oracle dbsec-lab JDBC Thin Client2 rows selected.
现在，尝试使用所有者 EMPLOYEESEARCH_PROD 查询 EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES 表……您应该被阻止！
$ ./dv_query_employee_search.sh
==============================================================================Query on EMPLOYEESEARCH_PROD data...
==============================================================================
ERROR:
ORA-47306: 20000: You cannot use the app account this way.ERROR:
ORA-01017: invalid username/password; logon deniedSP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]<proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
注意：因为您是通过非“可信路径”应用程序查询，所以您无法访问数据！
成功完成实验后，您可以从 Database Vault 中删除命令规则、规则集和规则：
./dv_del_trusted_path.sh
执行的命令和输出为：
==============================================================================Remove Command Rule, Rule Set, and Rule...
==============================================================================-- . Delete the Command Rule
SQL>
beginDVSYS.DBMS_MACADM.DELETE_CONNECT_COMMAND_RULE(user_name => 'EMPLOYEESEARCH_PROD');
end;
/PL/SQL procedure successfully completed.-- . Delete the Rule Set
SQL>
beginDVSYS.DBMS_MACADM.DELETE_RULE_SET(rule_set_name => 'Trusted Application Path');
end;
/PL/SQL procedure successfully completed.-- . Delete the Rule
beginDVSYS.DBMS_MACADM.DELETE_RULE(rule_name => 'Application Connection');
end;
/PL/SQL procedure successfully completed.
Task 4: Simulation Mode
首先，查询模拟日志，显示没有当前值：
./dv_query_simulation_logs.sh
执行的命令和输出为：
==============================================================================Query the simulation logs...
==============================================================================-- . Display the current simulation logs
SQL>
SELECT VIOLATION_TYPE, USERNAME, MACHINE, COMMAND, DV\$_MODULE, SQLTEXT FROM DBA_DV_SIMULATION_LOG;no rows selected
接下来，创建一个命令规则，该规则将模拟阻止与数据库的所有连接。 这是我们识别谁在连接以及他们从哪里连接的一种简单方法。
./dv_command_rule_sim_mode.sh
执行的命令和输出为：
==============================================================================Create a Command Rule that will simulate blocking all connections to the DB...
==============================================================================-- . Create the Command Rule
SQL>
BEGIN
DBMS_MACADM.CREATE_COMMAND_RULE(command => 'CONNECT',rule_set_name => 'Disabled',object_name => '%',object_owner => '%',enabled => DBMS_MACUTL.G_SIMULATION);
END;
/PL/SQL procedure successfully completed.
执行一个脚本来创建一些数据库连接并生成一些日志条目：
./dv_run_queries.sh
输出为：
==============================================================================Execute some db connections and generate some log entries...
==============================================================================CON_NAME
------------------------------
PDB1. Count EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES rows as "SYS"COUNT(*)
----------1000. Count EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES rows as "SYSTEM"COUNT(*)
----------1000. Count EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES rows as "DBA_DEBRA"COUNT(*)
----------1000
现在，我们再次查询模拟日志以查看我们有哪些新条目。 请记住，我们创建了一个命令规则来模拟阻止用户连接！
./dv_query_simulation_logs.sh
输出显示有Command Rule Violation：
==============================================================================Query the simulation logs...
==============================================================================. Display the current simulation logsVIOLATION_TYPE USERNAME MACHINE COMMAND
------------------------ -------------------- ------------------------------------------------- ------------
DV$_MODULE SQLTEXT
----------------- ------------------------------------------------------------
Command Rule Violation SYS dbsec-lab CONNECT
sqlplus@dbsec-lab CONNECT(TNS V1-V3)Command Rule Violation DBA_DEBRA dbsec-lab CONNECT
sqlplus@dbsec-lab CONNECT(TNS V1-V3)Command Rule Violation SYSTEM dbsec-lab CONNECT
sqlplus@dbsec-lab CONNECT(TNS V1-V3)
该日志显示所有已连接并被该规则阻止的用户。 它还显示了他们从哪里连接以及他们用来连接的客户端。
运行此脚本以获取模拟日志中存在的不同用户名列表：
./dv_distinct_users_sim_logs.sh
执行的命令和输出为：
==============================================================================Get a list of distinct usernames present in the DV simulation logs...
==============================================================================-- . Display the current simulation logs
SQL> SELECT distinct USERNAME FROM DBA_DV_SIMULATION_LOG order by 1;USERNAME
--------------------
DBA_DEBRA
SYS
SYSTEM
虽然我们只在 CONNECT 规则上使用了模拟模式，但我们可以在 Realm 上使用它来显示我们会遇到哪些违规行为。
在进入下一个实验之前，我们将清理模拟日志并删除命令规则：
./dv_purge_sim_logs.sh
./dv_drop_command_rule.sh
执行的命令和输出为：
==============================================================================Purge the simulation logs...
==============================================================================-- . Current simulation logs before pruging
SQL> select count(*) from dba_dv_simulation_log;COUNT(*)
----------3-- . Purge simulation logs
SQL> DELETE FROM DVSYS.SIMULATION_LOG$;3 rows deleted.-- . Current simulation logs after pruging
SQL> select count(*) from dba_dv_simulation_log;COUNT(*)
----------0==============================================================================Remove the Command Rule...
==============================================================================-- . Delete the Command Rule
SQL>
BEGINDBMS_MACADM.DELETE_COMMAND_RULE(command => 'CONNECT',object_owner => '%',object_name => '%',scope => DBMS_MACUTL.G_SCOPE_LOCAL);
END;
/PL/SQL procedure successfully completed.
Task 5: Ops Control
Database Vault Operations Control 自动限制公共用户（common user）在自治、常规云或本地环境中访问可插拔数据库 (PDB) 本地数据。
在Oracle By Example 19c新特性中也有此实验。
检查 Database Vault 和 Operations Control 的状态：
./dv_status.sh
输出为：
==============================================================================Check the status of Database Vault...
==============================================================================CON_NAME
------------------------------
CDB$ROOT. Show the DB Vault statusNAME STATUS
------------------------- --------------------
DV_APP_PROTECTION NOT CONFIGURED
DV_CONFIGURE_STATUS TRUE
DV_ENABLE_STATUS TRUEPDB_NAME OPEN_MODE NAME STATUS
-------------------- -------------------- ------------------------- --------------------
PDB1 READ WRITE DV_APP_PROTECTION NOT CONFIGUREDREAD WRITE DV_ENABLE_STATUS TRUEREAD WRITE DV_CONFIGURE_STATUS TRUEPDB_NAME OPEN_MODE NAME STATUS
-------------------- -------------------- ------------------------- --------------------
PDB2 READ WRITE DV_APP_PROTECTION NOT CONFIGUREDREAD WRITE DV_ENABLE_STATUS FALSEREAD WRITE DV_CONFIGURE_STATUS FALSE6 rows selected.
注意：目前尚未配置！
接下来，我们将在可插拔数据库 pdb1 和 pdb2中运行相同的查询，以用户DBA_DEBRA：
./dv_query_with_debra.sh
输出为：
==============================================================================Run the same queries as both pluggable database as dba_debra...
==============================================================================. Select "EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES" table on PDB1 as dba_debraCON_NAME
------------------------------
PDB1
USER is "DBA_DEBRA"OWNER TABLE_NAME TABLESPACE_NAME
------------------------ ------------------------ --------------------------
EMPLOYEESEARCH_PROD DEMO_HR_EMPLOYEES EMPDATA_PRODCOUNT(*)
----------1000. Select "EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES" table on PDB2 as dba_debraCON_NAME
------------------------------
PDB2
USER is "DBA_DEBRA"OWNER TABLE_NAME TABLESPACE_NAME
------------------------ ------------------------ --------------------------
EMPLOYEESEARCH_PROD DEMO_HR_EMPLOYEES EMPDATA_PRODCOUNT(*)
----------1000
然后以用户C##SEC_DBA_SAL运行相同的查询：
./dv_query_with_sal.sh
输出为：
==============================================================================Run the same queries as both pluggable database as c##sec_dba_sal...
==============================================================================. Select "EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES" table on PDB1 as c##sec_dba_salCON_NAME
------------------------------
PDB1
USER is "C##SEC_DBA_SAL"OWNER TABLE_NAME TABLESPACE_NAME
------------------------ ------------------------ --------------------------
EMPLOYEESEARCH_PROD DEMO_HR_EMPLOYEES EMPDATA_PRODCOUNT(*)
----------1000. Select "EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES" table on PDB2 as c##sec_dba_salCON_NAME
------------------------------
PDB2
USER is "C##SEC_DBA_SAL"OWNER TABLE_NAME TABLESPACE_NAME
------------------------ ------------------------ --------------------------
EMPLOYEESEARCH_PROD DEMO_HR_EMPLOYEES EMPDATA_PRODCOUNT(*)
----------1000
注意：
- 查询结果相同
- Common用户 C##SEC_DBA_SAL 可以访问可插拔数据库中的数据，就像 pdb Admin 一样
启用 Database Vault 19c Operations Control 并再次运行查询：
./dv_enable_ops_control.sh
执行的命令和输出为：
==============================================================================Enable Database Vault 19c Operations Control...
==============================================================================-- . Enable DB Vault Ops Control
SQL> exec dbms_macadm.enable_app_protection;PL/SQL procedure successfully completed.
查询DV状态，注意DV_APP_PROTECTION状态变为ENABLED：
$ ./dv_status.sh==============================================================================Check the status of Database Vault...
==============================================================================CON_NAME
------------------------------
CDB$ROOT. Show the DB Vault statusNAME STATUS
------------------------- --------------------
DV_APP_PROTECTION ENABLED
DV_CONFIGURE_STATUS TRUE
DV_ENABLE_STATUS TRUEPDB_NAME OPEN_MODE NAME STATUS
-------------------- -------------------- ------------------------- --------------------
PDB1 READ WRITE DV_APP_PROTECTION ENABLEDREAD WRITE DV_ENABLE_STATUS TRUEREAD WRITE DV_CONFIGURE_STATUS TRUEPDB_NAME OPEN_MODE NAME STATUS
-------------------- -------------------- ------------------------- --------------------
PDB2 READ WRITE DV_APP_PROTECTION ENABLEDREAD WRITE DV_ENABLE_STATUS FALSEREAD WRITE DV_CONFIGURE_STATUS FALSE6 rows selected.
再次以用户DBA_DEBRA运行查询：
./dv_query_with_debra.sh
输出正常，和之前一样：
==============================================================================Run the same queries as both pluggable database as dba_debra...
==============================================================================. Select "EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES" table on PDB1 as dba_debraCON_NAME
------------------------------
PDB1
USER is "DBA_DEBRA"OWNER TABLE_NAME TABLESPACE_NAME
------------------------ ------------------------ --------------------------
EMPLOYEESEARCH_PROD DEMO_HR_EMPLOYEES EMPDATA_PRODCOUNT(*)
----------1000. Select "EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES" table on PDB2 as dba_debraCON_NAME
------------------------------
PDB2
USER is "DBA_DEBRA"OWNER TABLE_NAME TABLESPACE_NAME
------------------------ ------------------------ --------------------------
EMPLOYEESEARCH_PROD DEMO_HR_EMPLOYEES EMPDATA_PRODCOUNT(*)
----------1000
再次以 Common用户 C##SEC_DBA_SAL运行查询：
./dv_query_with_sal.sh
输出显示权限不够：
==============================================================================Run the same queries as both pluggable database as c##sec_dba_sal...
==============================================================================. Select "EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES" table on PDB1 as c##sec_dba_salCON_NAME
------------------------------
PDB1
USER is "C##SEC_DBA_SAL"OWNER TABLE_NAME TABLESPACE_NAME
------------------------ ------------------------ --------------------------
EMPLOYEESEARCH_PROD DEMO_HR_EMPLOYEES EMPDATA_PRODselect count(*) from employeesearch_prod.demo_hr_employees*
ERROR at line 1:
ORA-01031: insufficient privileges. Select "EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES" table on PDB2 as c##sec_dba_salCON_NAME
------------------------------
PDB2
USER is "C##SEC_DBA_SAL"OWNER TABLE_NAME TABLESPACE_NAME
------------------------ ------------------------ --------------------------
EMPLOYEESEARCH_PROD DEMO_HR_EMPLOYEES EMPDATA_PRODselect count(*) from employeesearch_prod.demo_hr_employees*
ERROR at line 1:
ORA-01031: insufficient privileges
完成此实验后，禁用 Ops Control：
./dv_disable_ops_control.sh
执行的命令和输出为：
==============================================================================Disable Database Vault 19c Operations Control...
==============================================================================-- . Disable DB Vault Ops Control
SQL> exec dbms_macadm.disable_app_protection;PL/SQL procedure successfully completed.Task 6: Disabling Database Vault
禁用可插拔数据库 pdb1：
./dv_disable_on_pdb.sh pdb1
输出为：
==============================================================================Disable Database Vault for the pluggable database ...
==============================================================================CON_NAME
------------------------------
CDB$ROOT. Show the DB Vault statusNAME STATUS
------------------------- --------------------
DV_APP_PROTECTION DISABLED
DV_CONFIGURE_STATUS TRUE
DV_ENABLE_STATUS TRUEPDB_NAME OPEN_MODE NAME STATUS
-------------------- -------------------- ------------------------- --------------------
PDB1 READ WRITE DV_APP_PROTECTION DISABLEDREAD WRITE DV_ENABLE_STATUS TRUEREAD WRITE DV_CONFIGURE_STATUS TRUEPDB_NAME OPEN_MODE NAME STATUS
-------------------- -------------------- ------------------------- --------------------
PDB2 READ WRITE DV_APP_PROTECTION DISABLEDREAD WRITE DV_ENABLE_STATUS FALSEREAD WRITE DV_CONFIGURE_STATUS FALSE6 rows selected.. Connect to the pluggable database pdb1Session altered.CON_NAME
------------------------------
PDB1. Disable DB Vault
USER is "C##DVOWNER"PL/SQL procedure successfully completed.. Reboot the pluggable databaseCON_NAME
------------------------------
CDB$ROOTPluggable database altered.Pluggable database altered.. Show the DB Vault statusNAME STATUS
------------------------- --------------------
DV_APP_PROTECTION DISABLED
DV_CONFIGURE_STATUS TRUE
DV_ENABLE_STATUS TRUEPDB_NAME OPEN_MODE NAME STATUS
-------------------- -------------------- ------------------------- --------------------
PDB1 READ WRITE DV_APP_PROTECTION DISABLEDREAD WRITE DV_ENABLE_STATUS FALSEREAD WRITE DV_CONFIGURE_STATUS TRUEPDB_NAME OPEN_MODE NAME STATUS
-------------------- -------------------- ------------------------- --------------------
PDB2 READ WRITE DV_APP_PROTECTION DISABLEDREAD WRITE DV_ENABLE_STATUS FALSEREAD WRITE DV_CONFIGURE_STATUS FALSE6 rows selected.
现在，在容器数据库 cdb1 中禁用 Database Vault：
./dv_disable_on_cdb.sh
输出为：
==============================================================================Disable Database Vault for the container database CDB...
==============================================================================CON_NAME
------------------------------
CDB$ROOT. Show the DB Vault statusNAME STATUS
------------------------- --------------------
DV_APP_PROTECTION DISABLED
DV_CONFIGURE_STATUS TRUE
DV_ENABLE_STATUS TRUEPDB_NAME OPEN_MODE NAME STATUS
-------------------- -------------------- ------------------------- --------------------
PDB1 READ WRITE DV_APP_PROTECTION DISABLEDREAD WRITE DV_ENABLE_STATUS FALSEREAD WRITE DV_CONFIGURE_STATUS TRUEPDB_NAME OPEN_MODE NAME STATUS
-------------------- -------------------- ------------------------- --------------------
PDB2 READ WRITE DV_APP_PROTECTION DISABLEDREAD WRITE DV_ENABLE_STATUS FALSEREAD WRITE DV_CONFIGURE_STATUS FALSE6 rows selected.. Disable DB VaultCON_NAME
------------------------------
CDB$ROOT
USER is "C##DVOWNER"PL/SQL procedure successfully completed.. Reboot the DatabaseCON_NAME
------------------------------
CDB$ROOT
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.Total System Global Area 3674209872 bytes
Fixed Size 9141840 bytes
Variable Size 1996488704 bytes
Database Buffers 1660944384 bytes
Redo Buffers 7634944 bytes
Database mounted.
Database opened.. Show the DB Vault statusNAME STATUS
------------------------- --------------------
DV_APP_PROTECTION DISABLED
DV_CONFIGURE_STATUS TRUE
DV_ENABLE_STATUS FALSEPDB_NAME OPEN_MODE NAME STATUS
-------------------- -------------------- ------------------------- --------------------
PDB1 READ WRITE DV_APP_PROTECTION DISABLEDREAD WRITE DV_ENABLE_STATUS FALSEREAD WRITE DV_CONFIGURE_STATUS TRUEPDB_NAME OPEN_MODE NAME STATUS
-------------------- -------------------- ------------------------- --------------------
PDB2 READ WRITE DV_APP_PROTECTION DISABLEDREAD WRITE DV_ENABLE_STATUS FALSEREAD WRITE DV_CONFIGURE_STATUS FALSE6 rows selected.
注意：
- 要禁用 DB Vault，数据库将重新启动！
- cdb 的 DV_ENABLE_STATUS 必须为 FALSE
Appendix: About the Product
Oracle Database Vault 提供控制以防止未经授权的特权用户访问敏感数据并防止未经授权的数据库更改。
Oracle Database Vault 安全控制保护应用程序数据免遭未经授权的访问，并符合隐私和法规要求。
您可以部署控制以阻止特权帐户访问应用程序数据并使用可信路径授权控制数据库内的敏感操作。
通过对权限和角色的分析，您可以通过使用最低权限最佳实践来提高现有应用程序的安全性。
Oracle Database Vault 透明地保护现有数据库环境，消除昂贵且耗时的应用程序更改。
Oracle Database Vault 使您能够创建一组组件来管理数据库实例的安全性。
这些组件如下：
- Realms：领域是数据库内部的一个保护区，可以保护数据库模式、对象和角色。 例如，您可以保护与会计、销售或人力资源相关的一组模式、对象和角色。 将这些保护到领域后，您可以使用领域来控制对特定帐户或角色的系统和对象权限的使用。 这使您能够为想要使用这些模式、对象和角色的任何人提供细粒度的访问控制。
- Command rules：命令规则是一种特殊的安全策略，您可以创建它来控制用户如何执行几乎任何 SQL 语句，包括 SELECT、ALTER SYSTEM、数据库定义语言 (DDL) 和数据操作语言 (DML) 语句。 命令规则必须与规则集一起使用以确定是否允许该语句。
- Factors：因子是一个命名变量或属性，例如用户位置、数据库 IP 地址或会话用户，Oracle Database Vault 可以识别并用作可信路径。 您可以使用规则中的因子来控制活动，例如授权数据库帐户连接到数据库或执行特定的数据库命令来限制数据的可见性和可管理性。 每个因素可以有一个或多个身份。 标识是因子的实际值。 一个因子可以有多个标识，具体取决于因子检索方法或其标识映射逻辑。
- Rule sets：规则集是一个或多个规则的集合，您可以将这些规则与领域授权、命令规则、因子分配或安全应用程序角色相关联。 规则集根据其包含的每个规则的评估和评估类型（全部为真或任何为真）评估为真或假。 规则集中的规则是一个计算结果为真或假的 PL/SQL 表达式。 您可以在多个规则集中拥有相同的规则。
- Secure application roles：安全应用程序角色是一种特殊的 Oracle 数据库角色，可以根据对 Oracle Database Vault 规则集的评估来启用它。
为了增强这些组件，Oracle Database Vault 提供了一组 PL/SQL 接口和包。 通常，您采取的第一步是创建一个由您想要保护的数据库模式或数据库对象组成的领域。 您可以通过创建规则、命令规则、因子、标识、规则集和安全应用程序角色来进一步保护领域。 此外，您可以运行有关这些组件监控和保护的活动的报告。
使用 Database Vault 的好处：
- 解决合规性法规以提高安全意识
- 保护特权用户帐户，免受外部和内部的许多安全漏洞和数据窃取
- 帮助您为数据库设计灵活的安全策略
- 解决数据库整合和云环境问题，以降低成本并减少将敏感应用程序数据暴露给无需知道的人
- 在多租户环境中工作，提高了整合的安全性
Want to Learn More?
参考文档：Oracle Database Vault 19c
视频：
- Oracle Database Vault - Use Cases (Part1) (October 2019)
- Oracle Database Vault - Use Cases (Part2) (October 2019)
Acknowledgements
本实验作者为Hakim Loumi，数据库安全的PM；贡献者为Alan Williams和Rene Fontcha
这篇关于Oracle LiveLabs实验：DB Security - Database Vault的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
