IBM PureData System for Analytics, Version 7.1

SQL commands for security labels

You can use the following SQL commands with all security label components:

CREATE
ALTER
SHOW
DROP

The following is an example of how to create and display levels:

DEV.SCHEMA(ADMIN)=> CREATE SECURITY LEVEL conf VALUE 500;
CREATE SECURITY LEVEL
DEV.SCHEMA(ADMIN)=> CREATE SECURITY LEVEL greater VALUE 600;
CREATE SECURITY LEVEL
DEV.SCHEMA(ADMIN)=> CREATE SECURITY LEVEL secret VALUE 800;
CREATE SECURITY LEVEL
DEV.SCHEMA(ADMIN)=> SHOW SECURITY LEVEL ALL;

NAME      |   LEVEL
----------+--------
PUBLIC    |   0
CONF      |   500
GREATER   |   600
SECRET    |   800
OMNI      |   32767
(5 rows)

Since SECRET has a higher value than CONF and GREATER, a user with SECRET can access CONF and GREATER levels, but a user with CONF or GREATER cannot access SECRET levels. A GREATER level can access a CONF level, and any other levels with lesser values.

Restriction: You cannot change level values or drop any security levels, cohorts, or categories if you have any row-secure tables defined in the system.

To alter a security level, you rename it and give it the new value, as in the following example by using the name and value of CONF from the previous example:

DEV.SCHEMA(ADMIN)=> ALTER SECURITY LEVEL conf RENAME TO TOP_SECRET VALUE 
1000; 
ALTER SECURITY LEVEL

So now a user with the altered TOP_SECRET level can access SECRET levels, but a user with SECRET levels cannot access TOP_SECRET levels.

The following is an example of how to create and display categories. The system automatically generates IDs.

DEV.SCHEMA(ADMIN)=> CREATE CATEGORY super;
CREATE CATEGORY
DEV.SCHEMA(ADMIN)=> CREATE CATEGORY insider;
CREATE CATEGORY
DEV.SCHEMA(ADMIN)=> CREATE CATEGORY audit;
CREATE CATEGORY
DEV.SCHEMA(ADMIN)=> SHOW CATEGORY ALL;
NAME      |   ID
----------+--------
AUDIT     |   3
INSIDER   |   2
SUPER     |   1
OMNI      |   0
(4 rows)

To alter a category, rename it and give it the new value, as in the following example:

DEV.SCHEMA(ADMIN)=> ALTER CATEGORY SUPER RENAME TO TOP_SECRET;
ALTER CATEGORY

Cohorts are a strict set of hierarchies, and you can put cohorts into other cohorts for a finer control of access, as in the following example:

DEV.SCHEMA(ADMIN)=> CREATE COHORT TOP;
CREATE COHORT
DEV.SCHEMA(ADMIN)=> CREATE COHORT SALES IN COHORT TOP; 
CREATE COHORT

The following is an example of how to create and display different cohorts. The system automatically generates the IDs:

DEV.SCHEMA(ADMIN)=> CREATE COHORT "NA" in COHORT sales;
CREATE COHORT
DEV.SCHEMA(ADMIN)=> CREATE COHORT "EUROPE" in COHORT sales;
CREATE COHORT
DEV.SCHEMA(ADMIN)=> CREATE COHORT "Asia" in COHORT sales;
CREATE COHORT
DEV.SCHEMA(ADMIN)=> CREATE COHORT DIST in COHORT top;
CREATE COHORT
DEV.SCHEMA(ADMIN)=> CREATE COHORT ENG in COHORT "Europe";
CREATE COHORT
DEV.SCHEMA(ADMIN)=> CREATE COHORT FRA in COHORT "Europe";
CREATE COHORT
DEV.SCHEMA(ADMIN)=> CREATE COHORT GER in COHORT "Europe";
CREATE COHORT
DEV.SCHEMA(ADMIN)=> CREATE COHORT NE in COHORT dist;
CREATE COHORT
DEV.SCHEMA(ADMIN)=> SHOW COHORT ALL;
 NAME   | ID |                      CLOSURE
--------+----+----------------------------------------------------
 Asia   |  5 | "Asia"
 DIST   |  6 | DIST,NE
 ENG    |  8 | ENG
 Europe |  4 | "Europe",ENG,FRA,GER
 FRA    |  9 | FRA
 GER    | 10 | GER
 NA     |  3 | "NA"
 NE     |  7 | NE
 OMNI   |  0 |
 SALES  |  2 | SALES,"NA","Europe","Asia",ENG,FRA,GER
 TOP    |  1 | TOP,SALES,"NA","Europe","Asia",DIST,NE,ENG,FRA,GER
(11 rows)

The hierarchy shows that the cohort at the top can access all the cohorts beneath it. DIST cannot access SALES cohorts (both are “siblings” of TOP), and vice versa. NA, EUROPE, and ASIA cannot access NE and vice versa.

When a user accesses a table row, the security profile of the user is compared to the security label on the object to determine whether to allow access. The access check is as follows:

The label level must be less than or equal to the profile level.
All members of the label category must be members of the profile. The user must have all data categories.
At least one member of the table row cohort must also be a member of the profile.

You do not have to use all three dimensions (level, category, cohort) of the security label. For example, if you wanted to use security level and category, but not cohorts, you can specify as in the following:

DEV.SCHEMA(ADMIN)=> CREATE USER MARY PASSWORD 'abcd' SECURITY LABEL 'secret: 
blue';
CREATE USER

The absence of a specified dimension does not have an identifier, and is treated as missing. The missing state is used to decide whether to evaluate the access check.

If the label on a row is missing, the system ignores the user state and allows access. If the user label is missing, it is treated as NONE.

The following table shows the all-of comparison used for comparing categories.

Table 1. All-of comparison (categories)
User profile	Security label	Result
Missing	Missing	Allow access
Missing	Specified	Access not allowed
Specified	Missing	Allow access
Specified	Specified	The user must have all categories that the table row has. The user can have more categories.

The following table shows how the any-of comparison is used for comparing cohorts.

Table 2. Any-of comparison (cohorts)
User profile	Security label	Result
Missing	Missing	Allow access
Missing	Specified	Access not allowed
Specified	Missing	Allow access
Specified	Specified	The user must have at least one cohort in common with the table row. Both the user and the table row can have other cohorts.

From the previous examples, create a user:

DEV.SCHEMA(ADMIN)=> CREATE USER GRETA SECURITY LABEL 'SECRET : INSIDER, AUDIT 
: DIST, Europe, Asia';
CREATE USER

From the examples given, the following table shows how a match against the created user GRETA determines which access is allowed.

Table 3. Labels and access
Level	Category (all-of)	Cohort (any-of)	Can access
CONF	INSIDER	Asia	Yes.
CONF	INSIDER	SALES	No. User is restricted because none of the cohorts of user is SALES.
AUDIT	OMNI	Asia	No. User is restricted because OMNI equals all categories, and INSIDER and AUDIT are not all_of all categories.
GREATER	AUDIT	FRA	Yes. FRA belongs to cohort Europe.
TOP_SECRET	SUPER	GER	No. Level is too low and user ID does not have SUPER as a category.

When security labels are combined, the rules of Multi-Level Security determine that the result is the most restrictive combination, defined as the following:

The maximum of the levels.
The union of the all-of tag sets (categories).
The intersection of the any-of tag sets (cohorts).

The IBM® Netezza® system does not automatically calculate combinations of labels. You can define functions and aggregates to enforce rules.

The following built-in functions can be used to calculate the combination of two or more security labels:

combine_label(label1, label2)
This is a scalar function that combines two or more labels and returns the most restrictive combination.
max_label(label)
This is an aggregate function that combines a set of labels and returns a single, most restrictive label.

In the following example, SECRET is more restrictive than PUBLIC, so the result is SECRET. Two categories are more restrictive than one, and since you have to have both categories to see the data, the result is BLUE and GREEN. Cohorts are more restrictive because of the intersection. In this case, the intersection of PSG and QA is empty, so the result is explicitly NONE. Note that an explicit NONE means the data is not visible to anyone except OMNI.

DEV.SCHEMA(ADMIN)=> SELECT combine_label('secret: blue:psg', 'public: green: 
qa');
 COMBINE_LABEL
------------------------
 SECRET:GREEN,BLUE:NONE
(1 row)

To label query results or use for CTAS, you may combine labels with max_label and combine_label, which calculates the most restrictive label. Joins have no label, just the result.