IGEL UMS External Database Permissions and User Roles

This article describes the database permissions required by the IGEL Universal Management Suite (UMS) database user. These permissions are required so that UMS can perform the database actions used in the relevant workflows, such as installation, update, and runtime operations.

Depending on the security requirements of the environment, it is possible to use either one database user for all UMS database workflows or two different database users:

One user for the installation or update UMS workflow
One user for running the UMS workflow

Using two users increases security by limiting the risk of accidental or malicious schema changes during normal operation.

Environment

IGEL UMS with one of the following External Databases:

Microsoft SQL Server
Oracle Database
PostgreSQL

UMS Database Workflow Definitions

UMS requires different database permissions depending on the workflow being executed. The database user must have the permissions required for the actions that UMS performs in the corresponding workflow.

Workflow	Description
Installation	Initial creation of the UMS database schema and database objects
Update	Schema and object changes performed during a UMS update
Runtime	Normal UMS operation after installation or update
Directory operations	Runtime directory-related operations, depending on the database type
Device naming convention reset	Operations that reset or change the thin client naming counter

Summary of Required Permissions by Database Type

Microsoft SQL Server

For Microsoft SQL Server, the permissions can be assigned based on database roles.

Workflow	Required Role or Permission
Installation, Update	`db_ddladmin`, `db_datareader`, `db_datawriter` or `db_owner`
Runtime	`db_datareader`, `db_datawriter`

Oracle

For Oracle, the required privileges depend on whether the same user owns the UMS database objects.

Workflow	Required Privilege
Installation, Update	`CREATE SESSION`, `CREATE TABLE`, `ALTER TABLE`, `CREATE INDEX`, `CREATE TRIGGER`, `CREATE PROCEDURE`, `CREATE SEQUENCE`, `CREATE VIEW` or `RESOURCE`, `CREATE VIEW`
Runtime	`CREATE SESSION`

PostgreSQL

For PostgreSQL, the following privileges are required.

Workflow	Required Privilege
Installation, Update	`USAGE`, `CREATE`, `UPDATE`, `SELECT`
Runtime	`USAGE`, `INSERT`, `UPDATE`, `SELECT`

How to Separate Roles for Installation/Update and Runtime

The IGEL UMS is designed to work with one database. If separate roles are required for installation/update and runtime, there are two possible approaches:

Create two datasource definitions in the UMS Administrator, one for each database user, and switch between them.
Create one datasource definition with one database user and change the permissions assigned to that user before and after installation or update tasks.

After the first activation of a datasource, the UMS Server starts immediately with this datasource. Switching the database role or datasource requires a restart of the UMS Server.

After a UMS update, database changes are performed by the UMS Server during startup. Therefore, the datasource or database permissions used for the update must be active when the UMS Server starts for the first time after the update.

In a two-role setup, additional GRANTs may be required when new database objects are created. After an update, permissions for newly created tables, sequences, views, or other objects may need to be granted manually to the runtime user.

Microsoft SQL Server Role Separation

Microsoft SQL Server allows two users to access the same schema. Therefore, two datasources can be configured in the UMS Administrator.

User	Purpose	Required Roles
`ddl_user`	Installation and update	`db_ddladmin`, `db_datareader`, `db_datawriter` or `db_owner`
`data_user`	Runtime	`db_datareader`, `db_datawriter`

Use ddl_user for installation. Before updating UMS, activate the datasource that uses ddl_user and keep it active throughout the update workflow.

After the first complete UMS startup following installation or update, the datasource using data_user can be activated for normal runtime operation.

Device Naming Convention Reset Workflow

If device names must be reset to start from 1 again, UMS needs to change the current value of a sequence. The runtime user might not have the required permissions for this operation. In this case, temporarily switch to ddl_user, perform the action, and then switch back to data_user.

Oracle Role Separation

In Oracle, users and schemas are closely related. A database user is normally identified by the schema that belongs to the user. If two users are used, at least one of them must be granted access to the database objects of the other user.

Option 1: One User with Temporarily Elevated Privileges

Click for details...

Create a user, for example ddl_user and grant the required privileges for installation or update:

Run the following SQL as SYSDBA:

SQL

GRANT CREATE SESSION TO ddl_user;
GRANT RESOURCE TO ddl_user;
GRANT CREATE VIEW TO ddl_user;
GRANT UNLIMITED TABLESPACE TO ddl_user;

Alternatively, RESOURCE can be replaced with more specific grants:

SQL

GRANT CREATE TABLE TO ddl_user;
GRANT CREATE ANY INDEX TO ddl_user;
GRANT CREATE TRIGGER TO ddl_user;
GRANT CREATE PROCEDURE TO ddl_user;
GRANT CREATE SEQUENCE TO ddl_user;
GRANT UNLIMITED TABLESPACE TO ddl_user;

→ Use this user to install or update UMS.

After the UMS Server has started successfully for the first time, or after the update has completed, the installation/update privileges can be revoked:

SQL

REVOKE RESOURCE FROM ddl_user;
REVOKE CREATE VIEW FROM ddl_user;

Before the next UMS update, grant the privileges again:

SQL

GRANT RESOURCE TO ddl_user;
GRANT CREATE VIEW TO ddl_user;

Option 2: Two Users

Click for details...

A two-user setup can be used if the runtime user must have fewer privileges.

User	Purpose
`ddl_user`	Owns and creates the UMS database objects
`data_user`	Performs normal UMS runtime operations

First, use ddl_user to create the database objects during UMS installation or update.

After UMS has been installed and started successfully:

Create data_user and grant the required runtime privilege:
SQL
```
GRANT CREATE SESSION TO data_user;
```

Grant access to the tables owned by ddl_user:

SQL

BEGIN
 FOR t IN (
  SELECT table_name
  FROM all_tables
  WHERE owner = 'DDL_USER'
 ) LOOP
  EXECUTE IMMEDIATE
  'GRANT SELECT, INSERT, UPDATE, DELETE ON DDL_USER.' ||
  t.table_name || ' TO DATA_USER';
 END LOOP;
END;
/

Grant access to the views owned by ddl_user:

SQL

BEGIN
 FOR v IN (
  SELECT view_name
  FROM all_views
  WHERE owner = 'DDL_USER'
 ) LOOP
  EXECUTE IMMEDIATE
   'GRANT SELECT ON DDL_USER.' || v.view_name || ' TO DATA_USER';
 END LOOP;
END;
/

Grant access to the sequences owned by ddl_user:

SQL

BEGIN
 FOR s IN (
  SELECT sequence_name
  FROM all_sequences
  WHERE sequence_owner = 'DDL_USER'
 ) LOOP
  EXECUTE IMMEDIATE
   'GRANT SELECT ON DDL_USER.' || s.sequence_name || ' TO DATA_USER';
 END LOOP;
END;
/

Create synonyms for the objects.
Run the following SQL as data_user.

Create synonyms for tables:

SQL

BEGIN
  FOR t IN (
    SELECT table_name
    FROM all_tables
    WHERE owner = 'DDL_USER'
  ) LOOP
    EXECUTE IMMEDIATE
      'CREATE OR REPLACE SYNONYM ' || t.table_name ||
      ' FOR DDL_USER.' || t.table_name;
  END LOOP;
END;
/

Create synonyms for views:

SQL

BEGIN
 FOR v IN (
  SELECT view_name
  FROM all_views
  WHERE owner = 'DDL_USER'
 ) LOOP
  EXECUTE IMMEDIATE
   'CREATE OR REPLACE SYNONYM ' || v.view_name ||
   ' FOR DDL_USER.' || v.view_name;
 END LOOP;
END;
/

Create synonyms for sequences:

SQL

BEGIN
 FOR s IN (
  SELECT sequence_name
  FROM all_sequences
  WHERE sequence_owner = 'DDL_USER'
 ) LOOP
  EXECUTE IMMEDIATE
   'CREATE OR REPLACE SYNONYM ' || s.sequence_name ||
   ' FOR DDL_USER.' || s.sequence_name;
 END LOOP;
END;
/

After this has been done, create a second datasource in the UMS Administrator for data_user. Activate this datasource for normal UMS runtime operation.

When the datasource is activated, the UMS Administrator might display the message:

Specified datasource contains no schema! Create a new one?

Select No, because the schema already exists and is accessed through grants and synonyms.

Device Naming Convention Reset Workflow

If device names must be reset to start from 1 again, the runtime user might not be able to alter the sequence TCNAMINGCOUNTER. In Oracle, the statement ALTER SEQUENCE ... does not use the created synonyms. Granting the required permission would allow data_user to alter all sequences in the database.

As a workaround, switch temporarily to ddl_user for this action.

After a UMS upgrade with ddl_user, access to newly created objects must be granted again to data_user, and the required synonyms must be created again. Repeat the grant and synonym steps above after each UMS upgrade.

PostgreSQL Role Separation

PostgreSQL is similar to Oracle in that object ownership is relevant, but schema handling is less demanding.

User	Purpose
`ddl_user`	Owns and creates the UMS database objects
`data_user`	Performs normal UMS runtime operations

Create a user for schema operations, for example ddl_user.

GRANT CONNECT, CREATE ON DATABASE rmdb12_01 TO ddl_user;
GRANT USAGE, CREATE ON SCHEMA public TO ddl_user;

→ Use this user for UMS installation and UMS update workflows.

Create a second user for normal runtime operation, for example data_user.

GRANT CONNECT ON DATABASE rmdb12_01 TO data_user;

GRANT USAGE ON SCHEMA public TO data_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO data_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO data_user;

As with Oracle, permissions for newly created tables and sequences must be granted again after a UMS update. Run the following commands as ddl_user after the update:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO data_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO data_user;

Device Naming Convention Reset Workflow

The data_user is not allowed to change the sequence used by the Device naming conventions. To perform this action, run UMS temporarily with ddl_user.