Oracle LiveLabs实验：DB Security
概述
此实验申请地址在这里，时间为45分钟。
本实验也是DB 研讨会的的第6个实验，即Lab 8。
实验帮助在这里。
本实验使用的数据库为19.13。
也推荐看一下这个实验 Vault on an ，有简明的过程，特别是有创建用户的过程。
本研讨会介绍了 Vault (DV) 的各种特性和功能。 它使用户有机会学习如何配置这些功能，以防止未经授权的特权用户访问敏感数据。
Task 1: Vault
进入实验目录：
sudo su - oracle
cd $DBSEC_LABS/database-vault
首先在容器数据库 cdb1 中启用 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 中启用了 Vault！
Task 2: a Realm
在浏览器中启动Web应用：
返回您的终端会话并运行命令以查看中的数据：
./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.
现在，创建领域 以保护 模式中的对象免受恶意活动：
./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 % %
确保您在领域中有授权用户。 在这一步中，我们将添加 （这是一个用户） 作为领域授权所有者：
./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: a Path / Multi-
重新在浏览器中启动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 Client
oracle dbsec-lab JDBC Thin Client3 rows selected.
现在，使用所有者 查询 . 表以证明它是可访问的：
./dv_query_employee_search.sh
执行的命令和输出为(原文中的提示有错，因为from 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.
通过创建 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'
我们仅授权作为可信路径应用程序从 Web 应用程序（JDBC Thin ）通过模式所有者 进行访问！
我们通过将 Vault Rule 添加到 DV Rule Set 来使用它
./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
在“”上创建命令规则以保护 用户：
./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
现在，只有当匹配我们创建的规则集时，才能通过用户 连接（）！
返回您的 Web应用程序并刷新几次并通过单击 [搜索] 运行一些查询并浏览员工数据
注意：因为您将 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.
现在，尝试使用所有者 查询 . 表……您应该被阻止！
$ ./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
注意：因为您是通过非“可信路径”应用程序查询，所以您无法访问数据！
成功完成实验后，您可以从 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: 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
输出显示有 Rule ：
==============================================================================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
虽然我们只在 规则上使用了模拟模式，但我们可以在 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
Vault 自动限制公共用户（ user）在自治、常规云或本地环境中访问可插拔数据库 (PDB) 本地数据。
在 By 19c新特性中也有此实验。
检查 Vault 和 的状态：
./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中运行相同的查询，以用户：
./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##运行相同的查询：
./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
注意：
启用 Vault 19c 并再次运行查询：
./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_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.
再次以用户运行查询：
./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##运行查询：
./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 ：
./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: 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 中禁用 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.
注意：
: About the
Vault 提供控制以防止未经授权的特权用户访问敏感数据并防止未经授权的数据库更改。
Vault 安全控制保护应用程序数据免遭未经授权的访问，并符合隐私和法规要求。
您可以部署控制以阻止特权帐户访问应用程序数据并使用可信路径授权控制数据库内的敏感操作。
通过对权限和角色的分析，您可以通过使用最低权限最佳实践来提高现有应用程序的安全性。
Vault 透明地保护现有数据库环境，消除昂贵且耗时的应用程序更改。
Vault 使您能够创建一组组件来管理数据库实例的安全性。
这些组件如下：
为了增强这些组件， Vault 提供了一组 PL/SQL 接口和包。 通常，您采取的第一步是创建一个由您想要保护的数据库模式或数据库对象组成的领域。 您可以通过创建规则、命令规则、因子、标识、规则集和安全应用程序角色来进一步保护领域。 此外，您可以运行有关这些组件监控和保护的活动的报告。
使用 Vault 的好处：
Want to Learn More?
参考文档： Vault 19c
视频：
本实验作者为Hakim Loumi，数据库安全的PM；贡献者为Alan 和Rene
