Oracle Label Security. First meeting
Oracle Label Security (OLS) is another way to delimit access to data in the Oracle Database Server. A distinctive feature of OLS is the multi-level access policies and storage of access control attributes along with data.
Inside the policy, the levels of access to information are defined, for example: “jointly secret”, “secret”, “DSP”. A user with a higher level of access has access to data marked by its level and levels below.
Oracle Label Security is a paid option in Oracle Database Enterprise Edition.
')
Below will be discussed the basic concepts and an example of using OLS.
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 using both PL / SQL packages and through Oracle Enterprise Manager .
The article will discuss the implementation of OLS for the version of Oracle Databse 12c, with differences from previous versions can be found here .
Labels . Labels are representations of data access attributes.
Labels consist of the following components:
Policy . Link together labels, tables and authorization.
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 into the PAYMENTS table.
It is required to provide access control for reading, for the sums of payments and on regions. Data on especially large payments should be stored in a separate partition, on an encrypted disk. When a payment is received from ERP, it must be assigned a label, taking into account the amount and region. Entry to the table is limited to standard oracle mechanisms and is possible only from ERP.
The amounts of payments are divided into 3 parts:
Regions:
Users:
How to enable the option Oracle Label Security look here .
Create a table in the TEST scheme to which payments will be received:
The ols_payments column is pre-configured so that partitioning can be done. If this is not required, then it can be created automatically when policy is applied.
All further configuration work will go from under the user LBACSYS .
For each policy, a special role of the type policy_DBA is created. The role holder can change this policy.
We create policy payments_pol :
We create access levels by amounts for our policy:
For each level, you must specify its numerical value, abbreviated and full name. The abbreviated name will be used in all links.
Create groups by region:
For each group, you must specify its numerical value, abbreviated and full name. For all groups except the root, the parent is indicated.
The label text format is “LEVEL: COMPARTMENT1, .., COMPARTMENTN: GROUP1, ..., GROUPN”.
The label for a record containing an amount greater than 10,000 for the Moscow region will look like “B :: MO”.
Label tags are created to display text labels in a numeric value. Label tags can be generated automatically or manually set. Since we use partitioning by 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:
It remains to write a function that performs the generation of tags on the data:
And you can apply our policy to the table:
Fill the table with test data simulating the receipt of payments from ERP:
And distribute the rights to users:
Well, now check what happened:
Make sure big_boss sees all posts:
We are convinced that med_boss sees entries with an amount less than 10,000 across all regions:
We are convinced that mo_big_boss sees records with any amounts but only in the Moscow region:
Ps. Oracle Label Security supports access control for INSERT / DELETE / UPDATE, but the size of the article does not allow to tell about everything. Documentation is available here .
Inside the policy, the levels of access to information are defined, for example: “jointly secret”, “secret”, “DSP”. A user with a higher level of access has access to data marked by its level and levels below.
Oracle Label Security is a paid option in Oracle Database Enterprise Edition.
')
Below will be discussed the basic concepts and an example of using OLS.
Prologue
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 using both PL / SQL packages and through Oracle Enterprise Manager .
The 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
Labels . Labels are representations of data access attributes.
Labels consist of the following components:
- Levels . Represented as a number from 0 to 9999. The greater the number, the higher the level of access.
- 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 the example are not used.
- Groups . Hierarchical structure, if access is given to the top, 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 "Moscow" and "London" regions. If there is only access to the “Moscow” region, then access to the other regions is denied.
Policy . Link together labels, tables and authorization.
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 into the PAYMENTS table.
It is required to provide access control for reading, for the sums of payments and on regions. Data on especially large payments should be stored in a separate partition, on an encrypted disk. When a payment is received from ERP, it must be assigned a label, taking into account the amount and region. Entry to the table is limited to standard oracle mechanisms and is possible only from ERP.
The amounts of payments are divided into 3 parts:
- Big (big) - from 10,000
- Medium (medium) - from 5000 to 9999
- Small (up to 5000)
Regions:
- MO (Moscow and oblat)
- NW (Northwest)
Users:
- big_boss - has the maximum access level, sees all the data
- med_boss - sees payments from 5000 to 9999 in all regions
- mo_big_boss - sees all payments, but only in the MO region
Implementation
How to enable the option Oracle Label Security look here .
Create a table in the TEST scheme to which payments will be received:
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 is pre-configured so that partitioning can be done. If this is not required, then it can be created automatically when policy is applied.
All further configuration work will go from under the user LBACSYS .
For each policy, a special role of the type policy_DBA is created. The role holder can change this policy.
We create policy payments_pol :
Hidden text
EXEC SA_SYSDBA.CREATE_POLICY ( policy_name => 'payments_pol', /* policy */ column_name => 'ols_payments', /* */ default_options => 'read_control'); /* */ We create access levels by amounts 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, you must specify its numerical value, abbreviated and full name. The abbreviated name will be used in all links.
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, you must specify its numerical value, abbreviated and full name. For all groups except the root, the parent is indicated.
The label text format is “LEVEL: COMPARTMENT1, .., COMPARTMENTN: GROUP1, ..., GROUPN”.
The label for a record containing an amount greater than 10,000 for the Moscow region will look like “B :: MO”.
Label tags are created to display text labels in a numeric value. Label tags can be generated automatically or manually set. Since we use partitioning by 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 performs the generation of tags on 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 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 check what happened:
Make sure big_boss sees all posts:
$ 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 are convinced that med_boss sees entries with an amount less than 10,000 across 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 are convinced 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 to tell about everything. Documentation is available here .
Source: https://habr.com/ru/post/185946/
All Articles