Oracle LiveLabs实验：DB Security - Oracle Label Security (OLS)

dingdingfish

已于 2022-04-23 10:18:01 修改

阅读量859

点赞数

分类专栏： Oracle LiveLabs Oracle数据库安全文章标签： oracle database security livelabs OLS

于 2022-04-07 17:07:13 首次发布

本文链接：https://blog.csdn.net/stevensxiao/article/details/124010358

版权

Oracle数据库安全同时被 2 个专栏收录

52 篇文章 1 订阅

订阅专栏

Oracle LiveLabs

32 篇文章 0 订阅

订阅专栏

概述

此实验申请地址在这里，时间为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 LEVELS

  10 - CONSENT (CNST)

  20 - ANONYMIZED (ANON)

  30 - FORGET (FRGT)

  40 - NOCONSENT (NCNST)

-------------------------------------------


... Create CONSENT level

PL/SQL procedure successfully completed.

... Create ANONYMIZED level

PL/SQL procedure successfully completed.

... Create FORGET level

PL/SQL procedure successfully completed.

... Create NOCONSENT level

PL/SQL procedure successfully completed.


---------------------------------------------------------

. STEP 3: CREATE GROUPS

  Here we used a hierarchy of groups to control

  which data can be processed (based on given consent):

  1000 - DATA_PROCESSING (DT_PROD)

    1100 - CAMPAIGN_MGMT (CAMP_MGMT)

         1110 - EMAIL

         1120 - POST_MAIL

         1130 - WEB_ADS

    1200 - ANALYTICS

         1210 - RECOMMENDATION_ENGINE (REC_ENGINE)

    1300 - THIRDPARTY

         1310 - CONTACT_DETAILS (CONTACT_DET)

         1320 - PREFERENCE_DETAILS (PREF_DETAILS)

         1330 - PURCHASE_HIST (PURCH_HIST)

---------------------------------------------------------


... Create DATA_PROCESSING group

PL/SQL procedure successfully completed.

... ... Create CAMPAIGN_MGMT group

PL/SQL procedure successfully completed.

... ... ... Create EMAIL group

PL/SQL procedure successfully completed.

... ... ... Create POST_MAIL group

PL/SQL procedure successfully completed.

... ... ... Create ONLINE_ADS group

PL/SQL procedure successfully completed.

... ... Create ANALYTICS group

PL/SQL procedure successfully completed.

... ... ... Create REC_ENGINE group

PL/SQL procedure successfully completed.

... ... Create THIRDPARTY group

PL/SQL procedure successfully completed.

... ... ... Create CONTACT_DETAILS group

PL/SQL procedure successfully completed.

... ... ... Create PREFERENCE_DETAILS group

PL/SQL procedure successfully completed.

... ... ... Create PURCHASE_HIST group

PL/SQL procedure successfully completed.


------------------------------------------------------------

. STEP 4: CREATE LABELS

  The label is automatically designated as a valid data label

  This functionality limits the labels that can be assigned to data

  If a user widthraws consent the row label will have that compartment removed

  Allowed Labels (Trim down/add to suite the use cases):

  CNST::                                                500

  FORGET::                                              700

  ANON::                                                800

  NOCONSENT::                                           999

  ---------

    CNST::DT_PROC                                      1000

      CNST::CAMP_MGMT                                  1100

        CNST::EMAIL                                    1110

        CNST::POST_MAIL                                1120

        CNST::WEB_ADS                                  1130

        CNST::EMAIL,POST_MAIL                          1140

        CNST::EMAIL,ANALYTICS                          1145

        CNST::EMAIL,WEB_ADS                            1150

        CNST::CAMP_MGMT,ANALYTICS,THIRDPARTY           1160

        CNST::CAMP_MGMT,ANALYTICS                      1170

        CNST::CAMP_MGMT,THIRDPARTY                     1180

        CNST::ANALYTICS,THIRDPARTY                     1190

        CNST::POST_MAIL,WEB_ADS                        1195

  ---------

      CNST::ANALYTICS                                  1200

        CNST::REC_ENGINE                               1210

  ---------

      CNST::THIRDPARTY                                 1300

        CNST::CONTACT_DETAILS                          1310

        CNST::PREF_DETAILS                             1320

        CNST::PURCH_HIST                               1330

        CNST::CONTACT_DETAILS,PREF_DETAILS             1340

        CNST::CONTACT_DETAILS,PURCH_HIST               1350

        CNST::PREF_DETAILS,PURCH_HIST                  1360

------------------------------------------------------------

...
. STEP 5: ASSING LEVELS TO USERS

  Users                | 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 APPPREFERENCE

PL/SQL procedure successfully completed.

... ... prompt Set Group for APPPREFERENCE

PL/SQL procedure successfully completed.

... Set Level for APPFORGET

PL/SQL procedure successfully completed.

... Set Level for APPMKT

PL/SQL procedure successfully completed.

... ... Set Group for APPMKT

PL/SQL procedure successfully completed.

... Set Level for APPBI

PL/SQL procedure successfully completed.

... ... Set Group for APPBI

PL/SQL procedure successfully completed.

... Set Level for APP3RD

PL/SQL procedure successfully completed.

... ... Set Group for APP3RD

PL/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(*) count
  FROM APPCRM.CRM_CUSTOMER
 GROUP BY GDPR_COL
 ORDER BY label;

LABEL                                                 COUNT
-------------------------------------------------- --------
ANON                                                     10
CNST::ANALYTICS                                         200
CNST::EMAIL                                             123
CNST::EMAIL,ANALYTICS                                     3
FRGT                                                      5
NCNST                                                    50

6 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 purposes

Procedure 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 = 5

PL/SQL procedure successfully completed.


. Create the procedure "FORGET_ME" to forget customers

Procedure 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                          50

6 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                          50

6 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 = 6

PL/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 Policies

POLICY_NAME          COLUMN_NAME          STATUS
-------------------- -------------------- ---------------
OLS_DEMO_GDPR        GDPR_COL             ENABLED

1 row selected.

... OLS Levels

POLICY_NAME           LEVEL_NUM SHORT_NAME
-------------------- ---------- ---------------
OLS_DEMO_GDPR                10 CNST
OLS_DEMO_GDPR                20 ANON
OLS_DEMO_GDPR                30 FRGT
OLS_DEMO_GDPR                40 NCNST

4 rows selected.

... OLS Groups

POLICY_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_ADS

11 rows selected.


. Delete the OLS policy "OLS_DEMO_GDPR"

PL/SQL procedure successfully completed.


no rows selected


. List the OLS objects after deleting
... OLS Policies

no rows selected

... OLS Levels

no rows selected

... OLS Groups

no rows selected


. Dropping OLS Lab users

User dropped.


User dropped.


User dropped.


User dropped.


User dropped.


User dropped.


. Disable OLS

PL/SQL procedure successfully completed.


. Drop OLS Admin user

User dropped.

Task 2: Protect Glassfish Application

首先，设置 Glassfish 应用程序环境……并确保您尚未将 OLS 更改部署到应用程序。

$ ./ols_setup_glassfish_env.sh

==============================================================================
 Setup the Glassfish environment...
==============================================================================

. Check Glassfish environment

Glassfish 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 enabled

9 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 PDB

CON_NAME
------------------------------
PDB1
USER is "SYS"

Grant succeeded.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


. Reboot PDB

Pluggable database altered.


Pluggable database altered.


    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO

. Configure OLS Policy

CON_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 Policy

PL/SQL procedure successfully completed.

... Create OLS Levels
... ... Create "Public" OLS Level

PL/SQL procedure successfully completed.

... ... Create "Confidential" OLS Level

PL/SQL procedure successfully completed.

... ... Create "Highly Confidential" OLS Level

PL/SQL procedure successfully completed.

... Create OLS Compartments
... ... Create "HR" compartment

PL/SQL procedure successfully completed.

... ... Create "FIN" compartment

PL/SQL procedure successfully completed.

... ... Create "IP" compartment

PL/SQL procedure successfully completed.

... ... Create "IT" compartment

PL/SQL procedure successfully completed.

... Create OLS Groups
... ... Create "GBL" OLS Group

PL/SQL procedure successfully completed.

... ... Create "USA" OLS Group

PL/SQL procedure successfully completed.

... ... Create "CAN" OLS Group

PL/SQL procedure successfully completed.

... ... Create "LTM" OLS Group

PL/SQL procedure successfully completed.

... ... Create "EU" OLS Group

PL/SQL procedure successfully completed.

... ... Create "GER" OLS Group

PL/SQL procedure successfully completed.

... Set EMPLOYEESERCH to have highest level, all compartments, and GBL group

PL/SQL procedure successfully completed.

... Create OLS Labels

PL/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 combinations

TO_DATA_LABEL('OLS_DEMO_HR_APP','P')
------------------------------------
                                1000


TO_DATA_LABEL('OLS_DEMO_HR_APP','C')
------------------------------------
                                3000


TO_DATA_LABEL('OLS_DEMO_HR_APP','HC')
-------------------------------------
                                 5000


TO_DATA_LABEL('OLS_DEMO_HR_APP','P:IT')
---------------------------------------
                             1000000025


TO_DATA_LABEL('OLS_DEMO_HR_APP','P:IP')
---------------------------------------
                             1000000026


TO_DATA_LABEL('OLS_DEMO_HR_APP','P:HR')
---------------------------------------
                             1000000027


TO_DATA_LABEL('OLS_DEMO_HR_APP','P:FIN')
----------------------------------------
                              1000000028

... ... Create HC data labels with groups
create HC data labels with groups

TO_DATA_LABEL('OLS_DEMO_HR_APP','HC::GBL')
------------------------------------------
                                1000000029


TO_DATA_LABEL('OLS_DEMO_HR_APP','HC::USA')
------------------------------------------
                                1000000030


TO_DATA_LABEL('OLS_DEMO_HR_APP','HC::EU')
-----------------------------------------
                               1000000031


TO_DATA_LABEL('OLS_DEMO_HR_APP','HC::GER')
------------------------------------------
                                1000000032


TO_DATA_LABEL('OLS_DEMO_HR_APP','HC::CAN')
------------------------------------------
                                1000000033


TO_DATA_LABEL('OLS_DEMO_HR_APP','HC::LTM')
------------------------------------------
                                1000000034

... ... Create Public data labels with groups
create HC data labels with groups

TO_DATA_LABEL('OLS_DEMO_HR_APP','P::GBL')
-----------------------------------------
                               1000000035


TO_DATA_LABEL('OLS_DEMO_HR_APP','P::USA')
-----------------------------------------
                                     1100


TO_DATA_LABEL('OLS_DEMO_HR_APP','P::EU')
----------------------------------------
                                    1300


TO_DATA_LABEL('OLS_DEMO_HR_APP','P::GER')
-----------------------------------------
                               1000000036


TO_DATA_LABEL('OLS_DEMO_HR_APP','P::LTM')
-----------------------------------------
                               1000000037


TO_DATA_LABEL('OLS_DEMO_HR_APP','P::CAN')
-----------------------------------------
                               1000000038

... ... Create EMPLOYEESEARCH_PROD data labels with everything

TO_DATA_LABEL('OLS_DEMO_HR_APP','HC:IT,FIN,HR,IP:GBL,USA,EU,GER,LTM,CAN')
-------------------------------------------------------------------------
                                                               1000000039

... ... Create EMPLOYEESEARCH_PROD data labels with everything

TO_DATA_LABEL('OLS_DEMO_HR_APP','HC:IT,FIN,HR,IP:GBL')
------------------------------------------------------
                                            1000000023

... Setup User EMPLOYEESEARCH_PROD
... ... Set Levels for User

PL/SQL procedure successfully completed.

... ... Set Group for User

PL/SQL procedure successfully completed.

... ... Give EMPLOYEESEARCH_PROD NULL privileges on OLS controlled objects

PL/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 fresh

2 rows deleted.


2 rows deleted.


. Create our User Labels table so we can set the label for our app users when they login

Table 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 label

10 rows created.


. Create our demo users
Create our demo users within the HR APP table

. Insert values
... Canadian Lady

1 row created.


1 row created.


1 row created.

... Insert our EU Gentleman

1 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 level

12 rows updated.


. Set the OLSLABEL for hradmin, eu_evan, and can_candy in our DEMO_HR_USER_LABELS table

1 row updated.


1 row updated.


1 row updated.


Commit complete.


. Create our error log table

Table 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 dynamically

Procedure 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> 
begin
 SA_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 the
 OLSLABEL 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 them

Table 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 data

CITY                           COUNT_CITY
------------------------------ ----------
Berlin                                125
Costa Mesa                            136
London                                122
New York                              113
Paris                                 121
Santa Clara                           109
Sunnyvale                             112
Toronto                               162

8 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 labels

OLS_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_LABELS


PL/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 labels


OLS_READ_LABEL
----------------------------------------
HC::GBL,USA,CAN,EU,GER,LTM


... How many rows get returned? It should be every row in the table

  COUNT(*)
----------
      1000


... How many cities can be seen by app user HRADMIN

CITY                           COUNT_CITY
------------------------------ ----------
Berlin                                125
Costa Mesa                            136
London                                122
New York                              113
Paris                                 121
Santa Clara                           109
Sunnyvale                             112
Toronto                               162

8 rows selected.


... How many distinct OLSLABELS, and the varchar translation, are accessible

  OLSLABEL CHAR_LABEL
---------- ----------------------------------------
1000000038 P::CAN
      1300 P::EU
      1100 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_LABELS

PL/SQL procedure successfully completed.


OLS_READ_LABEL
----------------------------------------
P::EU,GER


  COUNT(*)
----------
       368


... How many cities can be seen by app user EU_EVAN

CITY                           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_LABELS

  OLSLABEL 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_LABELS

PL/SQL procedure successfully completed.


OLS_READ_LABEL
----------------------------------------
P::CAN


  COUNT(*)
----------
       162


... How many cities can be seen by app user CAN_CANDY

CITY                           COUNT_CITY
------------------------------ ----------
Toronto                               162


... How many distinct OLSLABELS, and the varchar translation, are accessible by app user CAN_CANDY

  OLSLABEL 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 settings

Waiting 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 file
  you 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。

dingdingfish

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Oracle LiveLabs实验：DB Security - Oracle Label Security (OLS)

概述此实验申请地址在这里，时间为30分钟。本实验也是DB Security Advanced研讨会的的第7个实验，即Lab 7。实验帮助在这里。本实验使用的数据库为19.13。Introduction本研讨会介绍了 Oracle Label Security (OLS) 的各种特性和功能。它使用户有机会学习如何配置这些功能以保护其敏感数据，帮助跟踪允诺，并根据《通用数据保护条例》等法规要求强制限制处理。Task 1: Simple CRM Application不同的应用有不同的用途：
复制链接

扫一扫