IDS Experts

From archive: April 2007 Remove archive filter - April 2007 X



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


Sanjit Chakraborty[Read More]

Introduction to Basic Text Search Datablade Part 1 Description and Syntax

cheetahblog Tags:  text search syntax bts 2,604 Views
One of the new features for Cheetah is the inclusion of severaladditional datablades. One of the more exciting of these blades is theBasic Text Search blade, BTS  for short. This datablade allowsyou to do a lot of  searches on text data found in char,varchar, lvarchar, blob and clob datatypes. The intent is to provide amore robust method by which to search text than current forms like theMATCHES or LIKE functions.  The documentationfor the BTS blade can be found in TheBuilt-In DataBlade Modules User's Guide . Today we willgo over a little of the syntax that allows you to perform some neatqueries.

At the heart of the BTS datablade is a single function. The name isbts_contains, and it has the following syntax:

bts_contains (<column to search>, <searchcriteria>,  {score # REAL})

The score section at the end is optional, and what it does is allow youto give a weighted value to each member of your result set.
So let's presume that you had a varchar(255) column called remarks in atable called photos. If you wanted to search for the words "Apple" orthe phrase "Macbook Pro" , and you only wanted the score of 70.0 or greater you would do the following:

SELECT * FROM photos WHERE bts_contains(remarks, 'Apple OR "MacbookPro"', score # REAL)
AND score >= 70.0

If  you didn't care about getting the score back from yoursearch, then you could write it as the following:


SELECT * FROM photos WHERE bts_contains(remarks, 'Apple OR "MacbookPro"')


The BTS datablade takes most of the shortcuts you are familiar withfrom using most web search engines in today's marketplace.


In part 2 we will cover how to setup the BTS blade and the administrative functionsthat are available.



Mark Jamison[Read More]

Non-blocking Checkpoints - Part 1

cheetahblog Tags:  non-blocking interval checkpoint 1,650 Views
Prior to IDS Version 9, IDS supported a blocking checkpoint algorithmthat causes the system to block all transactional updates while thebufferpool is flushed to disk. In IDS Version 9 onwards, an additionalcheckpoint algorithm, called Fuzzy Checkpoint, was introduced. FuzzyCheckpoints limit the number of updates required to be flushed to diskduring checkpoint processing.

With the new non-blocking checkpoint algorithm in Cheetah, IDS ensuresthat we virtually don't block transactional updates while the entirebufferpool is flushed to disk. Transactionalupdates are blocked only for very small duration of time required to flush thepartition partitions (tablespace tablespaces) to the bufferpool and tograb the restart point. (Restart point is wherefastrecovery starts. We log the checkpoint and use that LSN as the restartpoint for the checkpoint.)

As part of this feature, the new "onstat-g ckp" command helps DBAs to view and analyze checkpointinformation and performance advisories. The "onstat -g ckp" command isexplained in more detail in thispost.

ScottLashley's whitepaper on developerWorks explains the internalsabout non-blocking checkpoints in detail.

In Part 2, we shall learn how to tune checkpoints and modify the relevant onconfig parameters (RTO_SERVER_RESTART, AUTO_CKPTS,AUTO_LRU_TUNING, etc.)

Mirav Kapadia[Read More]

Overview of LBAC - Part2

cheetahblog Tags:  lbac 2,052 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]

Backing up to a directory with ontape

cheetahblog Tags:  ontape onconfig administration 2,257 Views
With Cheetah, IDS now allows a DBA to use ontape to point to adirectory instead of just a particular file. This can be handy when youhave multiple backups that you want to keep in the samedirectory.   
It involves a little extra administration though, so here’s aquick primer on it.

Setting it up for archives
It’s actually pretty easy to set  IDS up to do it.Here are the steps:
  1. Create your directory: for instance:/opt/Informix/archives/ as Informix (group Informix) (I have the bestluck putting the ending slash on the path.)
  2. chmod 777 the directory 
  3. set TAPEDEV in your $ONCONFIG to that directory.
  4. run ontape to create your archive
Setting it up for back upof your logical logs with the log_full.sh $INFORMIXDIR/etc/ALARMPROGRAM
  1. As above, create your directory and set permissions.
  2. save a copy of your $INFORMIXDIR/etc/log_full.sh tolog_full.org or something like that
  3. edit log_full.sh with your favorite editor (i.e. vi oremacs)
  4. Add the –y option to the command (ontape–a –y) 
  5. Recycle the engine.
IFX_ONTAPE_FILE_PREFIX
When you take an archive by default it will use your<HOSTNAME_SERVERNUM> as the standard prefix. For example:my machine name is ryleh and my SERVERNUM=11 When I do an archive andthen look at the file it is this: ryleh_11_L0
If you want to name it something different, you can set theundocumented environmental variable IFX_ONTAPE_FILE_PREFX
    Example:
    export IFX_ONTAPE_FILE_PREFIX=play
You will have to recycle the engine after you set it for it to takeeffect.
This way you can have multiple archives stored in the same directory. 

Conclusion:
All in all, backing up to a directory can be a very handy utility tohave for an administrator that has multiple instances, especially forsuch things as development and test instances.  You can savean archive under the name of say “benchmark” andthen when you need to load it up, you don’t have to look highand low for the file or the tape.  It’s right there,and convenient.

Joe Baric[Read More]

Overview of IDS LBAC -- Part 1

cheetahblog Tags:  lbac 2,029 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]