This example shows how you can specify a row-level security constraint column name in the select list or WHERE clause of a SELECT statement.
First, define the row-level security constraint. The create text for the group_membership constraint object looks like this.
CREATE CONSTRAINT group_membership SMALLINT, NOT NULL, VALUES (exec:100, manager:90, clerk:50, peon:25), INSERT SYSLIB.ins_grp_member, SELECT SYSLIB.rd_grp_member;
In this table definition, the database implicitly adds a row-level security constraint column named group_membership to the emp_record table when it creates that table. The group_membership column contains the data for the row-level security constraint.
CREATE TABLE emp_record ( emp_name VARCHAR(30), emp_number INTEGER, salary INTEGER, group_membership CONSTRAINT) UNIQUE PRIMARY INDEX(emp_name);
After you create emp_record, you can use a SELECT statement that retrieves the specified data from the group_membership column by specifying the name of that column in the select list for the query.
Following is an example of a SELECT statement on table emp_record that includes group_membership in the select list.
SELECT emp_name, group_membership FROM emp_record WHERE group_membership=90;
If you have the required security credentials, this query returns the emp_name and group_membership value name and value code for all managers. If you do not have the required credentials, no rows are returned. You cannot specify a value name as the search condition. You must specify a value code. In this example, the value code 90 represents the value name manager.
Suppose you then create this row-level security constraints and inventory table.
CREATE CONSTRAINT classification_level SMALLINT, NOT NULL, VALUES (top_secret:4, secret:3, confidential:2, unclassified:1), INSERT SYSLIB.InsertLevel, UPDATE SYSLIB.UpdateLevel, DELETE SYSLIB.DeleteLevel, SELECT SYSLIB.ReadLevel; CREATE CONSTRAINT classification_category BYTE(8) VALUES (nato:1, united_states:2, canada:3, united_kingdom:4, france:5, norway:6, russia:7), INSERT SYSLIB.insert_category, UPDATE SYSLIB.update_category, DELETE SYSLIB.delete_category, SELECT SYSLIB.read_category; CREATE TABLE inventory ( column_1 INTEGER, column_2 INTEGER, column_3 VARCHAR(30), classification_level CONSTRAINT, classification_category CONSTRAINT) PRIMARY INDEX(column_1);
User joe then logs onto a database session. The create text for joe looks like this.
CREATE USER joe AS PERMANENT=1e6, PASSWORD=Joe1234, CONSTRAINT = classfication_level (top_secret), CONSTRAINT = classification_category (united_states);
Because user joe is defined with the classification_level and classification_category row-level security constraints, he can execute this SELECT statement on inventory.
SELECT * FROM inventory WHERE column_1 = 1212;
The result set looks similar to this, returning not only the data from the three columns explicitly defined for inventory, but also the data from the two row-level security columns.
column_1 column_2 column_3 classification_level classification_category -------- -------- -------- -------------------- ----------------------- 1212 90505 Widgets 3 '4000000000000000'xb