July 8, 2013 at 23:56

Oracle Label Security. First meeting

    Oracle Label Security (OLS) is another way to differentiate data access in Oracle Database Server. A distinctive feature of OLS is the multilevel access policies and the storage of access control attributes along with data.

    Inside the policy, the levels of access to information are determined, for example: “top secret”, “secret”, “chipboard”. A user with a higher level of access has access to data marked with his level and levels below.

    Oracle Label Security is a paid option to Oracle Database Enterprise Edition.

    Below we will consider the basic concepts and an example of the use of OLS.

    Introduction


    Oracle Label Security uses Oracle Virtual Private Database (VPD) in its work , but unlike VPD, it does not require writing PL / SQL code. You can manage all aspects of OLS with both PL / SQL packages and Oracle Enterprise Manager .

    This article will discuss the implementation of OLS for the version of Oracle Databse 12c, with differences from previous versions can be found here .

    OLS Components


    The Labels (tags). Labels are a representation of data access attributes.
    Tags consist of the following components:
    • Levels (levels). They are represented as a number from 0 to 9999. The larger the number, the higher the access level.
    • Compartments (departments). An optional linear list that allows you to more accurately determine the level of access. For example: “Commercial department”, “IT department”, etc. In an example are not used.
    • Groups (group). Hierarchical structure, if access to the top is granted, then access to the leaves is automatically obtained. For example: Regional division, there is a peak - “all regions” and leaves - “Moscow”, “London”, etc. If there is access to “all regions”, then there is access to the regions “Moscow” and “London”. If there is access only to the Moscow region, then access to other regions is closed.

    The Policy (Policy). Link tags, tables, and authorization together.

    Formulation of the problem


    There is a company Z with a regional structure.
    The company operates a system that receives data on customer payments. They come from ERP and are entered in the PAYMENTS table .
    It is required to provide differentiation of read access, by the amount of payments and by region. Data on especially large payments should be stored in a separate partition, on an encrypted disk. When a payment is received from ERP, a label must be assigned to it taking into account the amount and region. Writing to a table is limited to standard oracle mechanisms and is possible only from ERP.

    The amounts of payments are divided into 3 parts:
    1. Big (large) - from 10000
    2. Medium (Medium) - 5,000 to 9,999
    3. Small (small) - up to 5000

    Regions:
    1. MO (Moscow and the region)
    2. NW (Northwest)

    Users:
    1. big_boss - has the maximum access level, sees all the data
    2. med_boss - sees payments from 5000 to 9999 in all regions
    3. mo_big_boss - sees all payments, but only in the MO region

    Implementation


    How to enable the Oracle Label Security option, see here .
    We create a table in the TEST scheme, which will receive payments:
    Hidden text
    create table PAYMENTS (
  ID number,
  CNAME  varchar2(64),  /* Название компании плательщика */
  REGION char(2),  /* Аббревиатура региона MO/NW */
  AMOUNT number, /* Сумма платежа */
  ols_payments number(10), /* Служебное поле для хранения метки */
  CONSTRAINT "PAYMENTS_PK" PRIMARY KEY ("ID")
  )
  PARTITION BY RANGE (ols_payments) (
   PARTITION SP VALUES LESS THAN (80000),
   PARTITION MP VALUES LESS THAN (90000),
   PARTITION BP VALUES LESS THAN (MAXVALUE) /* В реальной системе эта партиция будет располагаться на шифрованном диске */
  );
grant select on payments to public;


    The ols_payments column has been created in advance so that partitioning can be done. If this is not required, then it can be created automatically by applying policy.
    All further configuration work will go from the user LBACSYS .
    For each policy, a special role of the form policy_DBA is created. The role owner can change this policy.

    Create policy payments_pol :
    Hidden text
    EXEC SA_SYSDBA.CREATE_POLICY (
  policy_name      => 'payments_pol', /* Наименование policy */
  column_name      => 'ols_payments', /* Колонка таблицы для хранения меток */
  default_options  => 'read_control'); /* По умолчанию ограничиваем доступ на чтение */


    Create sum access levels for our policy:
    Hidden text
    BEGIN
 SA_COMPONENTS.CREATE_LEVEL (
   policy_name   => 'payments_pol',
   level_num     => 90,  /* Числовое значение */
   short_name    => 'B', /* Сокращенное наименование */
   long_name     => 'BIG_PAYMENT'); /* Полное наименование */
 SA_COMPONENTS.CREATE_LEVEL (
   policy_name   => 'payments_pol',
   level_num     => 80,
   short_name    => 'M',
   long_name     => 'MEDIUM_PAYMENT');
 SA_COMPONENTS.CREATE_LEVEL (
   policy_name   => 'payments_pol',
   level_num     => 70,
   short_name    => 'S',
   long_name     => 'SMALL_PAYMENT');
END;
/


    For each level, it is necessary to indicate its numerical value, abbreviated and full name. The abbreviated name will be used below in all references.

    We create groups by region:
    Hidden text
    BEGIN
  /* Корневая группа */
  SA_COMPONENTS.CREATE_GROUP (
   policy_name     => 'payments_pol',
   group_num       => 10,  /* Числовое заначение */
   short_name      => 'RG', /* Сокращенное наименование */
   long_name       => 'REGIONS'); /* Полное наименование */
  SA_COMPONENTS.CREATE_GROUP (
   policy_name     => 'payments_pol',
   group_num       => 20,
   short_name      => 'MO',
   long_name       => 'MOSCOW_REGION',
   parent_name     => 'RG'); /* родитель */
  SA_COMPONENTS.CREATE_GROUP (
   policy_name     => 'payments_pol',
   group_num       => 30,
   short_name      => 'NW',
   long_name       => 'NW_REGION',
   parent_name     => 'RG');
END;
/


    For each group, it is necessary to indicate its numerical value, abbreviated and full name. For all groups except the root, the parent is specified.

    The text format of the label looks like "LEVEL: COMPARTMENT1, .., COMPARTMENTN: GROUP1, ..., GROUPN".
    A label for a record containing more than 10,000 for the Moscow region will look like “B :: MO”.

    To display text labels in a numerical value, Label tags are created . Label tags can be generated automatically or manually defined. Since partitioning is used on a field containing Label tags, we create them manually. It is important to remember that the values ​​of Label tags must be unique within the framework of the database and cannot be duplicated in different policies. Label tags are in no way associated with group numbers or level numbers.

    Create tags:
    Hidden text
    BEGIN
-- RG labels
  SA_LABEL_ADMIN.CREATE_LABEL (
   policy_name     => 'payments_pol',
   label_tag       => 90010, /* Числовое передставление метки */
   label_value     => 'B::RG', /* Текстовое передставление метки */
   data_label      => TRUE);
  SA_LABEL_ADMIN.CREATE_LABEL (
   policy_name     => 'payments_pol',
   label_tag       => 80010,
   label_value     => 'M::RG',
   data_label      => TRUE);
  SA_LABEL_ADMIN.CREATE_LABEL (
   policy_name     => 'payments_pol',
   label_tag       => 70010,
   label_value     => 'S::RG',
   data_label      => TRUE);
-- MO LABELS
  SA_LABEL_ADMIN.CREATE_LABEL (
   policy_name     => 'payments_pol',
   label_tag       => 90020,
   label_value     => 'B::MO',
   data_label      => TRUE);
  SA_LABEL_ADMIN.CREATE_LABEL (
   policy_name     => 'payments_pol',
   label_tag       => 80020,
   label_value     => 'M::MO',
   data_label      => TRUE);
  SA_LABEL_ADMIN.CREATE_LABEL (
   policy_name     => 'payments_pol',
   label_tag       => 70020,
   label_value     => 'S::MO',
   data_label      => TRUE);
-- NW LABELS
  SA_LABEL_ADMIN.CREATE_LABEL (
   policy_name     => 'payments_pol',
   label_tag       => 90030,
   label_value     => 'B::NW',
   data_label      => TRUE);
  SA_LABEL_ADMIN.CREATE_LABEL (
   policy_name     => 'payments_pol',
   label_tag       => 80030,
   label_value     => 'M::NW',
   data_label      => TRUE);
  SA_LABEL_ADMIN.CREATE_LABEL (
   policy_name     => 'payments_pol',
   label_tag       => 70030,
   label_value     => 'S::NW',
   data_label      => TRUE);
END;
/


    It remains to write a function that generates labels according to the data:
    Hidden text
    create or replace function payments_label(region varchar2, amount number) return LBACSYS.LBAC_LABEL as 
  lvl varchar2(32);
  reg varchar2(32);
  rvalue number(10);
 begin
  case region
   when 'MO' then reg := 'MO';
   when 'NW' then reg := 'NW';
   else raise_application_error(-20000, 'invalid region.');
  end case;
  case 
   when amount >= 10000 then lvl := 'B';
   when amount >= 5000 then lvl := 'M';
   else lvl := 'S';
  end case;
   return LBACSYS.to_lbac_label('PAYMENTS_POL', lvl || '::' || reg);
end;


    And you can apply our policy to the table:
    Hidden text
    EXEC SA_POLICY_ADMIN.APPLY_TABLE_POLICY (
  policy_name    => 'payments_pol',
  schema_name    => 'test', 
  table_name     => 'payments',
  table_options  => 'READ_CONTROL,HIDE', /* контролировать чтение, не показывать служебную колонку */
  label_function => 'test.payments_label(:new.region,:new.amount)', /* функция генерации меток, вызов будет вставлен в автоматически созданные триггера на insert и update. Отсюда и названия параметров */
  predicate      => NULL);


    Fill the table with test data simulating the receipt of payments from ERP:
    Hidden text
    $ sqlplus test/test
insert into payments values(1, 'РОГА и КОПЫТА, Москва', 'MO', 100000);
insert into payments values(2, 'РОГА и КОПЫТА, Москва', 'MO', 7000);
insert into payments values(3, 'РОГА и КОПЫТА, Москва', 'MO', 100000);
insert into payments values(4, 'ВОДКА и БАЛАЛАЙКИ, Питер', 'NW', 150000);
insert into payments values(5, 'ВОДКА и БАЛАЛАЙКИ, Питер', 'NW', 3000);


    And we will distribute the rights to users:
    Hidden text
    -- big boss
BEGIN
 SA_USER_ADMIN.SET_LEVELS (
  policy_name   => 'payments_pol',
  user_name     => 'big_boss', 
  max_level     => 'B');
 SA_USER_ADMIN.SET_GROUPS (
  policy_name   => 'payments_pol',
  user_name     => 'big_boss', 
  read_groups   => 'RG');
-- medium boss
 SA_USER_ADMIN.SET_LEVELS (
  policy_name   => 'payments_pol',
  user_name     => 'med_boss', 
  max_level     => 'M');
 SA_USER_ADMIN.SET_GROUPS (
  policy_name   => 'payments_pol',
  user_name     => 'med_boss', 
  read_groups   => 'RG');
-- big boss of MO region
 SA_USER_ADMIN.SET_LEVELS (
  policy_name   => 'payments_pol',
  user_name     => 'mo_big_boss', 
  max_level     => 'B');
 SA_USER_ADMIN.SET_GROUPS (
  policy_name   => 'payments_pol',
  user_name     => 'mo_big_boss', 
  read_groups   => 'MO');
END;
/


    Well, now let's check what happened:

    Make sure that big_boss sees all the records:
    $ sqlplus big_boss/qw
SQL> select label_to_char (ols_payments) label, id, cname, amount from test.payments;
LABEL		   ID CNAME				 AMOUNT
---------- ---------- ------------------------------ ----------
S::MO               3 РОГА и КОПЫТА, Москва                2000
S::NW               5 ВОДКА и БАЛАЛАЙКИ, Питер             3000
M::MO               2 РОГА и КОПЫТА, Москва                7000
B::MO               1 РОГА и КОПЫТА, Москва              100000
B::NW               4 ВОДКА и БАЛАЛАЙКИ, Питер           150000

    We make sure that med_boss sees records with an amount of less than 10,000 in all regions:
    sqlplus med_boss/qw
SQL> select label_to_char (ols_payments) label, id, cname, amount from test.payments;
LABEL		   ID CNAME				 AMOUNT
---------- ---------- ------------------------------ ----------
S::MO               3 РОГА и КОПЫТА, Москва                2000
S::NW               5 ВОДКА и БАЛАЛАЙКИ, Питер             3000
M::MO               2 РОГА и КОПЫТА, Москва                7000

    We make sure that mo_big_boss sees records with any amounts but only in the Moscow region:
    $ sqlplus mo_big_boss/qw
SQL> select label_to_char (ols_payments) label, id, cname, amount from test.payments;
LABEL		   ID CNAME				 AMOUNT
---------- ---------- ------------------------------ ----------
S::MO               3 РОГА и КОПЫТА, Москва                2000
M::MO               2 РОГА и КОПЫТА, Москва                7000
B::MO               1 РОГА и КОПЫТА, Москва              100000

    PS. Oracle Label Security supports access control for INSERT / DELETE / UPDATE, but the size of the article does not allow you to talk about everything. Documentation is available here .
    Tags:

    Also popular now: