本文主要是介绍Oracle LiveLabs实验：DB Security - Oracle Label Security (OLS)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
概述
此实验申请地址在这里，时间为30分钟。
本实验也是DB Security Advanced研讨会的的第7个实验，即Lab 7。
实验帮助在这里。
本实验使了Oracle数据库19.13及Oracle Enterprise Manager 13.5。
Introduction
本研讨会介绍了 Oracle Label Security (OLS) 的各种特性和功能。 它使用户有机会学习如何配置这些功能以保护其敏感数据，帮助跟踪允诺，并根据《通用数据保护条例》等法规要求强制限制处理。
Task 1: Simple CRM Application
不同的应用有不同的用途：
- 用户应用
- 应用程序：用户设置其偏好以同意营销、处理数据或要求被遗忘
- 用户标签：NCNST::DP ；数据库用户：APPPREFERENCE
- 电子邮件营销
- 应用程序：只能访问已同意处理其数据且专门用于电子邮件营销的用户
- 用户标签：CONS::EMAIL；数据库用户：APPMKT
- 商业智能
- 应用程序：可以访问所有同意处理其数据的用户
- 用户标签：CONS::DP；数据库用户：APPBI
- 匿名者
- 批处理匿名用户记录并将数据标签设置为 ANON::
- 用户标签：FORGET::；数据库用户：APPFORGET
虽然我们提供脚本以自动化方式从头到尾执行整个实验室，但强烈建议您一个一个打开并一个一个复制/执行代码块。这样，您将更好地理解本练习的构建块。如果您决定逐个执行脚本，您可以随时查看日志文件 (.out) 以了解详细信息
进入实验目录：
sudo su - oracle
cd $DBSEC_LABS/label-security
首先设置标签安全环境，输出为ols_setup_env.out：
./ols_setup_env.sh
以上脚本：
- 创建 C##OSCAR_OLS 用户（CDB中）、创建表、加载数据、创建将用于展示不同场景的用户（PDB中），它还配置和启用 OLS
- 调用 load_crm_customer_data.sql 脚本以在 APPCRM 模式中创建表 CRM_CUSTOMER 并插入 391 行
接下来，您将创建标签安全策略。 策略由级别、组和/或分区组成。 政策的唯一强制性组成部分是至少有一个级别：
./ols_create_policy.sh
输出为：
==============================================================================Create the Label Security policy "OLS_DEMO_GDPR"...
==============================================================================CON_NAME
------------------------------
PDB1
USER is "C##OSCAR_OLS"-------------------------------------------. STEP 1: CREATE OLS POLICY (OLS_DEMO_GDPR)-------------------------------------------PL/SQL procedure successfully completed.-------------------------------------------. STEP 2: CREATE LEVELS10 - CONSENT (CNST)20 - ANONYMIZED (ANON)30 - FORGET (FRGT)40 - NOCONSENT (NCNST)-------------------------------------------... Create CONSENT levelPL/SQL procedure successfully completed.... Create ANONYMIZED levelPL/SQL procedure successfully completed.... Create FORGET levelPL/SQL procedure successfully completed.... Create NOCONSENT levelPL/SQL procedure successfully completed.---------------------------------------------------------. STEP 3: CREATE GROUPSHere we used a hierarchy of groups to controlwhich data can be processed (based on given consent):1000 - DATA_PROCESSING (DT_PROD)1100 - CAMPAIGN_MGMT (CAMP_MGMT)1110 - EMAIL1120 - POST_MAIL1130 - WEB_ADS1200 - ANALYTICS1210 - RECOMMENDATION_ENGINE (REC_ENGINE)1300 - THIRDPARTY1310 - CONTACT_DETAILS (CONTACT_DET)1320 - PREFERENCE_DETAILS (PREF_DETAILS)1330 - PURCHASE_HIST (PURCH_HIST)---------------------------------------------------------... Create DATA_PROCESSING groupPL/SQL procedure successfully completed.... ... Create CAMPAIGN_MGMT groupPL/SQL procedure successfully completed.... ... ... Create EMAIL groupPL/SQL procedure successfully completed.... ... ... Create POST_MAIL groupPL/SQL procedure successfully completed.... ... ... Create ONLINE_ADS groupPL/SQL procedure successfully completed.... ... Create ANALYTICS groupPL/SQL procedure successfully completed.... ... ... Create REC_ENGINE groupPL/SQL procedure successfully completed.... ... Create THIRDPARTY groupPL/SQL procedure successfully completed.... ... ... Create CONTACT_DETAILS groupPL/SQL procedure successfully completed.... ... ... Create PREFERENCE_DETAILS groupPL/SQL procedure successfully completed.... ... ... Create PURCHASE_HIST groupPL/SQL procedure successfully completed.------------------------------------------------------------. STEP 4: CREATE LABELSThe label is automatically designated as a valid data labelThis functionality limits the labels that can be assigned to dataIf a user widthraws consent the row label will have that compartment removedAllowed Labels (Trim down/add to suite the use cases):CNST:: 500FORGET:: 700ANON:: 800NOCONSENT:: 999---------CNST::DT_PROC 1000CNST::CAMP_MGMT 1100CNST::EMAIL 1110CNST::POST_MAIL 1120CNST::WEB_ADS 1130CNST::EMAIL,POST_MAIL 1140CNST::EMAIL,ANALYTICS 1145CNST::EMAIL,WEB_ADS 1150CNST::CAMP_MGMT,ANALYTICS,THIRDPARTY 1160CNST::CAMP_MGMT,ANALYTICS 1170CNST::CAMP_MGMT,THIRDPARTY 1180CNST::ANALYTICS,THIRDPARTY 1190CNST::POST_MAIL,WEB_ADS 1195---------CNST::ANALYTICS 1200CNST::REC_ENGINE 1210---------CNST::THIRDPARTY 1300CNST::CONTACT_DETAILS 1310CNST::PREF_DETAILS 1320CNST::PURCH_HIST 1330CNST::CONTACT_DETAILS,PREF_DETAILS 1340CNST::CONTACT_DETAILS,PURCH_HIST 1350CNST::PREF_DETAILS,PURCH_HIST 1360------------------------------------------------------------...
. STEP 5: ASSING LEVELS TO USERSUsers | Levels---------------------|------------------------------------------------APPPREFERENCE | Can process all data| . Level Min (CNST) and Level Max (NCNST)| . Group (DT_PROC)---------------------|------------------------------------------------APPFORGET | Can process data marked as to be forgotten| . Level Min (ANON) and Level Max (FRGT)---------------------|------------------------------------------------APPMKT | Can process data belonging to group EMAIL only| . Level Min (CNST) and Level Max (CNST)| . Group (EMAIL)---------------------|------------------------------------------------APPBI | Can process data belonging to group ANALYTICS| . Level Min (ANON) and Level Max (ANON)| . Group (ANALYTICS)---------------------|------------------------------------------------APP3RD | Can process data belonging to group THIRDPARTY| . Level Min (CNST) and Level Max (CNST)| . Group (THIRDPARTY)------------------------------------------------------------------------... Set Levels for APPPREFERENCEPL/SQL procedure successfully completed.... ... prompt Set Group for APPPREFERENCEPL/SQL procedure successfully completed.... Set Level for APPFORGETPL/SQL procedure successfully completed.... Set Level for APPMKTPL/SQL procedure successfully completed.... ... Set Group for APPMKTPL/SQL procedure successfully completed.... Set Level for APPBIPL/SQL procedure successfully completed.... ... Set Group for APPBIPL/SQL procedure successfully completed.... Set Level for APP3RDPL/SQL procedure successfully completed.... ... Set Group for APP3RDPL/SQL procedure successfully completed.----------------------------------------------------. STEP 6: APPLY THE OLS POLICY----------------------------------------------------PL/SQL procedure successfully completed.
此脚本将创建策略（级别、组和标签），为用户设置级别和组，并将策略应用于 APPCRM.CRM_CUSTOMER 表。对于每个步骤，您可以查看您执行的脚本的输出（例如“more ols_create_policy.out”）。
然后，我们必须标记数据……我们使用我们创建的策略并应用一个级别，一个或多个分区(可选)，一个或多个组(可选)。
输出如下：
==============================================================================Label the data...
==============================================================================CON_NAME
------------------------------
PDB1
USER is "SYS"-- . ANON - Already anonymized: 10 records
SQL>
UPDATE APPCRM.CRM_CUSTOMER
SET gdpr_col = CHAR_TO_LABEL('OLS_DEMO_GDPR','ANON')
where customerid between 51 and 60;10 rows updated.-- . CNST::ANALYTICS - Consented to be processed for analytics: 200 records
SQL>
UPDATE APPCRM.CRM_CUSTOMER
SET gdpr_col = CHAR_TO_LABEL('OLS_DEMO_GDPR','CNST::ANALYTICS')
where customerid between 66 and 265;200 rows updated.. CNST::EMAIL - Consented to be processed for email: 123 records
SQL>
UPDATE APPCRM.CRM_CUSTOMER
SET gdpr_col = CHAR_TO_LABEL('OLS_DEMO_GDPR','CNST::EMAIL')
where customerid between 266 and 388;123 rows updated.. CNST::EMAIL,ANALYTICS - Consented to be processed for email and bi: 3 records
SQL>
UPDATE APPCRM.CRM_CUSTOMER
SET gdpr_col = CHAR_TO_LABEL('OLS_DEMO_GDPR','CNST::EMAIL,ANALYTICS')
where customerid >= 389;3 rows updated.-- . FRGT - Asked to be forgotten: 5 records
SQL>
UPDATE APPCRM.CRM_CUSTOMER
SET gdpr_col = CHAR_TO_LABEL('OLS_DEMO_GDPR','FRGT')
where customerid between 61 and 65;5 rows updated.-- . NCNST - Did not consent or revoked consent: 50 records
SQL>
UPDATE APPCRM.CRM_CUSTOMER
SET GDPR_COL = CHAR_TO_LABEL('OLS_DEMO_GDPR','NCNST')
where customerid between 1 and 50;50 rows updated.Commit complete.. Show the count per Label
SQL>
SELECT LABEL_TO_CHAR (GDPR_COL) label, count(*) countFROM APPCRM.CRM_CUSTOMERGROUP BY GDPR_COLORDER BY label;LABEL COUNT
-------------------------------------------------- --------
ANON 10
CNST::ANALYTICS 200
CNST::EMAIL 123
CNST::EMAIL,ANALYTICS 3
FRGT 5
NCNST 506 rows selected.
其中，CHAR_TO_LABEL的第1个参数为policy name，第二个参数为label。
此脚本更新数据标签以创建将在场景中使用的各种标签。在现实世界的场景中，建议创建一个标签函数，该函数将根据其他现有表数据（其他列）分配标签。对于每个步骤，您可以查看您执行的脚本的输出（例如“more ols_label_data.out”）
然后我们将看到标签安全性的作用，用不同的用户查看同一张表：
$ $ ./ols_label_sec_in_action.sh==============================================================================Connects as different apps would be connecting to see records that they would be able to process...
==============================================================================. Marketing App would only show 126 records
(Can process data labeled: CNST::EMAIL and CNST::ANALYTICS, EMAIL)COUNT(*)
----------126. BI App would only show 213 records
(Can process data labeled: ANON, CNST::ANALYTICS, CNST::ANALYTICS, EMAIL)COUNT(*)
----------213. FORGET App would only show 15 records
(Can process data labeled: FRGT and ANON)COUNT(*)
----------15. APPPREFERENCE App can be used to set consent
(Can process ALL records - 391)COUNT(*)
----------391. What labels are currently in session?LABEL
------------------------------------------------------------------------------------------------------------------------------------------------------------------
NCNST::DT_PROC,CAMP_MGMT,EMAIL,POST_MAIL,WEB_ADS,ANALYTICS,REC_ENGINE,THIRDPARTY,CONTACT_DET,PREF_DETAILS,PURCH_HIST. What is the session row label?SA_SESSION.ROW_LABEL('OLS_DEMO_GDPR')
------------------------------------------------------------------------------------------------------------------------------------------------------------------
CNST::DT_PROC
每个应用程序只会看到他们能够处理的记录。例如。 AppMKT（用于向客户发送电子邮件的应用程序）只能查看标记为 CNST::EMAIL 的记录； AppBI 将能够查看标记为 ANON 和 CNST::ANALYTICS 的记录（标记为 CNST 级别的行，以及 Group Analytics 的一部分——也适用于 CNST::ANALYTICS、EMAIL）。
现在，我们将 UserID(100) 的状态更改为被遗忘。
$ ./ols_to_be_forgotten.sh==============================================================================Change users status to be forgotten...
==============================================================================CON_NAME
------------------------------
PDB1
USER is "APPFORGET". Create the procedure "PROCESS_DATA" to process requests to be forgotten for anonymization purposesProcedure created.. These would be the records to be anonimized
... User Session Label = FRGT
... ... Processing Data for User_ID (61): Rob Kempt (rob.kempt@aabz.com)
... ... Processing Data for User_ID (62): Elaine Moncure (elaine.moncure@aab0.com)
... ... Processing Data for User_ID (63): Joshua Disano (joshua.disano@aab1.com)
... ... Processing Data for User_ID (64): Lai Kurtich (lai.kurtich@aab2.com)
... ... Processing Data for User_ID (65): Lucas Summerill (lucas.summerill@aab3.com)
... Customers Processed = 5PL/SQL procedure successfully completed.. Create the procedure "FORGET_ME" to forget customersProcedure created.. How many records are currently marked "FRGT"LABEL COUNT
------------------------- -------
ANON 10
CNST::ANALYTICS 200
CNST::EMAIL 123
CNST::EMAIL,ANALYTICS 3
FRGT 5
NCNST 506 rows selected.. The User ID "100" asked to be forgotten
$ exec forget_me(100)PL/SQL procedure successfully completed.. Now, let's check how many records are marked "FRGT"LABEL COUNT
------------------------- -------
ANON 10
CNST::ANALYTICS 199
CNST::EMAIL 123
CNST::EMAIL,ANALYTICS 3
FRGT 6
NCNST 506 rows selected.. These would be the records to be anonimized
... User Session Label = FRGT
... ... Processing Data for User_ID (61): Rob Kempt (rob.kempt@aabz.com)
... ... Processing Data for User_ID (62): Elaine Moncure (elaine.moncure@aab0.com)
... ... Processing Data for User_ID (63): Joshua Disano (joshua.disano@aab1.com)
... ... Processing Data for User_ID (64): Lai Kurtich (lai.kurtich@aab2.com)
... ... Processing Data for User_ID (65): Lucas Summerill (lucas.summerill@aab3.com)
... ... Processing Data for User_ID (100): Verlie Ashland (verlie.ashland@aac2.com)
... Customers Processed = 6PL/SQL procedure successfully completed.
注意：
- 此脚本模拟一个应用程序，该应用程序将处理标记为被遗忘的记录
- 它创建一个存储过程来显示标记为被遗忘的记录（标记为 FRGT:😃
- 它还在 AppPreference 应用模式下创建一个过程，用于忘记某个客户
- AppPreference 可以访问所有数据，forget_me(p_id) 过程将标记某个 customerid 行 FRGT::“移动”一条记录从 Consent 到 Forgotten……在我们的示例中，我们将更改 UserID(100) 的状态：forget_me(100)
- 之后，我们检查其状态是否已正确更改为被遗忘
- 对于每个步骤，您可以查看您执行的脚本的输出（例如“more ols_to_be_forgotten.out”）
最后清理环境（删除 OLS 策略和用户）
$ ./ols_clean_env.sh==============================================================================Clean the OLS environment...
==============================================================================CON_NAME
------------------------------
PDB1
USER is "C##OSCAR_OLS". List the current OLS objects
... OLS PoliciesPOLICY_NAME COLUMN_NAME STATUS
-------------------- -------------------- ---------------
OLS_DEMO_GDPR GDPR_COL ENABLED1 row selected.... OLS LevelsPOLICY_NAME LEVEL_NUM SHORT_NAME
-------------------- ---------- ---------------
OLS_DEMO_GDPR 10 CNST
OLS_DEMO_GDPR 20 ANON
OLS_DEMO_GDPR 30 FRGT
OLS_DEMO_GDPR 40 NCNST4 rows selected.... OLS GroupsPOLICY_NAME GROUP_NUM SHORT_NAME
-------------------- ---------- ---------------
OLS_DEMO_GDPR 1200 ANALYTICS
OLS_DEMO_GDPR 1100 CAMP_MGMT
OLS_DEMO_GDPR 1310 CONTACT_DET
OLS_DEMO_GDPR 1000 DT_PROC
OLS_DEMO_GDPR 1110 EMAIL
OLS_DEMO_GDPR 1120 POST_MAIL
OLS_DEMO_GDPR 1320 PREF_DETAILS
OLS_DEMO_GDPR 1330 PURCH_HIST
OLS_DEMO_GDPR 1210 REC_ENGINE
OLS_DEMO_GDPR 1300 THIRDPARTY
OLS_DEMO_GDPR 1130 WEB_ADS11 rows selected.. Delete the OLS policy "OLS_DEMO_GDPR"PL/SQL procedure successfully completed.no rows selected. List the OLS objects after deleting
... OLS Policiesno rows selected... OLS Levelsno rows selected... OLS Groupsno rows selected. Dropping OLS Lab usersUser dropped.User dropped.User dropped.User dropped.User dropped.User dropped.. Disable OLSPL/SQL procedure successfully completed.. Drop OLS Admin userUser dropped.
Task 2: Protect Glassfish Application
首先，设置 Glassfish 应用程序环境……并确保您尚未将 OLS 更改部署到应用程序。
$ ./ols_setup_glassfish_env.sh==============================================================================Setup the Glassfish environment...
==============================================================================. Check Glassfish environmentGlassfish App (hr_prod_pdb1) is ready for the labs!
接下来，为 Glassfish 设置 OLS 策略：
$ ./ols_setup_glassfish_policy.sh==============================================================================Setup the OLS policy for Glassfish...
==============================================================================. Configure OLS for CDB
USER is "SYS"User altered.PDB_NAME NAME STATUS DESCRIPTION
------------ ------------------------- ------------ ----------------------------------------
PDB1 OLS_CONFIGURE_STATUS TRUE Determines if OLS is configured
PDB1 OLS_DIRECTORY_STATUS FALSE Determines if OID is enabled with OLS
PDB1 OLS_ENABLE_STATUS FALSE Determines if OLS is enabled
PDB2 OLS_CONFIGURE_STATUS FALSE Determines if OLS is configured
PDB2 OLS_DIRECTORY_STATUS FALSE Determines if OID is enabled with OLS
PDB2 OLS_ENABLE_STATUS FALSE Determines if OLS is enabled
CDB$ROOT OLS_CONFIGURE_STATUS FALSE Determines if OLS is configured
CDB$ROOT OLS_DIRECTORY_STATUS FALSE Determines if OID is enabled with OLS
CDB$ROOT OLS_ENABLE_STATUS FALSE Determines if OLS is enabled9 rows selected.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.. Reboot DB
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.Pluggable database altered.. Configure OLS for PDBCON_NAME
------------------------------
PDB1
USER is "SYS"Grant succeeded.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.. Reboot PDBPluggable database altered.Pluggable database altered.CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 PDB1 READ WRITE NO4 PDB2 READ WRITE NO. Configure OLS PolicyCON_NAME
------------------------------
PDB1
USER is "LBACSYS"
... Create OLS Policy "OLS_DEMO_HR_APP" on "DEMO_HR_EMPLOYEES" table
... ... Drop existing OLS Policy
begin
*
ERROR at line 1:
ORA-12416: policy OLS_DEMO_HR_APP not found
ORA-06512: at "LBACSYS.LBAC_SYSDBA", line 685
ORA-06512: at "LBACSYS.LBAC_STANDARD", line 20
ORA-06512: at "LBACSYS.LBAC_SYSDBA", line 52
ORA-06512: at "LBACSYS.LBAC_SYSDBA", line 575
ORA-06512: at "LBACSYS.SA_SYSDBA", line 53
ORA-06512: at line 2... ... Create the OLS Policy "OLS_DEMO_HR_APP"PL/SQL procedure successfully completed.... ... Disable OLS PolicyPL/SQL procedure successfully completed.... Create OLS Levels
... ... Create "Public" OLS LevelPL/SQL procedure successfully completed.... ... Create "Confidential" OLS LevelPL/SQL procedure successfully completed.... ... Create "Highly Confidential" OLS LevelPL/SQL procedure successfully completed.... Create OLS Compartments
... ... Create "HR" compartmentPL/SQL procedure successfully completed.... ... Create "FIN" compartmentPL/SQL procedure successfully completed.... ... Create "IP" compartmentPL/SQL procedure successfully completed.... ... Create "IT" compartmentPL/SQL procedure successfully completed.... Create OLS Groups
... ... Create "GBL" OLS GroupPL/SQL procedure successfully completed.... ... Create "USA" OLS GroupPL/SQL procedure successfully completed.... ... Create "CAN" OLS GroupPL/SQL procedure successfully completed.... ... Create "LTM" OLS GroupPL/SQL procedure successfully completed.... ... Create "EU" OLS GroupPL/SQL procedure successfully completed.... ... Create "GER" OLS GroupPL/SQL procedure successfully completed.... Set EMPLOYEESERCH to have highest level, all compartments, and GBL groupPL/SQL procedure successfully completed.... Create OLS LabelsPL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.... Generate Data Labels for our combinations of Levels, Compartments, or Groups
... ... Generate Data Labels for our combinationsTO_DATA_LABEL('OLS_DEMO_HR_APP','P')
------------------------------------1000TO_DATA_LABEL('OLS_DEMO_HR_APP','C')
------------------------------------3000TO_DATA_LABEL('OLS_DEMO_HR_APP','HC')
-------------------------------------5000TO_DATA_LABEL('OLS_DEMO_HR_APP','P:IT')
---------------------------------------1000000025TO_DATA_LABEL('OLS_DEMO_HR_APP','P:IP')
---------------------------------------1000000026TO_DATA_LABEL('OLS_DEMO_HR_APP','P:HR')
---------------------------------------1000000027TO_DATA_LABEL('OLS_DEMO_HR_APP','P:FIN')
----------------------------------------1000000028... ... Create HC data labels with groups
create HC data labels with groupsTO_DATA_LABEL('OLS_DEMO_HR_APP','HC::GBL')
------------------------------------------1000000029TO_DATA_LABEL('OLS_DEMO_HR_APP','HC::USA')
------------------------------------------1000000030TO_DATA_LABEL('OLS_DEMO_HR_APP','HC::EU')
-----------------------------------------1000000031TO_DATA_LABEL('OLS_DEMO_HR_APP','HC::GER')
------------------------------------------1000000032TO_DATA_LABEL('OLS_DEMO_HR_APP','HC::CAN')
------------------------------------------1000000033TO_DATA_LABEL('OLS_DEMO_HR_APP','HC::LTM')
------------------------------------------1000000034... ... Create Public data labels with groups
create HC data labels with groupsTO_DATA_LABEL('OLS_DEMO_HR_APP','P::GBL')
-----------------------------------------1000000035TO_DATA_LABEL('OLS_DEMO_HR_APP','P::USA')
-----------------------------------------1100TO_DATA_LABEL('OLS_DEMO_HR_APP','P::EU')
----------------------------------------1300TO_DATA_LABEL('OLS_DEMO_HR_APP','P::GER')
-----------------------------------------1000000036TO_DATA_LABEL('OLS_DEMO_HR_APP','P::LTM')
-----------------------------------------1000000037TO_DATA_LABEL('OLS_DEMO_HR_APP','P::CAN')
-----------------------------------------1000000038... ... Create EMPLOYEESEARCH_PROD data labels with everythingTO_DATA_LABEL('OLS_DEMO_HR_APP','HC:IT,FIN,HR,IP:GBL,USA,EU,GER,LTM,CAN')
-------------------------------------------------------------------------1000000039... ... Create EMPLOYEESEARCH_PROD data labels with everythingTO_DATA_LABEL('OLS_DEMO_HR_APP','HC:IT,FIN,HR,IP:GBL')
------------------------------------------------------1000000023... Setup User EMPLOYEESEARCH_PROD
... ... Set Levels for UserPL/SQL procedure successfully completed.... ... Set Group for UserPL/SQL procedure successfully completed.... ... Give EMPLOYEESEARCH_PROD NULL privileges on OLS controlled objectsPL/SQL procedure successfully completed.
注意：
- 此脚本启用 OLS，因此它将重新启动数据库
- 然后，它创建名为 OLS_DEMO_HR_APP 的 OLS 策略以及级别（PUBLIC、CONFIDENTIAL、HIGHLY - CONFIDENTIAL）、隔间（HR、FIN、IP、IT）和 OLS 组（GLOBAL、USA、CANADA、LATAM、EU、GERMAN）
- 它还生成将使用的数据标签
- 这允许我们将数字分配给我们想要拥有的 label_tag
- 对于每个步骤，您都可以查看您执行的脚本的输出（例如“more ols_setup_glassfish_policy.out”）
创建 EMPLOYEESEARCH 应用环境：
$ ./ols_config_employeesearch_app.sh==============================================================================Create EMPLOYEESEARCH App...
==============================================================================CON_NAME
------------------------------
PDB1
USER is "EMPLOYEESEARCH_PROD". Make sure we remove our demo users so we can start fresh2 rows deleted.2 rows deleted.. Create our User Labels table so we can set the label for our app users when they loginTable dropped.Table created.Table altered.. Insert all users from DEMO_HR_USERS into our DEMO_HR_USER_LABELS table and give them 'P' as their label10 rows created.. Create our demo users
Create our demo users within the HR APP table. Insert values
... Canadian Lady1 row created.1 row created.1 row created.... Insert our EU Gentleman1 row created.1 row created.1 row created.1 row created.1 row deleted.1 row created.. Make all of our HR App Users have "Public" access level12 rows updated.. Set the OLSLABEL for hradmin, eu_evan, and can_candy in our DEMO_HR_USER_LABELS table1 row updated.1 row updated.1 row updated.Commit complete.. Create our error log tableTable dropped.Table created.. Create our "EMPLOYEESEARCH_PROD.SET_APP_USER_LABEL" procedure
This should be updated to accept p_policy_name too so we can pass the policy name dynamicallyProcedure created.No errors.
- 此脚本将为应用程序用户标签 EMPLOYEESEARCH_PROD.DEMO_HR_USER_LABELS 创建一个自定义表，并使用 EMPLOYEESEARCH_PROD.DEMO_HR_USERS 中的所有行填充它
- 该脚本还将创建一些我们将在本练习中使用的其他用户，例如 CAN_CANDY、EU_EVAN，然后将适当的 OLS 用户标签授予所有应用程序用户
打开网络浏览器并启动 Glassfish 应用程序：
- 链接为：http://<YOUR_DBSEC-LAB_VM_PUBLIC_IP>:8080/hr_prod_pdb1
- 用户名和口令为can_candy/Oracle123
- 单击Search Employees， 然后点击Search
- 登出
- 已用户eu_evan登录，口令为Oracle123
- 单击Search Employees， 然后点击Search
这2个用户均能看到所有数据。
返回您的终端会话并将 OLS 策略应用于 EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES 表：
./ols_apply_policy.sh
执行的代码和输出如下：
$ ./ols_apply_policy.sh==============================================================================Apply the policy to EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES...
==============================================================================
USER is "LBACSYS"-- . Apply the policy
SQL>
beginSA_POLICY_ADMIN.APPLY_TABLE_POLICY(policy_name => 'OLS_DEMO_HR_APP', schema_name =>'EMPLOYEESEARCH_PROD', table_name =>'DEMO_HR_EMPLOYEES');
end;
/PL/SQL procedure successfully completed.
将 OLS 策略应用于表后，只有具有授权标签或 OLS 权限的用户才能查看数据。
现在，更新 EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES 表以使用适当的 OLS 数字标签填充 OLSLABEL 列。
./ols_set_row_labels.sh
执行的代码和输出为：
==============================================================================Update EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES table to populate theOLSLABEL column with the appropriate OLS numeric label...
==============================================================================
USER is "EMPLOYEESEARCH_PROD". Set the row labels in EMPLOYEESEARCH_PROD.DEMO_HR_EMPLOYEES
SQL> update EMPLOYEESEARCH_PROD.demo_hr_employees set city = 'Berlin' where city is null;0 rows updated.SQL> update EMPLOYEESEARCH_PROD.demo_hr_employees set olslabel = char_to_label('OLS_DEMO_HR_APP','P::GER') where city = 'Berlin';
125 rows updated.SQL>
update EMPLOYEESEARCH_PROD.demo_hr_employees set olslabel = char_to_label('OLS_DEMO_HR_APP','P::USA') where city in ('Costa Mesa','New York','Santa Clara','Sunnyvale');470 rows updated.SQL>
update EMPLOYEESEARCH_PROD.demo_hr_employees set olslabel = char_to_label('OLS_DEMO_HR_APP','P::EU') where city in ('Paris','London');243 rows updated.SQL>
update EMPLOYEESEARCH_PROD.demo_hr_employees set olslabel = char_to_label('OLS_DEMO_HR_APP','P::CAN') where city in ('Toronto');162 rows updated.Commit complete.
我们将根据表中的 CITY 列执行此操作；例如，“Berlin”将收到 P::GER 的 OLS 标签，因为它们属于 GERMANY 组。
看看策略输出是什么样的：
$ ./ols_verify_our_policy.sh==============================================================================Verify the impact of the OLS Policy...
==============================================================================
USER is "EMPLOYEESEARCH_PROD". First we will clean up the DEMO_HR_ERROR_LOG table so we can see errors if we have themTable truncated.no rows selected. Before we query based on OLS, lets get an overview of the data we will work with
(This is all of the rows in DEMO_HR_EMPLOYEES)COUNT(*)
----------1000. Here is the distribution of city dataCITY COUNT_CITY
------------------------------ ----------
Berlin 125
Costa Mesa 136
London 122
New York 113
Paris 121
Santa Clara 109
Sunnyvale 112
Toronto 1628 rows selected.. Enable our Policy now that we have labeled data into DEMO_HR_EMPLOYEES
USER is "LBACSYS"PL/SQL procedure successfully completed.. This is what EMPLOYEESEARCH_PROD sees:
... This is what our DB user, and schema owner, EMPLOYEESEARCH_PROD has for a label
He should have everything because we want him to be able to see every combination of labelsOLS_READ_LABEL
----------------------------------------
HC:HR,FIN,IP,IT:GBL,USA,CAN,EU,GER,LTM. This is what HRADMIN sees:
... This is what app user HRADMIN sees when we use our procedure to set his label
His label is set based on his row value in DEMO_HR_USER_LABELSPL/SQL procedure successfully completed.... This is what our app user HRADMIN has for a label
He should have everything because we want him to be able to see every combination of labelsOLS_READ_LABEL
----------------------------------------
HC::GBL,USA,CAN,EU,GER,LTM... How many rows get returned? It should be every row in the tableCOUNT(*)
----------1000... How many cities can be seen by app user HRADMINCITY COUNT_CITY
------------------------------ ----------
Berlin 125
Costa Mesa 136
London 122
New York 113
Paris 121
Santa Clara 109
Sunnyvale 112
Toronto 1628 rows selected.... How many distinct OLSLABELS, and the varchar translation, are accessibleOLSLABEL CHAR_LABEL
---------- ----------------------------------------
1000000038 P::CAN1300 P::EU1100 P::USA
1000000036 P::GER. This is what App User EU_EVAN sees:
... When we use our procedure to set his label
His label is set based on his row value in DEMO_HR_USER_LABELSPL/SQL procedure successfully completed.OLS_READ_LABEL
----------------------------------------
P::EU,GERCOUNT(*)
----------368... How many cities can be seen by app user EU_EVANCITY COUNT_CITY
------------------------------ ----------
Berlin 125
London 122
Paris 121... How many distinct OLSLABELS, and the varchar translation, are accessible by app user EU_EVAN
His label is set based on his row value in DEMO_HR_USER_LABELSOLSLABEL CHAR_LABEL
---------- ----------------------------------------1300 P::EU
1000000036 P::GER. This is what App User CAN_CANDY sees:
... This is what app user CAN_CANDY sees when we use our procedure to set her label
Her label is set based on his row value in DEMO_HR_USER_LABELSPL/SQL procedure successfully completed.OLS_READ_LABEL
----------------------------------------
P::CANCOUNT(*)
----------162... How many cities can be seen by app user CAN_CANDYCITY COUNT_CITY
------------------------------ ----------
Toronto 162... How many distinct OLSLABELS, and the varchar translation, are accessible by app user CAN_CANDYOLSLABEL CHAR_LABEL
---------- ----------------------------------------
1000000038 P::CAN
最后，我们更改 Glassfish 应用程序配置文件以嵌入 OLS 策略……此脚本将引导您完成我们需要添加的所有内容：
$ ./ols_upd_glassfish.sh==============================================================================Update Glassfish environment to protect the app with OLS policy...
==============================================================================. These are the updated .jsp files we will be using
-rwxr-xr-x. 1 oracle oinstall 17643 Jan 4 16:50 /home/oracle/DBSecLab/livelabs/label-security/hr_prod_pdb1_ols/controller.jsp
-rwxr-xr-x. 1 oracle oinstall 17290 Jan 4 16:50 /home/oracle/DBSecLab/livelabs/label-security/hr_prod_pdb1_ols/employee_create.jsp
-rwxr-xr-x. 1 oracle oinstall 18637 Jan 4 16:50 /home/oracle/DBSecLab/livelabs/label-security/hr_prod_pdb1_ols/employee_view.jsp
-rwxr-xr-x. 1 oracle oinstall 14007 Jan 4 16:50 /home/oracle/DBSecLab/livelabs/label-security/hr_prod_pdb1_ols/search.jsp
-rwxr-xr-x. 1 oracle oinstall 23157 Jan 4 16:50 /home/oracle/DBSecLab/livelabs/label-security/hr_prod_pdb1_ols/session_data.jsp. We have added an additional call to the DB to execute our set_app_user_label procedure// *******************************************System.out.println ("***** SEARCH.JSP *****");CallableStatement stmt2 = null;String sql = "{call employeesearch_prod.set_app_user_label ( ? )}";stmt2 = conn.prepareCall(sql);stmt2.setString (1, oLoggedUser);stmt2.executeQuery();System.out.println (oLoggedUser);System.out.println( sql );// *******************************************. Changing the Glassfish conf files to embbed OLS policy
-rwxr-xr-x. 1 oracle oinstall 13766 Jan 4 16:43 /u01/app/glassfish/hr_prod_pdb1/search_engineering.jsp
-rwxr-xr-x. 1 oracle oinstall 17643 Apr 7 09:02 /u01/app/glassfish/hr_prod_pdb1/controller.jsp
-rwxr-xr-x. 1 oracle oinstall 17290 Apr 7 09:02 /u01/app/glassfish/hr_prod_pdb1/employee_create.jsp
-rwxr-xr-x. 1 oracle oinstall 18637 Apr 7 09:02 /u01/app/glassfish/hr_prod_pdb1/employee_view.jsp
-rwxr-xr-x. 1 oracle oinstall 14007 Apr 7 09:02 /u01/app/glassfish/hr_prod_pdb1/search.jsp
-rwxr-xr-x. 1 oracle oinstall 23157 Apr 7 09:02 /u01/app/glassfish/hr_prod_pdb1/session_data.jsp. Reboot the Glassfish App to enable the new settingsWaiting for the domain to stop .
Command stop-domain executed successfully.
Waiting for domain1 to start .....
Successfully started the domain : domain1
domain Location: /u01/app/glassfish/glassfish4/glassfish/domains/domain1
Log File: /u01/app/glassfish/glassfish4/glassfish/domains/domain1/logs/server.log
Admin Port: 4848
Command start-domain executed successfully.. Now you should be able to login and see how we have updated our application to prevent application users from seeing data. If you would like to follow along with the Glassfish log fileyou can do so by opening a terminal and executing:$ tail -f /u01/app/glassfish/glassfish/domains/domain1/logs/server.log
重新在Glassfish应用中用can_candy和eu_evan登录并查看数据。此时，前者只能看到加拿大的用户，后者只能看到欧洲用户。而hradmin可以看到所有用户。
完成实验后，您可以删除策略并将 Glassfish JSP 文件恢复到其原始状态：
./ols_restore_glassfish_env.sh
Appendix: About the Product
OLS 通过将行标签与用户的标签授权进行比较，使您能够轻松地将敏感信息限制为仅限授权用户。
这样，具有不同授权级别的用户（例如，经理和销售代表）可以访问表中的特定数据行。 您可以将 OLS 策略应用于一个或多个应用程序表。 OLS 的设计类似于 Oracle 虚拟专用数据库 (VPD)。 但是，与 VPD 不同的是，OLS 提供了开箱即用的访问中介功能、数据字典表和基于策略的架构，从而消除了自定义编码并提供了可被多个应用程序使用的基于标签的一致访问控制模型。
OLS 基于政府和国防组织中的多级安全 (MLS) 要求。 OLS 软件默认安装，但不会自动启用。 您可以在 SQLPlus 中或使用 Oracle 数据库配置助手 (DBCA) 启用 OLS。 OLS 的默认管理员是用户 LBACSYS。 要管理 OLS，您可以使用一组 PL/SQL 包和命令行级别的独立函数或 Oracle Enterprise Manager Cloud Control。 要查找有关 OLS 策略的信息，您可以查询 ALL_SA_*、DBA_SA_* 或 USER_SA_* 数据字典视图。
OLS 策略具有一组标准组件，如下所示：
- 标签（Labels）：数据和用户的标签，以及用户和程序单元的授权，管控对指定受保护对象的访问。 标签由以下部分组成：
- 级别（Levels）：级别指示要分配给行的敏感度类型（例如，敏感或高度敏感）。 级别是强制性的。
- 分区（Compartments ，可选）：数据可以具有相同的级别（例如，PUBLIC、CONFIDENTIAL 和 SECRET），但可以属于公司内部的不同项目（例如，ACME 合并和 IT 安全）。 分区代表此示例中的项目，这些项目有助于定义更精确的访问控制。 它们最常用于政府环境。
- 组（Groups，可选）：组标识拥有或访问数据的组织（例如，英国、美国、亚洲、欧洲）。 组在商业和政府环境中都使用，并且由于其灵活性而经常用于代替分区。
- 策略（Policy）：策略是与这些标签、规则、授权和受保护表相关联的名称。
OLS 为控制行级管理提供了几个好处：
- 它支持行级数据分类，并根据数据分类和用户标签授权或安全许可提供开箱即用的访问仲裁。
- 它使您能够为数据库用户和应用程序用户分配标签授权或安全许可。
- 它提供 API 和图形用户界面，用于定义和存储数据分类标签和用户标签授权。
- 它与 Oracle Database Vault 和 Oracle Advanced Security Data Redaction 集成，使安全审核可用于 Database Vault 命令规则和 Data Redaction 策略定义。
Want to Learn More?
参考文档：Oracle Label Security 19c
Acknowledgements
本实验的作者为Hakim Loumi，数据库安全PM；贡献者为Alan Williams和Rene Fontcha。
这篇关于Oracle LiveLabs实验：DB Security - Oracle Label Security (OLS)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
