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.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):
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):
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_USERSby a leading “IDM:” in the columnUSER_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:
INSTITUTE |
DEPARTMENT |
AMOUNT |
|---|---|---|
196 |
DEP1 |
5000 |
103 |
DEP2 |
300 |
196 |
DEP2 |
400 |
104 |
DEP2 |
10000 |
DEPARTMENT = 'DEP2'INSTITUTE |
DEPARTMENT |
AMOUNT |
|---|---|---|
103 |
DEP2 |
300 |
196 |
DEP2 |
400 |
104 |
DEP2 |
10000 |
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:
INSTITUTE |
DEPARTMENT |
AMOUNT |
|---|---|---|
196 |
DEP1 |
(null) |
103 |
DEP2 |
300 |
196 |
DEP2 |
(null) |
104 |
DEP2 |
10000 |
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.