5. Security Module

This module includes object, row, and column level security as well as sandboxing and the administration of users and privileges via APIs. Furthermore, the integration of an external identity management system, such as Active Directory, belongs to this module. Metadata tables hold the respective settings, and refresh jobs apply these settings periodically. Tenants separate the users within the same data pool.

5.1. Administrative Roles

The RED framework provides three administrative roles:

R_APPMGR (Application Manager)

Responsible for the business release process and therefore manages the complete TSA, PSA, and APB layers. Has access to all related APIs.

R_SECMGR (Security Manager)

Handles the tasks of the security framework SCURTY described in this chapter and therefore has access to the SCURTY APIs.

R_APPMON (monitoring)

For monitoring systems and users: read-only access to all relevant views and tables of the RED framework.

5.2. RED Authorisation Concept

Access for issuing queries is only granted to personalised user accounts. This allows for a fine-granular allocation of rights and for meaningful auditing. Authentication and authorisation are also managed via these accounts. The only exceptions from this personalisation rule are generic accounts (e.g. batch users), which do not relate to a physical person.

In 3-tier architectures the identity of the person is forwarded to the database as well, see login process.

Access rights are assigned at object, row, and column level:

LEVEL

RESTRICTS

DEFINED BY

GRANTED VIA

Object level (OLS)

which objects a user can access

object groups with search rules

object group access (manually or via IDM)

Row level (RLS)

which rows of an object a user can see

tenants (tenant columns and codes)

tenant access per object group (manually or via IDM)

Column level (CLS)

whether sensitive column values are visible or NULL

security columns

access to security columns (manually or via IDM)

RED distinguishes three user account types for accessing the PSV layer (reporting layer):

TYPE

DESCRIPTION

PRIVATE SANDBOX

SANDBOX MASTER

Human User (HU)

personalised account of a physical person

yes

yes

Generic User (GU)

account not bound to a person, e.g. for batch processes

yes

no

Technical User (TU)

account of an application server in 3-tier architectures that impersonates human users

User accounts that do not belong to any of these types are labelled as “unclassified user accounts”.

5.2.1. Human User (HU)

To gain access to the database, each physical person gets their own personalised database account, called a human user (HU). An HU can own a private sandbox, which can be created when the user is created or changed. The transfer of access rights on objects in this schema to other user accounts is prohibited and technically prevented. Human users cannot create schemas themselves.

5.2.2. Generic User (GU)

Generic user accounts are not bound to a physical person and must only be used for applications, batch processes, and the like if absolutely necessary.

Generic users are subject to several constraints: they may own a private sandbox, but they cannot become a sandbox master, and they do not have impersonation rights.

5.2.3. Technical User (TU)

In 3-tier architectures the client authenticates against the application server, which then has to forward the identity of the physical person to the database. For this purpose the application server uses a technical user, see login process.

5.3. Logging onto the System

In 2-tier architectures the authentication happens directly through the database login using a personalised user account.

In 3-tier architectures the authentication takes place via an application. The identity of the physical person has to be forwarded to the database in this case. This works by impersonation: the technical user (TU) of the application takes on the identity of the human user (HU) who is logged on to the application. This impersonation function is provided by the Exasol database.

Newer 3-tier systems, for example MicroStrategy, use personalised user accounts for authenticating against the database directly, even if the login happens via a web application.

The following sections show the sequence of the authentication process including an LDAP directory. The LDAP directory is optional; without it, users and passwords are managed by the database itself.

5.3.1. Authenticating Using an LDAP Directory

An external LDAP directory can be used for the authentication against Exasol. The LDAP directory has to be configured in Exasol beforehand; this configuration is not part of this guide.

5.3.1.1. Authentication in 2-Tier Environments

In 2-tier environments the client application authenticates directly against the Exasol database, which checks the password with the LDAP directory (see the figure below):

2-tier login: client application, Exasol database, LDAP directory Client application Exasol database LDAP directory client login (user name and password) 1 password enquiry 2 password confirmation 3 connection established 4
Authentication process in a 2-tier environment

5.3.1.2. Authentication in 3-Tier Environments

In 3-tier environments the client authenticates against the application server, which then logs on to the Exasol database (see the figure below):

3-tier login: client application, application server, Exasol database, LDAP directory Client application Application server e.g. MicroStrategy Exasol database LDAP directory client login 1 login with user credentials 2 password enquiry 3 password confirmation 4 server login confirmed 5 session and start page 6
Authentication process in a 3-tier environment

5.4. Administration of User Accounts

It is recommended to integrate an external identity management system (IDM). However, users and rights can also be administered manually via APIs.

Existing user accounts are listed in the view SCURTY.REP_ALL_USERS.

5.4.1. Integration of an External Identity Management System

User accounts, sandboxes, and rights can be managed automatically by integrating an IDM such as Active Directory. The integration is done via views that map the respective IDM permissions to the security concept (e.g. mapping of AD groups to object groups or tenants). The configuration of an IDM for RED is not part of this guide and should best be done in cooperation with Sphinx IT Consulting.

  • To give a new user access to RED, the relevant access rights are set in the IDM and adopted by RED.

  • User accounts created by the IDM are managed by the IDM from then on. They can be identified in SCURTY.REP_ALL_USERS by a leading “IDM:” in the column USER_COMMENT.

  • If a user account was created manually and the authorisation is then set in the IDM, this does not result in a conflict: the account is not recreated, but its permissions are reset to those defined in the IDM.

  • User accounts are locked as soon as their access rights for RED have been revoked in the IDM.

  • The current status and access rights of the IDM-provisioned user accounts can be found in the SCURTY.IDM_% views.

Important

If an IDM is configured, manually created user accounts with the same name as in the IDM are updated regularly according to the access rights stored in the IDM. Manual changes to the access rights of IDM-managed accounts are reversed by the next IDM refresh.

The complete API documentation for the administration of user accounts, object group access, and tenant access can be found in the SCURTY API documentation.

5.5. Object Level Security

Object groups (OG) combine database objects into one group. Objects are assigned via search rules with regular expressions for schema names, object names, and object comments. For each of these three, include and exclude patterns can be defined. Within a rule the patterns are linked with “and”: an object is added if it matches all include patterns and none of the exclude patterns. Several rules can exist per object group; an object belongs to the group if it matches any of them. Because comments are evaluated as well, objects can be added to or removed from an object group explicitly by changing their comment.

Important

If a search rule does not restrict the objects within a schema, the whole schema is added to the object group, including all objects created in the future. A rule has no restrictions if the parameters p_comment_incl_pattern and p_object_incl_pattern are set to ‘.*’ while p_comment_excl_pattern and p_object_excl_pattern are NULL. If an include and an exclude pattern apply to the same schema, object, or comment at the same time, the object is not added to the object group.

In addition, global exclusion rules (SCURTY.REP_EXCL_OBJ_DISCOVER_RULES) prevent objects from being assigned to any object group.

A refresh job (rep_refresh, every 30 minutes by default, defined in SCURTY.ADM_JOBS) evaluates the search rules and adds or removes objects accordingly. Objects cannot be removed from an object group manually, only by changing the search rules. The time of the last successful refresh per security domain is shown in SCURTY.ADM_REFRESH_JOB_STATUS.

Object groups are implemented as database roles and are mainly used for the views in the PSV (reporting) layer.

User accounts can be granted access to one or more object groups, either manually by a Security Manager or automatically by the IDM. The current access rights are stored in SCURTY.REP_USER_OG_ACCESS.

Important

Access rights can only be granted to existing object groups (see SCURTY.REP_OBJECT_GROUPS). WRITE access can only be granted to object groups that have been created with WRITE access, and access rights can only be granted to classified user accounts (types HU and GU).

If an IDM is used, its information is provided to RED via an interface view. The IDM refresh job (idm_refresh, every 30 minutes by default, defined in SCURTY.ADM_JOBS) creates the access rights from this information and stores them in SCURTY.REP_USER_OG_ACCESS, just as if they had been granted manually.

Important

Access to an object group does not necessarily mean access to data. Row or column level security might prevent the user from seeing a single row.

5.6. Row Level Security

Access to data in the PSV views can be restricted at row level depending on the user account, e.g. to a particular BDomain or any other suitable attribute. For this purpose, table columns that decide whether access to a row is granted are defined as tenant columns, e.g. BDOMAIN, BRANCH, or REGION. Tenants are organised in tenant groups; the special code ALL grants access to all tenants of a group.

A user account is granted access to one or more tenant codes per object group. A tenant works like an implicit SQL WHERE condition that is fixed and cannot be changed by the user. Tenant access is granted either manually by a Security Manager or automatically by the IDM.

In the following example two users query the same table. User 1 has access to department DEP2, user 2 to institute 196 of department DEP2:

Table 5.1 Base table

INSTITUTE

DEPARTMENT

AMOUNT

196

DEP1

5000

103

DEP2

300

196

DEP2

400

104

DEP2

10000

User 1: DEPARTMENT = 'DEP2'

INSTITUTE

DEPARTMENT

AMOUNT

103

DEP2

300

196

DEP2

400

104

DEP2

10000

User 2: INSTITUTE = 196 AND DEPARTMENT = 'DEP2'

INSTITUTE

DEPARTMENT

AMOUNT

196

DEP2

400

5.7. Column Level Security

In addition, access to data in the PSV views can be restricted at column level depending on the user account, e.g. for sensitive columns such as salaries or margins. Security columns are defined in a similar way to tenants. The select list of a view with security columns is modified accordingly and cannot be changed by the user: depending on the access rights, a user sees either the column value or NULL. Access to security columns is granted either manually by a Security Manager or automatically by the IDM; it can also be restricted to certain tenants.

In the following example the column AMOUNT of the base table (Table 5.1) is a security column. User 1 may see it only for the institutes 103 and 104, user 2 not at all:

User 1: security columns only for institutes 103 and 104

INSTITUTE

DEPARTMENT

AMOUNT

196

DEP1

(null)

103

DEP2

300

196

DEP2

(null)

104

DEP2

10000

User 2: no security columns

INSTITUTE

DEPARTMENT

AMOUNT

196

DEP1

(null)

103

DEP2

(null)

196

DEP2

(null)

104

DEP2

(null)

Users with the role R_SEC_EXEMPT_ACCESS are excluded from row and column level security. Grant this role only where it is absolutely necessary.

5.8. Sandboxing

Sandboxes are database schemas in which objects can be created, e.g. for development, tests, evaluations, or analyses. Sandboxes are secure playgrounds for non-productive use, i.e. not for reporting or ETL processes.

There are two types of sandboxes: private sandboxes and project-related sandboxes.

5.8.1. Private Sandboxes

A private sandbox is a schema that is assigned to exactly one user account and is provided with a quota. Objects created in this schema must not be used by other user accounts; the transfer of rights (grants) is prohibited and technically prevented. Within a private sandbox there are no access restrictions at object or row level, as the user account owns the objects and therefore has full access to them. A private sandbox cannot be given an expiry date. Quotas (storage space) are assigned manually or automatically. Only one private sandbox can be created per user account.

5.9. Auditing

Complete SQL auditing can be done by the Exasol database. When this feature is activated, every interaction with the database is recorded and can be used to investigate possible incidents. See Exasol Auditing for details.