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)

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

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)

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 following table shows the all-of comparison used for comparing categories.

The following table shows how the any-of comparison is used for comparing 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.

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 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)