Row-level security

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Teradata Employee

Row-level security



Row-Level Security

This feature was introduced in Teradata Database 14.00.



Description

Teradata row-level security (RLS) allows you to restrict data access on a row-by-row basis in accordance with your site security policies. Row-level security policies can be used in addition to the standard GRANT privileges to provide a finer level of access control to table data.

RLS supports both hierarchical and non-hierarchical security schemes:

  • A hierarchical security scheme defines hierarchical security levels. Users with higher security levels automatically have access to rows protected by lower security levels.

    An example would be levels defined from higher to lower security as TOP SECRET, SECRET, CLASSIFIED, and UNCLASSIFIED. A user having a security level of SECRET would be able to access rows that are protected as SECRET, CLASSIFIED, and UNCLASSIFIED, but would not be able to access rows that are TOP SECRET.

  • A non-hierarchical security scheme defines distinct and unrelated security categories, also called compartments. Access granted to one type of protected row does not automatically allow access to rows protected with any other security category.

    An example would be categories for different countries: USA , CAN, UK, GER. A user having a security category of USA will only be able to access rows labeled as USA, and would not be allowed access to rows labeled CAN, UK, or GER that are not explicitly labeled as USA. (Your own custom policies determine whether, for example, a USA user could access a row labeled with both USA and CAN.)


More...

A CONSTRAINT object defines the RLS levels or categories for a specific security policy, and relates the policy to user-defined functions (UDFs) that enforce the actual row-level access controls. You write the UDFs yourself to enforce policies specific to your organization. You will generally write four UDFs, one for each of the four SQL statement types (SELECT, UPDATE, INSERT, and DELETE) that can be restricted at the row level by RLS. When an SQL request is submitted against a row in a table that includes a CONSTRAINT column, these UDFs are run automatically by Teradata Database to determine whether the user is allowed to access to the row.

You can assign security levels and categories to a user (or to a PROFILE) from up to six hierarchical and two non-hierarchical CONSTRAINT objects, supporting up to eight different security policies or criteria.

You can assign security levels and categories to the rows in a table by adding a CONSTRAINT column to the table and naming the column for an existing CONSTRAINT object. Populate the CONSTRAINT field of a row with values that identify the RLS access restriction level or category. Tables can include up to five CONSTRAINT columns to limit row access by up to five different sets of security criteria. Users must meet the appropriate criteria for all constraint columns in order to gain access to rows in the table. Views and some index types can also include security constraint columns.


RLS versus View-Based Security Techniques

Another form of row-level security can be implemented using views. In this technique, row-level access rules are part of view definitions. However, this type of row-level security is limited to filtering rows for SELECT operations. Other access to the base tables themselves can bypass the view-imposed row-level security.

Because RLS is applied at the level of base tables rather than views, the security policy is enforced on any method of accessing the base-table data and cannot be bypassed. RLS security policies can be enforced for SELECT, INSERT, UPDATE, and DELETE operations.



Benefits

  • RLS provides row-level access control to sensitive data. In addition to the normal object-level privileges required for data access, the system checks the user clearance for each operation on each row of data protected with RLS.
  • RLS provides mandatory controls on granting access privileges. Normally, owners of database objects automatically have the privileges to grant access to their owned objects to any other user. For objects protected by RLS, only users who are explicitly granted certain privileges can assign security constraints and other RLS access privileges to other users.


Considerations

  • In order to use RLS, you must:
    • Designate a system administrator to manage RLS, and grant them the required privileges.
    • Create the RLS infrastructure to support your system of security classifications. This involves writing security policy UDFs as scalar C language functions, and creating CONSTRAINT objects that reference those UDFs.
    • Create or alter tables to include constraint columns that enforce RLS.
    • Assign database users security levels or categories for RLS.
  • Because RLS involves row-level filtering, it adds processing overhead that results in some performance degradation, as compared to queries that do not involve such filtering. The amount of overhead is a function of several factors including the complexity of the C UDFs written to enforce RLS, which implement your site-specific security policies. The performance impact of using RLS is likely to be comparable to implementing a security policy of similar complexity using views and security table joins. To minimize the performance impact of the RLS policy UDFs, be sure to alter the functions to run in unprotected mode after they have been completely tested while running in the slower protected mode.





Scenario: Implementing Row-Level Security for a Table


This scenario implements a row-level security (RLS) scheme for an employee table in order to restrict access to the row data. Members of a Human Resources (HR) department are assigned security classifications based on their country. These classifications allow individual HR members to access only information for employees from their country.

The security scheme implemented in this scenario is non-hierarchical: HR employees who have access to employee data from one country do not automatically have access to data for employees of any other country.

The scenario demonstrates how to:

  • Grant an administrator (sysdba) privileges required to manage RLS.
  • Create an RLS infrastructure to support a system of security classifications. This involves the following steps:
    • Write UDFs as C code functions that implement security policies for SELECT, INSERT, UPDATE, and DELETE SQL operations.
    • Create a CONSTRAINT object that defines the security categories for this scenario, and points to the security policy UDFs.
    • Assign users different security categories, limiting the rows they can access in RLS-protected tables.
    • Create an employee table protected by RLS by including a constraint column in the table definition.


For More Information about Row-Level Security

For more information about RLS and the SQL used in these examples, see:
Document Description
SQL Data Definition Language - Detailed Topics, B035-1184 Provides information on creating CONSTRAINT objects and a discussion of row-level security.
SQL Data Definition Language - Syntax and Examples, B035-1144 Shows SQL syntax for creating and altering CONSTRAINT objects.
SQL External Routine Programming, B035-1147 Describes creating user-defined functions in external programming languages.
Security Administration, B035-1100 Discusses row-level security implementation and contrasts with other security methods.
Orange Book: Teradata Row Level Security

Document #: 541-0005614

Detailed discussion and application examples of implementing and using RLS.
Orange Book: Teradata Database User Defined Function User's Guide

Document #: 541-0004361

Detailed discussion and application examples of creating and using UDFs.







Example: Grant Privileges to Manage RLS


The following SQL statements grant privileges that allow user sysdba to:
  • Create FUNCTION (UDF) and CONSTRAINT objects, which are used to implement RLS
  • Assign security categories to users and table rows, which determine the rows those users can access
  • Override aspects of RLS for purposes of system maintenance

.logon teradata_system_name/DBC,password

grant FUNCTION on syslib to sysdba with grant option;
grant CONSTRAINT DEFINITION to sysdba with grant option;

grant CONSTRAINT ASSIGNMENT to sysdba with grant option;

grant OVERRIDE SELECT CONSTRAINT on hr_db to sysdba;
grant OVERRIDE INSERT CONSTRAINT on hr_db to sysdba;
grant OVERRIDE UPDATE CONSTRAINT on hr_db to sysdba;
grant OVERRIDE DELETE CONSTRAINT on hr_db to sysdba;





Example: Create a SELECT Security Policy UDF


The following C code defines a scalar function invoked by the RLS security policy UDF for SELECT statements. It determines which rows of a table a SELECT request is allowed to access.

The SELECT policy function has three arguments, which are passed to the function by Teradata Database when a request involves a table protected by RLS:
  • UserCompartments holds the security category or categories assigned to the user.
  • RowCompartments holds the constraint column value from the row that is being evaluated to determine whether access will be allowed.
  • AccessAllowed is the result of the function that is returned. The return value determines whether access to the row is allowed for the SELECT request.

The function compares the first two argument values to determine whether access to the row is allowed and returns T or F. For our non-hierarchical RLS scheme in this scenario, the function enforces the Mandatory Access Control (MAC) no-read-up policy for SELECT security in the following way:
  • If the security categories of the user sending the request includes all categories set in the row's CONSTRAINT column, the SELECT is allowed. (For a hierarchical security scheme, the function would evaluate whether the security level of the user is the same or greater than the security level of the row.)
  • If the row has no security categories defined (the value of the CONSTRAINT column is NULL), the SELECT is allowed regardless of the user's categories.

// selectcompartment.c

#define SQL_TEXT Latin_Text
#include <sys/types.h>
#include "sqltypes_td.h"

typedef unsigned char byte;

void RLS_SelectCompartment(byte *UserCompartments,
byte *RowCompartments,
char *AccessAllowed,
int *UserCompartments_i,
int *RowCompartments_i,
int *AccessAllowed_i)
{
register i;

*AccessAllowed_i = 0; // Never returns NULL result

// Enforce no-read-up policy
// User's compartments must dominate the row compartments

// If row compartments are NULL, then SELECT is allowed
if (*RowCompartments_i == -1) {
*AccessAllowed = 'T';
return;
}

// Row compartments are not NULL
// If user compartments are NULL, then SELECT is not allowed
if (*UserCompartments_i == -1) {
*AccessAllowed = 'F';
return;
}

// User and row compartments are not NULL
// Check for user compartments dominating row compartments
for (i = 0; i < 2; i++) {
if ((RowCompartments[i] ^ UserCompartments[i]) & RowCompartments[i]) {
// SELECT is not allowed
*AccessAllowed = 'F';
return;
}
}

// SELECT is allowed
*AccessAllowed = 'T';
return;
}

The following SQL code creates the SELECT policy UDF used for enforcing RLS. When the UDF is created, the C code is compiled.

REPLACE FUNCTION SYSLIB.RLS_SelectCompartment
(CURRENT_SESSION byte(2), INPUT_ROW byte(2))
RETURNS char(1)
SPECIFIC SYSLIB.RLS_SelectCompartment
LANGUAGE C
DETERMINISTIC
NO SQL
PARAMETER STYLE SQL
EXTERNAL NAME 'CS!RLS_SelectCompartment!C:\myC\RLS\selectcompartment.c';

The RLS_SelectCompartment UDF will be part of the CONSTRAINT object that is created for this RLS scenario.





Example: Create an INSERT Security Policy UDF


The following C code defines a scalar function invoked by the RLS security policy UDF for INSERT statements. It determines the value that is inserted into the CONSTRAINT column of a table protected with RLS.

The INSERT policy function has two arguments, which are passed to the function by Teradata Database when an INSERT request involves a table protected by RLS:
  • UserCompartments holds the security group or groups of the user.
  • NewRowCompartments holds the value returned by the function that will be assigned to the constraint column of the inserted row.

The function reads the security categories of the user who is inserting the row, and assigns them to the CONSTRAINT column of the newly inserted row. If the user has no assigned security categories, the row is inserted with a CONSTRAINT column value of NULL.

// insertcompartment.c

#define SQL_TEXT Latin_Text
#include <sys/types.h>
#include "sqltypes_td.h"

typedef unsigned char byte;

void RLS_InsertCompartment(byte *UserCompartments,
byte *NewRowCompartments,
int *UserCompartments_i,
int *NewRowCompartments_i)
{
register i;

// Policy is to set row compartments to the user compartments
// specified for the session

// If user/session compartments are NULL, then return NULL
if (*UserCompartments_i == -1) {
*NewRowCompartments_i = -1;
return;
}

// User/session compartments are not NULL
for (i = 0; i < 2; i++)
NewRowCompartments[i] = UserCompartments[i];
*NewRowCompartments_i = 0;
return;
}

Note: The C code defines four arguments rather than two to allow for the possibility of NULL being passed to the UDF. Because this scenario uses a non-hierarchical security strategy, our CONSTRAINT object was defined as data type BYTE(2), which allows the security coding to be passed to the security functions. We also defined the CONSTRAINT to allow NULL values, for cases where users do not have an associated security group designation. Teradata Database passes NULL indicators as integer data types, so the function needs to allow for that by including integer arguments in addition to byte arguments.

The following SQL code creates the INSERT policy UDF used for enforcing RLS. When the UDF is created, the C code is compiled.

REPLACE FUNCTION SYSLIB.RLS_InsertCompartment
(CURRENT_SESSION byte(2))
RETURNS byte(2)
SPECIFIC SYSLIB.RLS_InsertCompartment
LANGUAGE C
DETERMINISTIC
NO SQL
PARAMETER STYLE SQL
EXTERNAL NAME 'CS!RLS_InsertCompartment!C:\myC\RLS\insertcompartment.c';

The RLS_InsertCompartment UDF will be part of the CONSTRAINT object that is created for this RLS scenario.





Example: Create an UPDATE Security Policy UDF


The following C code defines a scalar function invoked by the RLS security policy UDF for UPDATE statements. It determines which rows of a table an UPDATE request is allowed to access.

The UPDATE policy function has three arguments, which are passed to the function by Teradata Database when a request involves a table protected by RLS:
  • UserCompartments holds the security category or categories of the user.
  • RowCompartments is the constraint column value from the row that is being evaluated to determine whether access will be allowed.
  • NewRowCompartments is the result of the function that is returned. The constraint column value is replaced with this value in the row that is updated.

The function compares the first two argument values to determine whether access to the row is allowed. For this scenario, the function enforces the Mandatory Access Control (MAC) no-write-down policy for UPDATE security in the following way:

  • If the user security includes one or more RLS categories that are set for the row in the CONSTRAINT column, and does not include any categories that are not set for the row, the UPDATE is allowed.
  • If neither the user nor the row have any RLS categories set (that is, both are NULL), the UPDATE is allowed.
    Note: Although the policy is written to allow users without security labels to update NULL columns, UPDATE operations filter rows using the SELECT policy function before the UPDATE is applied. Because our SELECT function does not allow users with no security to SELECT rows, users having an RLS category of NULL will not be able to UPDATE NULL rows.

  • If the user has an assigned RLS category, but the row RLS category is NULL, the UPDATE is allowed.
  • If the user RLS category is NULL, but the row being evaluated has a non-NULL RLS category set, the UPDATE is not allowed.
  • If UPDATE is allowed, the CONSTRAINT column of the updated row is assigned an RLS category matching that of the user who performed the update.
// updatecompartment.c

#define SQL_TEXT Latin_Text
#include <sys/types.h>
#include "sqltypes_td.h"

typedef unsigned char byte;

void RLS_UpdateCompartment(byte *UserCompartments,
byte *RowCompartments,
byte *NewRowCompartments,
int *UserCompartments_i,
int *RowCompartments_i,
int *NewRowCompartments_i)
{
register i;

// Enforce no-write-down policy
// Row compartments must dominate the user's compartments

// If user/session and row compartments are NULL, then
// UPDATE is allowed with NULL
if (*UserCompartments_i == -1) {
if (*RowCompartments_i == -1) {
*NewRowCompartments_i = -1;
// SET row compartments to NULL
return;
} else {
// User/session compartments are NULL
// but row compartments are not
// UPDATE is not allowed
for (i = 0; i < 2; i++)
NewRowCompartments[i] = 0;
*NewRowCompartments_i = 0;
return;
}
}

// If row compartments are NULL, then
// UPDATE is allowed with user/session compartments
if (*RowCompartments_i == -1) {
for (i = 0; i < 2; i++)
NewRowCompartments[i] = UserCompartments[i];
*NewRowCompartments_i = 0;
return;
}

// Check for row compartments dominating
// user/session compartments
for (i = 0; i < 8; i++) {
if ((UserCompartments[i] & RowCompartments[i]) != RowCompartments[i]) {
// UPDATE is not allowed
for (i = 0; i < 2; i++)
NewRowCompartments[i] = 0;
*NewRowCompartments_i = 0;
return;
}
}

// UPDATE is allowed with inclusive
// user/session and row compartments
for (i = 0; i < 2; i++)
NewRowCompartments[i] = UserCompartments[i] | RowCompartments[i];
*NewRowCompartments_i = 0;
return;
}

The following SQL code creates the UPDATE policy UDF used for enforcing RLS. When the UDF is created, the C code is compiled.

REPLACE FUNCTION SYSLIB.RLS_UpdateCompartment
(CURRENT_SESSION byte(2), INPUT_ROW byte(2))
RETURNS byte(2)
SPECIFIC SYSLIB.RLS_UpdateCompartment
LANGUAGE C
DETERMINISTIC
NO SQL
PARAMETER STYLE SQL
EXTERNAL NAME 'CS!RLS_UpdateCompartment!C:\myC\RLS\updatecompartment.c';

The RLS_UpdateCompartment UDF will be part of the CONSTRAINT object that is created for this RLS scenario.





Example: Create a DELETE Security Policy UDF


The following C code defines a scalar function invoked by the RLS security policy UDF for DELETE statements. It determines whether the user is allowed to delete a row.

The DELETE policy function has two arguments, which are passed to the function by Teradata Database when an INSERT request involves a table protected by RLS:
  • RowCompartments is the value of the CONSTRAINT column of the row being evaluated for deletion. It codes for the security category or categories of the row.
  • DeleteAllowed is the value returned by the function that indicated if the DELETE operation on the row is allowed.

In our scenario, the row can be deleted only if it has no security categories (the value of the CONSTRAINT column for the row is NULL).

// deletecompartment.c

#define SQL_TEXT Latin_Text
#include <sys/types.h>
#include "sqltypes_td.h"

typedef unsigned char byte;

void RLS_DeleteCompartment(byte *RowCompartments,
char *DeleteAllowed,
int *RowCompartments_i,
int *DeleteAllowed_i)
{

// Policy is that row can be deleted only if its compartments are NULL
// If row compartments are NULL, then DELETE is allowed
if (*RowCompartments_i == -1)
*DeleteAllowed = 'T';
else
// DELETE is not allowed
*DeleteAllowed = 'F';

*DeleteAllowed_i = 0;
return;
}

The following SQL code creates the DELETE policy UDF used for enforcing RLS. When the UDF is created, the C code is compiled.

REPLACE FUNCTION SYSLIB.RLS_DeleteCompartment
(INPUT_ROW byte(2))
RETURNS char(1)
SPECIFIC SYSLIB.RLS_DeleteCompartment
LANGUAGE C
DETERMINISTIC
NO SQL
PARAMETER STYLE SQL
EXTERNAL NAME 'CS!RLS_DeleteCompartment!C:\myC\RLS\deletecompartment.c';

The RLS_DeleteCompartment UDF will be part of the CONSTRAINT object that is created for this RLS scenario.





Example: Create a CONSTRAINT Object


A CONSTRAINT object defines the security levels or groups used in an RLS scheme, and embodies the security functions used in that scheme.

The CONSTRAINT is referenced by:
  • a special column of RLS-protected tables, and used to restrict row access according to the security levels or groups defined in the CONSTRAINT object
  • CREATE USER statements to set the security levels or groups of users whose access to table rows will be limited

The following SQL statement creates a CONSTRAINT object that defines four non-hierarchical security groups based on country. It relates those security groups to the security UDFs that are used to enforce RLS. With a data type of BYTE(2), we can define up to 16 security levels, although we will only defined four here to make the example clearer. Non-hierarchical RLS schemes allow you to define CONSTRAINT objects having data types up to BYTE(32), which would allow up to 256 different security levels. (Hierarchical security level schemes use CONSTRAINT objects defined as data type SMALLINT, and can support up to 10,000 progressively restrictive security levels.)

CREATE CONSTRAINT RLS_IUDS_Country_Compartment BYTE(2),
VALUES (US:1, UK:2, CAN:3, GER:4),
INSERT SYSLIB.RLS_InsertCompartment,
UPDATE SYSLIB.RLS_UpdateCompartment,
DELETE SYSLIB.RLS_DeleteCompartment,
SELECT SYSLIB.RLS_SelectCompartment;





Example: Create a Table Protected With RLS


The following SQL creates a table in the hr_db database that includes a CONSTRAINT column (RLS_IUDS_Country_Compartment) that is used to enforce RLS. Next, the example inserts five rows of data into the table to represent five employees who work in four countries.

DATABASE hr_db;

CREATE TABLE t1, FALLBACK
(
employee_id INTEGER,
employee_name CHARACTER(15),
employee_location CHARACTER(3),
RLS_IUDS_Country_Compartment CONSTRAINT
);

INSERT INTO t1 VALUES (1, 'John','UK','4000'xb)
;INSERT INTO t1 VALUES (2, 'Mary','US','8000'xb)
;INSERT INTO t1 VALUES (3, 'Adam','UK','4000'xb)
;INSERT INTO t1 VALUES (4, 'Simon','CAN','2000'xb)
;INSERT INTO t1 VALUES (5, 'Peter','GER','1000'xb)
;

In the CONSTRAINT object, every security category or level is assigned an associated numeric value. In our scenario, these categories and associated values correspond to a country. The two-byte hex value inserted into the CONSTRAINT column for each employee row represents the security category that a user must have in order to have access to the row. For more information on how these hex values are determined, see the Orange Book: Teradata Row Level Security, Document # 541-0005614.

HR database administrative users will be required to have a security group that corresponds to the CONSTRAINT value of these rows in order to perform certain SQL operations on the rows. Administrative users can be assigned to more than one security category to allow them access to data from more than a single country.





Example: Create Users That Have RLS Security Constraints


The following SQL creates three HR department database users. Notice that the CREATE USER statement includes the CONSTRAINT option, which assigns each user to one or more RLS security categories:
  • hr_corp is assigned to security categories that allow access to rows designated as containing information for employees in all four countries.
  • hr_americas is assigned to security categories that allow access only to rows designated as containing information for employees from the US and Canada.
  • hr_europe is assigned to security categories that allow access only to rows designated as containing information for employees from the UK and Germany.

Note: Assigning multiple categories to a user is allowed for non-hierarchical RLS schemes.

The GRANT statements allow the users full access to the objects they create and general DML access to objects in the hr_corp database. These statements are not related to RLS.

create user hr_corp from sysdba as
perm = 0
,password = hr_corp
,default database = hr_db
,account = 'hr_corp'
,spool =100000
,no fallback
,no before journal
,no after journal
,CONSTRAINT = RLS_IUDS_Country_Compartment (US, UK, CAN, GER)
;

grant all on hr_corp to hr_corp with grant option;
grant SELECT, INSERT, UPDATE, DELETE on hr_db to hr_corp with grant option;

create user hr_americas from hr_corp as
perm = 0
,password = hr_americas
,default database = hr_db
,account = 'hr_americas'
,spool = 100000
,no fallback
,no before journal
,no after journal
,CONSTRAINT = RLS_IUDS_Country_Compartment (US, CAN)
;

grant all on hr_americas to hr_americas;
grant SELECT, UPDATE on hr_db to hr_americas;

create user hr_europe from hr_corp as
perm = 0
,password = hr_europe
,default database = hr_db
,account = 'hr_europe'
,spool = 100000
,no fallback
,no before journal
,no after journal
,CONSTRAINT = RLS_IUDS_Country_Compartment (UK, GER)

;

grant all on hr_europe to hr_europe;
grant SELECT, UPDATE on hr_db to hr_europe;





Examples: Accessing Rows in a Table Protected With RLS


User hr_corp can view all rows labeled as having information restricted to US, UK, CAN, or GER RLS categories. Those are the rows that contain the binary representation of US, UK, CAN, or GER categories in the CONSTRAINT column (RLS_IUDS_Country_Compartment).

.logon iedbc/hr_corp,

SELECT * from t1;

employee_id employee_name employee_location RLS_IUDS_Country_Compartment
----------- --------------- ----------------- ----------------------------
5 Peter GER 1000
4 Simon CAN 2000
3 Adam UK 4000
1 John UK 4000
2 Mary US 8000
+---------+---------+---------+---------+---------+---------+---------+----

User hr_americas can only view rows labeled for US, CAN, or both.

.logon iedbc/hr_americas,

SELECT * from t1;

employee_id employee_name employee_location RLS_IUDS_Country_Compartment
----------- --------------- ----------------- ----------------------------
2 Mary US 8000
4 Simon CAN 2000
+---------+---------+---------+---------+---------+---------+---------+----

User hr_europe can view only rows labeled for UK, GER, or both.

.logon iedbc/hr_europe,

SELECT * from t1;

employee_id employee_name employee_location RLS_IUDS_Country_Compartment
----------- --------------- ----------------- ----------------------------
5 Peter GER 1000
3 Adam UK 4000
1 John UK 4000
+---------+---------+---------+---------+---------+---------+---------+----

User hr_corp changes his RLS session info from US, UK, CAN, and GER to US only. The INSERT policy will use the session info to set the CONSTRAINT column value for the new row. If this was not done the CONSTRAINT column would be loaded with ‘F000’xb instead of ‘8000’xb, where ‘F000’xb represents the combined binary values of US, UK, CAN, and GER. Only sysdba and hr_corp could view the new row.

.logon iedbc/hr_corp,

SET SESSION CONSTRAINT = RLS_IUDS_Country_Compartment (US);

*** Set SESSION accepted.
+---------+---------+---------+---------+---------+---------+---------+----

INSERT INTO t1 VALUES (6, 'Bob','US',);

*** Insert completed. One row added.
+---------+---------+---------+---------+---------+---------+---------+----

Because the session constraint was set to US, in this session hr_corp can view only rows with US in the security compartment.

SELECT * from t1;

employee_id employee_name employee_location RLS_IUDS_Country_Compartment
----------- --------------- ----------------- ----------------------------
2 Mary US 8000
6 Bob US 8000
+---------+---------+---------+---------+---------+---------+---------+----

With a new logon hr_corp can again view rows with any of the country categories in the CONSTRAINT column.

.logon iedbc/hr_corp,

SELECT * from t1;

employee_id employee_name employee_location RLS_IUDS_Country_Compartment
----------- --------------- ----------------- ----------------------------
5 Peter GER 1000
4 Simon CAN 2000
6 Bob US 8000
3 Adam UK 4000
1 John UK 4000
2 Mary US 8000
+---------+---------+---------+---------+---------+---------+---------+----

.logon iedbc/hr_americas,

SELECT * from t1;

employee_id employee_name employee_location RLS_IUDS_Country_Compartment
----------- --------------- ----------------- ----------------------------
2 Mary US 8000
4 Simon CAN 2000
6 Bob US 8000
+---------+---------+---------+---------+---------+---------+---------+----

.logon iedbc/hr_europe,

SELECT * from t1;

employee_id employee_name employee_location RLS_IUDS_Country_Compartment
----------- --------------- ----------------- ----------------------------
5 Peter GER 1000
3 Adam UK 4000
1 John UK 4000
+---------+---------+---------+---------+---------+---------+---------+----

hr_americas has UPDATE rights on t1 and can change any column except the CONSTRAINT column.

.logon iedbc/hr_americas,

UPDATE t1 SET employee_name = 'Robert' WHERE employee_id = 6;

*** Update completed. One row changed.
+---------+---------+---------+---------+---------+---------+---------+----

sysdba has all OVERRIDE CONSTRAINT rights and can set the CONSTRAINT column to NULL. The row is now visible to all users and can be deleted.

.logon iedbc/sysdba,

UPDATE hr_db.t1 SET RLS_IUDS_Country_Compartment = NULL WHERE employee_id =
6;

*** Update completed. One row changed.
+---------+---------+---------+---------+---------+---------+---------+----

.logon iedbc/hr_europe,

SELECT * from t1;

employee_id employee_name employee_location RLS_IUDS_Country_Compartment
----------- --------------- ----------------- ----------------------------
5 Peter GER 1000
6 Robert US ?
3 Adam UK 4000
1 John UK 4000
+---------+---------+---------+---------+---------+---------+---------+----

hr_corp has DELETE rights on t1 and can DELETE the row now that the CONSTRAINT column is NULL.

.logon iedbc/hr_corp,

DELETE t1 WHERE employee_id = 6;

*** Delete completed. One row removed.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

SELECT * from t1;

employee_id employee_name employee_location RLS_IUDS_Country_Compartment
----------- --------------- ----------------- ----------------------------
5 Peter GER 1000
4 Simon CAN 2000
3 Adam UK 4000
1 John UK 4000
2 Mary US 8000

+---------+---------+---------+---------+---------+---------+---------+----



<SCRIPT type="text/javascript"><BR /><BR /> jQuery(function() {<BR /> jQuery("span.notetitle").css({"font-weight":"bold"});<BR /> jQuery("div.note").css({"border":"1px solid grey"});<BR /> jQuery("pre").css({"background-color":"#EEEEEE", "border":"1px solid black"});<BR /> jQuery(".toggle, .toggle-02, .toggle-03, .toggle-04, .toggle-05, .toggle-06, .toggle-07, .toggle-08, .toggle-09, .toggle-10, .toggle-11").css({"cursor":"pointer"});<BR /> jQuery(".expandme").css({"color":"#0E45FF", "text-decoration":"underline", "margin-left":"2em"});<BR /> jQuery(".toggle-02, .toggle-03, .toggle-04, .toggle-05, .toggle-06, .toggle-07, .toggle-08, .toggle-09, .toggle-10, .toggle-11").css( <BR /> {"background-image":"url('https://developer.teradata.com/sites/all/files/plus_arrow.gif')", <BR /> "background-repeat":"no-repeat", <BR /> "background-position":"5px 7px",<BR /> "text-indent":"2em",<BR /> "display":"block"});<BR /> jQuery(".panel, .panel-02, .panel-03, .panel-04, .panel-05, .panel-06, .panel-07, .panel-08, .panel-09, .panel-10, .panel-11").css({"display":"none"});<BR /> jQuery("[class^='toggle-']").append("<div style='color:#0E45FF; text-decoration:underline;'>More...</div>");<BR /> });<BR /> jQuery(function() {<BR /> jQuery(".toggle").click(function(){<BR /> jQuery(".panel").slideToggle("fast"); <BR /> });<BR /> jQuery(".toggle-02").click(function(){<BR /> jQuery(".panel-02").slideToggle("fast", function() {<BR /> jQuery(".toggle-02").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> });<BR /> jQuery(".toggle-03").click(function(){<BR /> jQuery(".panel-03").slideToggle("fast", function() {<BR /> jQuery(".toggle-03").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> });<BR /> jQuery(".toggle-04").click(function(){<BR /> jQuery(".panel-04").slideToggle("fast", function() {<BR /> jQuery(".toggle-04").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> });<BR /> jQuery(".toggle-05").click(function(){<BR /> jQuery(".panel-05").slideToggle("fast", function() {<BR /> jQuery(".toggle-05").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> });<BR /> jQuery(".toggle-06").click(function(){<BR /> jQuery(".panel-06").slideToggle("fast", function() {<BR /> jQuery(".toggle-06").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> });<BR /> jQuery(".toggle-07").click(function(){<BR /> jQuery(".panel-07").slideToggle("fast", function() {<BR /> jQuery(".toggle-07").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> });<BR /> jQuery(".toggle-08").click(function(){<BR /> jQuery(".panel-08").slideToggle("fast", function() {<BR /> jQuery(".toggle-08").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> });<BR /> jQuery(".toggle-09").click(function(){<BR /> jQuery(".panel-09").slideToggle("fast", function() {<BR /> jQuery(".toggle-09").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> });<BR /> jQuery(".toggle-10").click(function(){<BR /> jQuery(".panel-10").slideToggle("fast", function() {<BR /> jQuery(".toggle-10").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> });<BR /> jQuery(".toggle-11").click(function(){<BR /> jQuery(".panel-11").slideToggle("fast", function() {<BR /> jQuery(".toggle-11").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> }); <BR /> });<BR /></SCRIPT>
8 REPLIES
Enthusiast

Re: Row-level security

Hi,

Thanks for this nice article. I have one question here as we are planning to implement RLS at our site.

CREATE CONSTRAINT Compartment BYTE(8), NULL,
VALUES (USA:1, Canada:2, UK:3, France:4, Germany:5)
SELECT SYSLIB.SelectCompartment,
INSERT SYSLIB.InsertCompartment,
UPDATE SYSLIB.UpdateCompartment,
DELETE SYSLIB.DeleteCompartment;

 

USA:1 ‘8000000000000000’xb
1000000000000000000000000000000000000000000000000000000000000000

 

Canada:2 ‘4000000000000000’xb
0100000000000000000000000000000000000000000000000000000000000000

 

In the Orange Book for RLS, i see that above mapping is given for different values that map USA,Canada etc. I also undersatnd that hex equivalent of - 1000000000000000000000000000000000000000000000000000000000000000 is ‘8000000000000000’xb.

and hex equivalent of 0100000000000000000000000000000000000000000000000000000000000000 is ‘4000000000000000’xb.

 

 

So, the question is :

1. How do we map value 1 in USA:1 to ‘8000000000000000’xb and 1000000000000000000000000000000000000000000000000000000000000000 ? If i convert this binary and hex value to decimal, it is not 1. I need to know this mapping because if i want to add another country for e.g INDIA:6 later, what will be the corresponding Hex value ? I would need to have that value in my insert statements to the table.

Also, how to we map value 2 in Canada:2 to its hex and binary value  below ?

Canada:2 ‘4000000000000000’xb
0100000000000000000000000000000000000000000000000000000000000000

 

 

2. Could you also explain the - Canada and France part ?

But i couldnot understand the below part :

"Further, in this example, a compartment that represents Canada and France would have values
as follows:
‘5000000000000000’xb
0101000000000000000000000000000000000000000000000000000000000000"

 

3. Some more details on this mapping so as to understand this concept would really be appreciated.

 

--Thx Samir

 

Apprentice

Re: Row-level security

Hi Samir,

 

For 'compartment' constraints the values are not the traditional hex equivalents as you're thinking of them.

 

Think of them as 'bit numbers' (from the left) in a BYTE(8) field, they are not data values.

 

Using the example you highlighted:

CREATE CONSTRAINT Compartment BYTE(8), NULL,
VALUES (USA:1, Canada:2, UK:3, France:4, Germany:5)

If bit#1 is turned on then this means that the user can read 'USA' data (because USA=1)

If bit#2 is turned on then this means that the user can read 'Canada' data (because Canada=2)

 

If a user is allowed to read data for both Canada and France then the constraint for the row needs to have either bit#2 or bit#4 turned on.

 

Does that help?

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Row-level security

Thanks Dave for the reply. So, as i understand, its the ordinal position of the bits that needs to be enabled. So, if i add another country e.g India:6, the bits will be as below.I need to enable 6th bit.

0000010000000000000000000000000000000000000000000000000000000000

And while inserting into the table, i need to convert this binary numberto Hex equivalent and then insert the values with other column vaules. Please let me know if my understabnding is correct.

 

Also, from your text below. For access to both Canada and France, bit#2 and bit#4 should be enabled rt ? You mentioned either one of them.

"If a user is allowed to read data for both Canada and France then the constraint for the row needs to have either bit#2 or bit#4 turned on."

 

--Thx Samir

Highlighted
Apprentice

Re: Row-level security

Hi Samir,

 

For the 'Canada and France' situation:

Firstly, the 'rule' would typically be that the user is allowed to read to read data for both Canada and France (i.e they can read either).

 

Given this, the user constraint will have both bits turned on - they can read data for both Canada and France.

 

For the user to read a row, the value in the constraint column in the row needs either bit to be turned on - the row is either for Canada or France.

 

Does that help?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Apprentice

Re: Row-level security

Sorry, should have added...

 

For inserting data you don't code hex values. One way of inserting the correct constraint column value is by using 'set session constraint' - see the article about that.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Row-level security

Dave, Going by your explanation, bit for Either Cannada or France should be set, if i set it for Canada, will he be able to read from France ? - 0100000000000000000000000000000000000000000000000000000000000000

 

CREATE CONSTRAINT Compartment BYTE(8), NULL,

VALUES (USA:1, Canada:2, UK:3, France:4, Germany:5)

SELECT SYSLIB.SelectCompartment,

INSERT SYSLIB.InsertCompartment,

UPDATE SYSLIB.UpdateCompartment,

DELETE SYSLIB.DeleteCompartment;

 

 

 

Enthusiast

Re: Row-level security

Hi Dave,

Do you mean, i can set session constraint for a user and just give an insert command with column values and it will insert appropiate hex value for that particular country ?

something like :

SET SESSION

CONSTRAINT = Compartment (France, Germany);

Insert into table values(1,2) etc

But, how we do this in the ETL ?

 

i see in this article that below insert statements are used , so i had this impression.
 INSERT INTO t1 VALUES (1, 'John','UK','4000'xb)
;INSERT INTO t1 VALUES (2, 'Mary','US','8000'xb)
;INSERT INTO t1 VALUES (3, 'Adam','UK','4000'xb)
;INSERT INTO t1 VALUES (4, 'Simon','CAN','2000'xb)
;INSERT INTO t1 VALUES (5, 'Peter','GER','1000'xb);

Apprentice

Re: Row-level security

Hi Samir,

 

If we look at an example that might make it easier.

 

Assume that we're using RLS against sales transactions.

The business rule is that each row in the sales transaction table is associated with a single country, e.g. the country of the customer.

When the row is inserted the constraint column value will have a single bit set - because the row is associated with a single country.

 

Assume that we have inserted three rows, one for the USA (bit#1), one for Canada (bit#2) and one for France (bit#4).

The constraint values will be:

row#1 USA - 1000.....

row#2 canada - 0100...

row#3 France - 0001...

 

Now assume that we have a USER_CnFr who is associated with Canada and France (and maybe other countries, but assume not USA). Their constraint value will be 0101....

 

When they run a select against the sales transaction table the corresponding bits in the user constraint value and the row constraint value are checked. If any corresponding bit in both are 'on' then the user can see the row.

row# USA - not seen because not corresponding bits are both on

row#2 Canada - bit#2 is on in both, the row is seen

row#3 France- bit#4 is on in both, the row is seen

 

Now assume that we have a USER_Cn who is associated only with Canada. Their constraint value will be 0100....

When they run a select against the sales transaction table the corresponding bits in the user constraint value and the row constraint value are checked. If any corresponding bit in both are 'on' then the user can see the row.

row# USA - not seen because not corresponding bits are both on

row#2 Canada - bit#2 is on in both, the row is seen

row#3 France- not seen because not corresponding bits are both on

 

The above are possible rules

Bear in mind when reading the above that the actual logic is determined by the constraint udf - which 'you' write. As you'll see from the article, the SELECT udf simply returns a 'T' or 'F' and the dbms processes the row or not. You can also see this if you explain the SELECT query against an RLS protected table.

- essentially the Teradata dbms is providing the infrastructure to do this, but your code (the udf's) actually implements your business rules.

 

(Sorry, got to go for a meeting, back later...)

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com