IDS Experts




For a complete list of current SQL reserved words, see the IBM Informix Guide to SQL: Syntax.


Sanjit Chakraborty[Read More]

Overview of LBAC - Part2

cheetahblog Tags:  lbac 1,931 Views

What are the basic IDS LBAC concepts?


1. Security label component

Security label component is a new database entity that can be created, dropped, altered and renamed. It is the building block for security labels. A security label is composed of one or more security label components. For ALTER SECURITY LABEL COMPONENT, we only support adding security label component elements to existing security label component.

There are three types of security label components: arrays, sets, and trees.

ARRAY: An array represents an ordered set. In an array, the order in which the elements appear is important because it denotes the degree of sensitivity of the data. The rank of the first element is higher than the rank of the second element, and so on. There can be a maximum of 64 security label component element values in the array.

For example:
CREATE SECURITY LABEL COMPONENT levels ARRAY ['TOP SECRET', 'SECRET', 'UNCLASSIFIED'];
ALTER SECURITY LABEL COMPONENT levelsARRAY ['CONFIDENTIAL' BEFORE ‘UNCLASSIFIED’];

SET: A set is a collection of elements where the order in which those elements appear is not important. There can be a maximum of 64 unique values in the set.

For example:
CREATE SECURITY LABEL COMPONENT compartments SET {'Sales', 'HR', 'R&D', ‘Tech Support’};
ALTER SECURITY LABEL COMPONENT compartments SET {‘Marketing’, ‘Finance’};

TREE: A tree represents a hierarchy and is introduced to support the "group" concept that Oracle supports. Trees can be used to represent organizational charts and to identify departments within the organization that own the data. There can be a maximum 64 security label component elements in the tree.

For example:
CREATE SECURITY LABEL COMPONENT groupsTREE ('Worldwide' ROOT, 'Europe' UNDER 'Worldwide', 'North America' UNDER ' Worldwide ', 'Asia' UNDER 'Worldwide', 'China' UNDER 'Asia', 'USA' UNDER 'North America');
ALTER SECURITY LABEL COMPONENT groups TREE (‘France’ UNDER ‘Europe’, ‘Canada’ UNDER ‘North America’);

2. Security Policy

A security policy is a new database entity that can be created, dropped and renamed. The security policy defines the set of security label components that make up a security label. The security policy also specifies the access rules that IDS uses to determine whether a user who holds a security label L1 can access a data row or a column protected with a security label L2. A table can have maximum of one security policy attached to it and a policy can have a maximum of 16 security label components.

For example:
CREATE SECURITY POLICY secpolicy1COMPONENTS level, compartments, groups WITH IDSLBACRULES;

3. Access Rule

There are access rules for READ access and WRITE access for each type of security label component made up the security policy. Read access rules are applied by IDS when a user attempts to read (SELECT) a labeled data row. IDS applies the write access rules when a user attempts to insert, update or delete a data row. IDS will ship with a predefined set of read and write access rules. These rules are collectively called IDSLBACRULES and they can be divided into two categories as follows.

READ ACCESS RULE

The read access rules, called IDSLBACREAD, apply when data is retrieved. Data is retrieved on SELECT, UPDATE and DELETE operations. They can be summarized as follows:

IDSLBACREADARRAY: Each array component of the user security label must be greater than or equal to the array component of the data row security label i.e. the user can only read data at or below his/her level.
IDSLBACREADTREE: Each tree component of the user security label must include at least one of the elements in the tree component of the data row security label (or the ancestor of one such element).
IDSLBACREADSET: Each set component of the user security label must include the set component of the data row security label.

WRITE ACCESS RULE

The write access rules, called IDSLBACWRITE, apply for INSERT, UPDATE and DELETE operations. They can be summarized as follows:

IDSLBACWRITEARRAY: Each array component of the user security label must be equal to the array component of the data row security label i.e. the user can write data only at his/her level.
IDSLBACWRITETREE: Each tree component of the user security label must include at least one of the elements inthe tree component of the data row security label (or the ancestor of one such element).
IDSLBACWRITESET: Each set component of the user security label must include the set component of the data row security label.

4. Security Labels

A security label is a new database entity that can be created, dropped and renamed. A security label is always associated with a security policy which defines the set of components that make up a security label. There are three types of security labels:

User security label: A security label that is granted to a database user. A user can have at the most two labels per policy: one for read access and one for write access.

Row security label: A security label tagged with a data row of a database table.

Column security label: A security label associated with a column of a database table.

For example:
CREATE SECURITY LABEL secpolicy1.mylabel COMPONENT level 'TOP SECRET', COMPONENT compartments 'Marketing', COMPONENT groups 'Europe', 'north America';

5. Exemption

Exemption is a means to allow some database users to bypass the label access rules. The administrator can grant a database user an exemption to bypass one or more rules in a particular security policy. The following exemptions will be supported:

  1. An exemption to bypass one or more of the IDSLBACREAD rules

  2. An exemption to bypass one or more of the IDSLBACWRITE rules

  3. An exemption to bypass all read and write access rules


Note that the IDSLBACWRITEARRAY rule can be thought of as being two different rules combined. One prevents writing to data that is higher than user’s level (write-up) and the other prevents writing to data that is lower than user’s level (write-down). When granting an exemption to this rule one can exempt the user from either of these rules or from both.

For example:
GRANT EXEMPTION ON RULE IDSLBACWRITEARRAY WRITEDOWN FOR Secpolicy1 TO usrA, usrB;

6. Protected Table

A protected table is a database table to which a label security policy has been attached. There are two types of protection granularity supported:

Row level granularity: A database table can be marked as protected with row level granularity during CREATE TABLE or ALTER TABLE by attaching a security policy to such table and by specifying the column where the row security label will be stored; this column is referred as row label column, and the row label column is always defined as type of IDSSECURITYLABEL, which is a build-in distinct type used specifically for row label column. The security policy attached to the database table determines the type of the security label used to protect a data row, and the access rules that will govern access to labeled data rows in that table. The predefined IDSLBACRULES are the supported access rules that are essentially the same as DB2LBACRULES.

Column level granularity: A database table can be marked as protected with column level granularity during CREATE TABLE or ALTER TABLE by attaching a security policy to such table and by attaching a security label to one or more columns of that table. When a column is associated with a security label, that column is referred to as a protected column. The security policy attached to the database table determines the type of the security label used to protect a column, and the predefined IDSLBACRULES access rules that will govern access to protected columns of that table.

A protected table can also be defined with both row and column level granularities. When a protected table is accessed, column level access control is enforced before row level access control.

Example to create table with both row and column level protection
CREATE TABLE T1 (seclabel IDSSECURITYLABEL, employeeId int, SSN char (9) COLUMN SECURED WITH mylabel)SECURITY POLICY Secpolicy1;

7. Protected Column

A protected column is a column to which a security label has been attached. Users may or may not have the right to access such column depending on the security labels granted to them. When an SQL statement that refers to a protected column is submitted, IDS verifies whether the user is authorized to access that column.

IDS applies the rules above to compare the security label associated with the protected column and the security labels associated with the user. If the user cannot access that column in that mode (read or write) then an error is returned. Otherwise, the statement proceeds as usual.

For example:
CREATE TALBE T2 (salary float COLUMN SECURED WITH mylabel, name varchar (20)) security policy Secpolicy1;


What is the new database security administrator DBSECADM role?

IDS LBAC introduces a new database security administrator (DBSECADM) role. This role is required to manipulate LBAC related objects. This role is a server level role and can only be granted by a database server administrator (DBSA). The DBSECADM performs all the security related administration. The responsibilities include:
  1. Create, drop, alter and rename security label components

  2. Create, drop and rename security policies

  3. Create, drop and rename security labels

  4. Attach policies to tables

  5. Grant and revoke security labels

  6. Grant and revoke LBAC rule exemptions

  7. Change user using set session authorization

Example to grant a user with DBSECADM role:

GRANT DBSECADM TO USER user1;


What are security label scalar functions?

Security label scalar functions are introduced by LBAC feature to manipulate security labels during INSERT, UPDATE and SELECT operation.

SECLABEL_BY_COMP(): A built-in function that can be used in insert and update operations toprovide the row security label of a data row by providing its individual components as opposed toproviding its name.
SECLABEL_BY_NAME(): A built-in function that can be used in insert and update operations toprovide the row security label of a data row by providing its name as opposed to its individualcomponents.
SECLABEL_TO_CHAR(): A built-in function that can be used in select operations that retrieve therow security label column. The function returns the row security label by giving the details of itsindividual components as opposed to its name. This function can be thought of as the reverse of SECLABEL_BY_COMP.

Examples of security label functions

INSERT INTO T1 VALUES (SECLABEL_BY_COMP('Secpolicy1', 'Top Secret:Marketing:Europe’), 1, '100200300');
INSERT INTO T1 VALUES (SECLABEL_BY_NAME('Secpolicy1', 'mylabel'), 2, '200300600');
SELECT SECLABEL_TO_CHAR('Secpolicy1', seclabel), employeeId, SSN FROM T1;

Usage scenario:

This simple usage scenario will serve as an example to demonstrate how to perform LBAC setup on your database to protect your sensitive data.

1. DBSECADM creates the security label components, security policy, and security label.

CREATE SECURITY LABEL COMPONENT level ARRAY ['TOP SECRET', 'SECRET', 'CONFIDENTIAL', 'UNCLASSIFIED'];
CREATE SECURITY LABEL COMPONENT compartments SET {'Sales', 'HR', 'R&D', ‘Tech Support’};
CREATE SECURITY LABEL COMPONENT groups TREE ('Worldwide' ROOT, 'Europe' UNDER 'Worldwide', 'North America' UNDER ' Worldwide ', 'Asia' UNDER 'Worldwide', 'China' UNDER 'Asia', 'USA' UNDER 'North America');
CREATE LABEL SECURITY POLICY Secpolicy1COMPONENTS level, compartments, groupsWITH IDSLBACRULES;
CREATE SECURITY LABEL Secpolicy1.HR COMPONENT level 'TOP SECRET',COMPONENT compartments 'HR',COMPONENT groups 'Worldwide';

2. DBSECADM grants security label to user Nancy who is an employee from HR department.

GRANT SECURITY LABEL Secpolicy1.HR FOR ALL ACCESS TO USER Nancy;

3. DBSECADM creates a protected table and attaches the label security policy to the table:

CREATE TABLE T1 (seclabel IDSSECURITYLABEL, employeeId int, SSN char(9) COLUMN SECURED WITH HR) SECURITY POLICY Secpolicy1;

4. User Nancy try to access the table.

INSERT INTO T1 (employeeId, SSN) VALUES (6, '123456789');



This statement will succeed and the seclabel column is inserted with the default valueof Nancy’s write label, in this case it is label HR.

SELECT employeeId, SSN from T1;

This statement will return one row Nancy just inserted. If the same SELECT statement is executed by user Bob and Bob has not been granted any label for Secpolicy1 yet, no rows will be returned.

DELETE from table T1 where employeeId = 6;

If the DELETE statement is executed by Nancy, one row (inserted by Nancy) will be deletedfrom the table, no row will be deleted if it is executed by Bob.


More practical usage scenarios of LBAC will be covered in subsequent LBAC blog entries to illustrate the power of LBAC on data access control.

Jihong Ma[Read More]

OAT Version 2.23 Available Now!

cheetahblog Tags:  openadmin oat 1,929 Views

The newest version of OpenAdmin Tool for IDS, Version 2.23, has just been released! Download the new version today to get the enhanced SQL Explorer and version 2.0 of the ER Plugin.



New feature highlights:



The SQL Explorer has been newly redesigned. New features include

  • Filters and search fields for viewing SQL tracing data.
  • Support for different SQL trace levels - global or user - so you can manage what kinds of SQL information are traced for IDS V11.50.xC1 servers.
  • Support for suspending and resuming history tracing, without releasing resources, on IDS V11.50.xC1 servers.


ER Plug-in Version 2.0: Version 2.0 of OAT’s ER plug-in includes a new Replicate Explorer and becomes the first step in supporting ER administration graphically through OAT.





  • The Replicate Explorer now lets you monitor your replicates and replicate sets.
  • Screenshot of the Replicate Explorer



  • The Node Details -> Configuration page now supports the editing and updating of ER configuration parameters






Download OAT Version 2.23 now at https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=swg-informixfpd



For additional information on OpenAdmin Tool for IDS, including feature details, screenshots and demos, go to www.openadmintool.org.

Erika Von Bargen

[Read More]

Monitoring Onbar Performance

cheetahblog Tags:  performance onbar 1 Comment 1,921 Views
Previously there was no easy way to monitor onbar archiving progress. It always a question, how long onbar process will take to complete an archive or how much time onbar will spend to transfer data between server, storage manager and vice versa.



Informix introduce two new configuration parameters to help onbar monitoring.


  • BAR_PROGRESS_FREQ
  • BAR_PERFORMANCE




BAR_PROGRESS_FREQ
The BAR_PROGRESS_FREQ configuration parameter specifies, in minutes, the frequency of the progress messages in the bar activity log for backup and restore operations.



For example, if BAR_PROGRESS_FREQ is set to 5, onbar reports the percentage of the object backed up or restored every five minutes. Following is an excerpt of bar activity log that showing progress of rootdbs dbspace backup:


  2007-05-09 16:12:58 13344  13342 /export/home/informix/bin/onbar_d -b -L 0 2007-05-09 16:12:59 13344  13342 Archive started on rootdbs (Requested Level 0). 2007-05-09 16:13:00 13344  13342 Begin level 0 backup rootdbs. 2007-05-09 16:13:00 13344  13342 Successfully connected to Storage Manager. 2007-05-09 16:18:00 13344  13342 1 percent of rootdbs has been backed up. 2007-05-09 16:23:01 13344  13342 6 percent of rootdbs has been backed up. 2007-05-09 16:28:02 13344  13342 11 percent of rootdbs has been backed up. 2007-05-09 16:33:03 13344  13342 16 percent of rootdbs has been backed up. 2007-05-09 16:38:04 13344  13342 21 percent of rootdbs has been backed up. 2007-05-09 16:43:05 13344  13342 26 percent of rootdbs has been backed up.


The default value of BAR_PROGRESS_FREQ is 0. If the value set to 0, onbar does not write any progress messages to the bar activity log.



The BAR_PROGRESS_FREQ value can’t less than five minute for monitoring onbar progress.



If ON–Bar cannot determine the size of the backup or restore object, it reports the number of transfer buffers sent to the database server instead of the percentage of the object backed up or restored.




BAR_PERFORMANCE


The BAR_PERFORMANCE configuration parameter specifies the type of performance statistics to report, and write them to the bar activity log for backup and restore operations.



For example, if BAR_PERFORMANCE is set to 3, onbar reports the time spent transferring data between the Informix server and the storage manager, in the bar activity log.



The default value of BAR_PERFORMANCE is 0. If the value set to 0, onbar does not report any performance statistics to the bar activity log.



Valid values of BAR_PERFORMANCE are 0,1,2 or 3.



  • 0 - turn performance monitoring off
  • 1 - display the time spent transferring data between the server and storage manager
  • 2 - display sub-second accuracy in the timestamps
  • 3 - display both timestamps and transfer statistics




Both BAR_PROGRESS_FREQ and BAR_PERFORMANCE configuration parameters take effect while onbar process starts.



Sanjit Chakraborty

[Read More]

Overview of IDS LBAC -- Part 1

cheetahblog Tags:  lbac 1,915 Views

This article describes LBAC concept and outlines IDS LBAC solution an implementation of MAC (Mandatory Access Control) for protecting data rows and columns based on security labels.

What is LBAC?

Label-Based Access Control (LBAC) is a means by which a database system can control access to a database object based on security labels. If the security label granted to a user dominates the security label protecting that object, access to the object is authorized. A database object in this context can refer to either a row of data or a table column. Each protected database object is assigned a security label which stores information about the classification (or sensitivity) of the data. Similarly, each database user is assigned a security label that determines which labeled data (rows or columns) he or she can access. LBAC is an implementation of MAC (Mandatory Access Control),which provides a reasonable level of security assurance of sensitive data stored in database.

What is IDS LBAC solution?

IDS LBAC solution is similar to DB2 LBAC solution; some key characteristics are as following:
  1. Offer row level and column level protection, allowing protection of data rows of a table and columns of table.


  2. Support three types (Hierarchical, Horizontal and Tree structured) security label component: ARRAY, SET and TREE respectively.


  3. Ship pre-defined IDSLBAC access rules to govern the access to table row/column protected by security label. Access control enforces the proper IDSLBAC access rules that apply for the security component type for each component that makes up the security label.


  4. Allow flexible definition of security label components that make up a security label, which allows the security label structure to be compatible with US government as well as other application domain which require different label structure not necessarily compliant to traditional MLS security label structure and access rules. Support a security label to be made up to 16 components of any type.


  5. Provides means to bypass LBAC access rules through granting user exemption.


  6. Provides functions for manipulating security label. SECLABEL_BY_COMP(), SECLABEL_BY_NAME(), SECLABEL_TO_CHAR().



In part2 of LBAC overview, basic LBAC concepts and a simple user scenario will be covered.

Jihong Ma[Read More]