Oracle Label Security (Oracle Label Security)

Author: Darl Kuhn, Steve Roughton

Oracle Label Security (Oracle Label Security) controls user access by row.

Most commercial applications must handle security issues. Applications often need to restrict access to dedicated records, establish audit trails, or execute a workflow process, all of which must comply with the company's security policies. Building secure software is a challenging and complex task. managing software security policies across the organization may be more difficult.

As a schema designer, you may add security columns to tables and create user-specific views based on these tables. As a DBA, you may create roles and permissions to protect database objects. As a developer, you may write PL/SQL packages to encapsulate Security Transaction Processing in applications. All these technologies are very effective, but these methods also have some shortcomings. For example, someone may unintentionally export private data to a personal mode, and the original application may be incompatible with the security object, alternatively, you may use SQL * Plus to bypass the security check of the entire application.

The Oracle9i database has a component that can help solve these problems: Oracle Label Security. Oracle Label Security was first introduced to the third edition of Oracle8i (8.1.7). It is a simple tool that enables you to establish and implement enterprise Security policies.

Oracle Label Security is a set of processes and constraints built into the database engine. The data engine implements row-level access control for a single table or the entire mode. To use Oracle Label Security, you need to create one or more Security policies, each of which contains a group of tags. You can use these tags to indicate which users can access the data type. After creating a policy, apply the policy to the table to be protected and grant the tags to your users. This completes the entire process. Oracle Label Security makes queries transparent and performs new policies at the real-time computing access level.

When the Oracle9i database parses each SQL statement, it also checks whether each table is protected by a security policy. Based on the user's access permissions, Oracle9i Database adds security predicates to the WHERE clause of the statement. Because these occur inside the database engine, users cannot bypass the security mechanism regardless of the source of the SQL statement.

How does it work?

Here is a very simple example to illustrate how Oracle Label Security works. We created a table named documents, and filled it with four records. At the same time, we defined two security levels: PUBLLIC (public) and INTERNAL (INTERNAL ). Each level has a numeric value: 1000 or 2000. You can specify a level for each row of the table. The following is a simple SELECT statement for the table:

　

SQL> SELECT * FROM statements;

Docid docname level DOC_LABEL
---------------------------------
1 pai_ware PUBLIC 1000
2 WEST_PAYROLL INTERNAL 2000
3 EAST_SALES INTERNAL 2000
4 COMP_PAYROLL INTERNAL 2000

　

Assume that there are two users in our database: EMP and MGR. We specify the following access levels for these users:

EMP is specified as PUBLIC read-only.

MGR is specified as PUBLIC and INTERNAL read/write.
When the two users access the table, EMP can read only 1st rows, while MGR can read/write all four rows.
　

What happens internally when these two users access the documents table? Assume that the EMP user runs the following query:

　

SELECT * FROM statements;

　

The Oracle9i database parses the query and determines that the table is protected by TAG security. Oracle Label Security adds a WHERE clause to the query to ensure that the EMP can only see rows marked with PUBLIC access:

　

SELECT * FROM documents ents
WHERE doc_label = 1000;

　

The following content is displayed after the EMP user runs the query:

　

Docid docname level DOC_LABEL
------------------------------
1 pai_ware PUBLIC 1000

You may want to know: "Why not create a restricted access view based on a column value? "In fact, if your application requires only a few levels without special security requirements, you can add a security column to your table and then use the view.

But assuming that your system requirements have changed, you now need to use a custom read/write license for changing datasets to manage users at multiple levels across multiple organizations. In addition, these institutions are located in different countries and each has its own legal and security restrictions. If you only use a view, it is difficult to meet these requirements.

Fortunately, Oracle Label Security is designed to adapt to scaling, so implementing this type of application Security may be easier than you expected.

Example of a exercise

Implementing Oracle Label Security includes the following 10 steps:

Install Oracle Label Security (once per database)
Create security policy
Definition level
Range (optional)
Define a group (optional)
Create tags
Apply a label policy to a table
User tag
Access at the normal authorization level
Specify a proper label for the rows in the table
When using Oracle Label Security, you can use the Policy Manager graphical user interface (GUI) or Oracle Label Security PL/SQL package of Oracle Enterprise Manager. In our example implementation, we will use PL/SQL packages. The same concept can be applied to either of the above two technologies.
　

Step 1. Install Oracle Label Security
For each database, you only need to install Oracle Label Security once. The installation process includes four steps:

Start the Universal Installer.
Select and install the Oracle Label Security option.
Run $ ORACLE_HOME/rdbms/admin/catols. SQL as follows:
　

　

SQL> CONN sys/password AS SYSDBA;
SQL> @? /Rdbms/admin/catols

　

Note: This catols. SQL script SHUTDOWN the database IMMEDIATE (close immediately) in its last step ).

Restart the instance and run
　

　

SQL> SELECT username FROM dba_users;

　

You will see a new LBACSYS user that includes all Oracle Label Security objects. The default password is LBACSYS (so you must change the password ). This user will manage your security policies.

Step 2: create a security policy
The next task is to create a security policy. A policy is a bucket that includes all security rules and access requirements ). Row-level data labels and pattern access to these rows are always associated with a policy.

In this example, You need to define row-level access to company documents. In this step, create a policy named DOC_POLICY. To create a policy, first establish a connection as LBACSYS, and then use the sa_sysdb.create_policy process:

　

SQL> CONN lbacsys/lbacsys
SQL> EXEC sa_sysdba.create_policy
('Doc _ policy', 'doc _ label ');

　

The first parameter DOC_POLICY is the name of this policy, and the second parameter DOC_LABEL is the name of a column. Oracle Label Security adds this column to the table you will replace under the Label control.

To verify that your policy has been created, you can query DBA_SA_POLICIES as follows:

　

SQL> SELECT policy_name, status
From DBA_SA_POLICIES;

Yy_name STATUS
------------------
DOC_POLICY ENABLED

　

To disable, re-enable, or delete a policy, use the following procedure:

　

SQL> EXEC sa_sysdba.disable_policy
('Doc _ policy ');
SQL> EXEC sa_sysdba.enable_policy
('Doc _ policy ');
SQL> EXEC sa_sysdba.drop_policy
('Doc _ policy ');

　

Step 3: define the level
Each security policy must contain different levels of the specified access table. In this example, two sensitivity levels are created: PUBLIC and INTERNAL.

　

SQL> EXEC sa_components.create_level
('Doc _ policy', 1000,
'Public ', 'public level ');
SQL> EXEC sa_components.create_level
('Doc _ policy', 2000,
'Internal', 'internal ');

　

Each level has a policy name, a digit ID, an abbreviation name, and a full name. The value ID indicates the sensitivity level. The higher the number, the higher the sensitivity. In this example, INTERNAL is more sensitive than PUBLIC. To view the level you created, execute the following process:

　

SQL> SELECT * FROM dba_sa_levels
Order by level_num;

　

Step 4: Define a range (optional)
Range allows you to limit the access to a row of data to a specific level. In this example, you have the same sensitivity level for reading documents, but only a subset of this level can be seen in a certain range. Next, you need to create the FINANCE and HUMAN_RESOURCE intervals:

　

SQL> EXEC sa_components.create_compartment
('Doc _ policy', 200,
'Fin ', 'Finance ');
SQL> EXEC sa_components.create_compartment
& Nb

Sp; ('doc _ policy', 100,
'Hr', 'human _ resource ');

　

The interval contains a Policy Name, a digit ID, an abbreviation name, and a full name. The value ID of an interval does not specify the sensitivity level. It is only used to sort the intervals when access information is displayed. To learn more about the interval, You can query the DBA_SA_COMPARTMENTS view.

Step 5: Define a group (optional)
Similar to the use interval, grouping is another optional method that limits access within one level. When there are multiple levels of users (such as in the organization setting diagram of a company), groups are very useful.

When creating a group, you must define a hierarchy level ). In this example, ALL_REGIONS is the parent and WEST_REGION and EAST_REGION are the child of ALL_REGIONS.

　

SQL> EXEC sa_components.create_group
('Doc _ policy', 10,
'All', 'all _ regions ');
SQL> EXEC sa_components.create_group
('Doc _ policy', 20, 'west ',
'West _ REGION ', 'all ');
SQL> EXEC sa_components.create_group
('Doc _ policy', 30, 'east ',
'East _ region', 'all ');

　

Similar to the interval, a group also has a digit ID, an abbreviation name, and a full name. In addition, numbers do not indicate any sensitivity. They are only used to sort group information. To view the group information, you can query the DBA_SA_GROUPS view.

Step 6: Create a tag
A tag is a combination of levels, ranges, and groups. Each tag must contain a level, range, or group. This tag allows you to quickly combine various types of access that different data users require.

A tag is a combination of the abbreviation names of a level, interval, and group, and follows the following syntax:

　

Level: interval,... interval _ n: group,... group _ n
　

Levels, ranges, and groups must be separated by colons. If more than one interval or group is specified, they must be separated by commas.

For example, some financial department users may only access internal documents. Its labels are similar:

　

INTERNAL: FIN (INTERNAL: finance)
Create four tags to specify related requirements, as shown below:
SQL> EXEC sa_label_admin.create_label
('Doc _ policy', '123 ',
'Public', TRUE );
SQL> EXEC sa_label_admin.create_label
('Doc _ policy', '123 ',
'Internal: HR: West', TRUE );
SQL> EXEC sa_label_admin.create_label
('Doc _ policy', '123 ',
'Internal: FIN: East', TRUE );
SQL> EXEC sa_label_admin.create_label
('Doc _ policy', '123 ',
'Internal: HR, FIN: all', TRUE );

When creating a tag, you must specify a number for it. This number is unique among all database policies. To view the tag information, you can query the DBA_SA_LABELS view.

Step 7: Apply the label Policy to the table
To place a table under the label security control, you need to assign the label Policy to the table. In the following process, the DOC_POLICY is applied to the DOCUMENTS tables owned by the user APP. Oracle Label Security controls read/write access to the table.

　

SQL> EXEC sa_policy_admin.apply_table_policy-
(Policy_name => 'doc _ policy '-
, Schema_name => 'app '-
, Table_name => 'documents '-
, Table_options => 'label _ DEFAULT,
READ_CONTROL, WRITE_CONTROL ');

When running this process, the Oracle9i database adds a column named DOC_LABEL to the documents table. The name of this column is defined when the security policy is created in step 2. If you describe the documents table, you will see the new DOC_LABEL column as follows:

　

SQL> DESC app.doc uments

Name Type
---------------------
DOCID NUMBER
DOCNAME VARCHAR2 (30)
DOC_LABEL NUMBER (10)

　

When you apply this policy, you can also specify HIDE in the TABLE_OPTIONS parameter to cancel the column from the user:

　

Table_options & n

Bsp; => 'label _ DEFAULT,
READ_CONTROL, WRITE_CONTROL, HIDE'
　

The TABLE_OPTIONS parameter allows you to define the types of controls to be applied to the table. LABEL_DEFAULT indicates that if no label is provided for an INSERT statement, the default session row label is used. The READ_CONTROL parameter specifies that the SELECT, UPDATE, and DELETE access is valid throughout the tag. The WRITE_CONTROL parameter determines which INSERT statements, and the UPDATE activity is authorized by a tag.

To determine which tables and modes have been applied to which policies, you can query the DBA_SA_TABLE_POLICIES view.

Step 8: specify user tags
Now you need to define which users have access permissions in a policy. This is where you specify the maximum read/write permissions for a user. In this example, you specify the following tags for the three users:

MGR is granted the highest level of read/write permissions.
HR_EMP is granted a read/write permission on the hr west document.
EMP is granted the PUBLIC read/write access permission.
Code List 1 provides the syntax for specifying tags for each user.
　

These processes map a user to the access level and the row with the specified tag. To view users and access levels, you can query the DBA_SA_USER_LABELS view.

Step 9: Specify access at the normal authorization level
Make sure that the CRUD (CREATE, READ, UPDATE, and DELETE) access has been prepared in order. Label Security works with formal table authorization. Before CRUD authorization is completed, you cannot perform SELECT, INSERT, UPDATE, or DELETE operations. When an SQL query accesses a table, Oracle Label Security first checks whether CRUD access is appropriate. If a Security policy is applied to a table, it will ensure that the access is executed. The following lists the appropriate CRUD authorization processes for users:

　

SQL> CONN app/app
SQL> GRANT SELECT ON orders ents TO emp;
SQL> GRANT SELECT, UPDATE ON statements
TO hr_emp;
SQL> GRANT SELECT, UPDATE, INSERT
ON documents TO mgr;

　

Step 10: specify the appropriate tag
Now, make sure that a proper label is specified for each row. In this example, data is loaded from the beginning. The label can be loaded either in numbers or using the CHAR_TO_LABEL function. This example describes both methods. Create a connection as MGR and insert the data to the APP. DOCUMENTS tables Table:

　

SQL> CONN mgr/mr_bigg
SQL> INSERT INTO app.doc uments VALUES
(1, 'share _ WARE ', CHAR_TO_LABEL
('Doc _ policy', 'public '));
SQL> INSERT INTO app.doc uments VALUES
(2, 'West _ PAYROLL ', 20200 );
SQL> INSERT INTO app.doc uments VALUES
(3, 'east _ sales', 20400 );
SQL> INSERT INTO app.doc uments VALUES
(4, 'comp _ PAYROLL ', 30900 );

　

If the table already has data, you need to use the appropriate label value to update the label column (DOC_LABEL ). Because the table is already under the control of Oracle Label Security, you must update the Label column in a mode with permissions. Of course, you can also temporarily disable this policy. Update the label column first, and then re-enable this policy. If you use SQL * Loader to insert data to a protected table, make sure that the loaded user (mode) has the appropriate label write permission.

After Label Security Control is enabled for a table, even the table owner cannot read or write data without proper label permissions. A variant of this rule is that the table owner can truncate data without the Oracle Label Security DELETE permission.

Operation data

Now, when you connect to a different user identity, note that you can only operate data according to your security policy and the way CRUD accesses:

　

SQL> CONN mgr/mr_bigg
SQL> SELECT docname, doc_label
FROM app.doc uments;
DOCNAME DOC_LABEL
----------------------
Pai_ware 10000
WEST_PAYROLL 20200
EAST_SALES 20400
COMP_PAYROLL 30900

　

If you connect as HR_EMP, the same query will return the following content:

　

DOCNAME DOC_LABEL
----------------------
Pai_ware 10000
WEST_PAYROLL 20200

　

If you connect as EMP, only the following content is returned for the same query:

　

DOCNAME

DOC_LABEL
----------------------
Pai_ware 10000
　

When any SQL statement accesses the APP. Statements table, the Oracle9i database first verifies CRUD access and then applies Oracle Label Security restrictions. In this way, the user can only perform authorized operations.

DBA considerations

If you are a DBA, there are other considerations. When exporting data protected by Label Security, you can only export the data in a mode with the appropriate read permission granted. For example, if you want to export the APP. DOCUMENTS table as a SYSTEM, you will receive the following message:

　

EXP-00079: Data in table "DOCUMENTS" is protected. (Data in table "DOCUMENTS" is protected)

Conventional path may only be exporting partial table. (The Conventional path can only export some tables)

.. Exporting table DOCUMENTS 0 rows exported (0 rows in exported table DOCUMENTS are exported)
　

You cannot apply a security policy to this SYSTEM mode. You will need to use a non-SYSTEM mode with the read permission on all labels protected rows in the table. For example, if you have an EXPUSER mode for exporting your database, you need to grant it special READ permission on all rows protected by the policy:

　

SQL> EXEC sa_user_admin.set_user_privs
('Doc _ policy', 'expuser', 'read ');

To grant a mode FULL read and write permissions to the data protected by the policy, you can use the FULL Keyword:

　

SQL> EXEC sa_user_admin.set_user_privs
('Doc _ policy', 'expuser', 'full ');

Note: All data can be seen in any mode granted SYSDBA permissions (such as SYS), regardless of whether the data is protected by Label Security.

No matter what special permissions you have (such as FULL or FULL), you cannot use the export utility to back up the LBACSYS mode. If you try to export LBACSYS, you will receive an error message: "LBACSYS is not a valid username. (LBACSYS is not a valid user name )". Therefore, you need to use a physical backup (hot backup, cold backup, or RMAN) of the database to back up the LBACSYS object.

Before importing data protected by tags to another database, you must install Oracle Label Security. You also need to create policies and labels in advance, and ensure that the import mode (User) has full write permissions. For more information, see Chapter 12th of the Oracle Label Security Administrator guide.

If there is a large amount of data protected by Label Security, you need an optimization policy. Depending on the cardinality, you may want to add a B-tree index or a one-bit ing index to the label column. For example, if the tag base is high, a B-tree index should be used.

Oracle recommends analyzing objects in the LBACSYS mode, application tables, and indexes to improve the execution plan generated by the cost-based optimizer. We recommend that you analyze the LBACSYS object after making any changes to the security policy.

Conclusion

Oracle Label Security in the Oracle9i database provides a Security method for fine-grained access control of data. This feature is encapsulated in the database engine, so it cannot be bypassed. It provides a security method required to implement and maintain complex "row" level security.