Skip to content

6 Database Content and Configuration

In order to create the initial content of the EMM database, your initial release of the GUI service contains files with SQL statements to create the latest version of the EMM database from scratch. You will find these files with extension sql in directory

/home/console/release/emm/<release>/WEB-INF/sql

where <release> is the placeholder for your initial EMM version. You will find the Oracle-specific SQL files in sub-directory oracle and the MariaDB-specific SQL files in sub-directory mariadb.

6.1 Database Content (Oracle)

Create a new database with SID emm in your Oracle DBMS instance using UTF-8 encoding. At first, check the file paths in SQL script emm-oracle-create-tablespaces.sql and change them as needed (default directory for data is /oracle/DATAEMM/data/). But do not change the names of the tablespaces!

Create the necessary tablespaces and user for EMM database emm as DB user sysdba with

sqlplus / as sysdba
@emm-oracle-create-tablespaces.sql
@emm-oracle-create-user.sql

Make sure that the database user used by EMM has the required rights. If the user name is agnitas, we recommend to create the user this way:

CREATE USER agnitas IDENTIFIED BY <password>;
GRANT CONNECT, RESOURCE TO agnitas;
GRANT CREATE SESSION, CREATE TABLE, CREATE TRIGGER, CREATE VIEW, CREATE SYNONYM, ALTER SESSION, UNLIMITED TABLESPACE TO agnitas;
ALTER USER agnitas DEFAULT tablespace data_default;

If you use module Oracle Text, do not forget to assign role CTXAPP to the database user used by EMM, so that EMM is able to use this module for its text search functionality.

The EMM database schema and the initial database content is located in file emm-oracle-fulldb.sql, the initial EMM messages table messages_tbl (holding all current GUI translations) is located in file emm-messages.sql and the initial permissions for EMM user groups are located in file userrights.sql. You will find these files in directory /home/console/webapps/emm/WEB-INF/sql/oracle/.

Let EST (EMM Support Tool) create the initial EMM database or log in with sqlplus as database user agnitas and execute the available SQL files in this order (the version numbers are just examples):

@emm-oracle-fulldb.sql
@emm-oracle-update-22.04.471.sql
@emm-oracle-update-22.04.815.sql
@emm-oracle-update-22.07.047.sql
@emm-oracle-update-22.07.081.sql
SET DEFINE OFF;
@emm-oracle-messages.sql
@userrights.sql
@emm-oracle-migration.sql

The four lines starting with @emm-oracle-update are placeholders for all scripts with updates of the DB schema and content.

Take care to execute the available files in the right sequence: Start with the file with the lowest numbers and end with the highest numbers available, like in the example above. File userrights.sql must be the last file to be executed!

For cross-checking, database table agn_dbversioninfo_tbl lists which SQL files of type emm-oracle-update have been successfully executed.

If you do not want to execute the update files one by one, we provide a helper script that runs them in the correct sequence. In this case you only have to execute

@emm-oracle-fulldb.sql

within sqlplus to set up the initial DB schema and content. You can start the update script afterwards from the command line with

emm-oracle-execute-missing-updates.sh dbcfg

as user console. The helper script uses the parameters from file dbcfg in directory /opt/agnitas.com/etc/ to access your database.

6.2 Database Content (MariaDB)

Let EST (EMM Support Tool) create the initial EMM database or create it manually with

mysql -u root -p
CREATE DATABASE emm CHARACTER SET utf8 COLLATE utf8_unicode_ci;

and create the EMM database user agnitas with SQL statement

CREATE USER 'agnitas'@'localhost' IDENTIFIED BY '<password>';
GRANT ALL PRIVILEGES ON emm.* TO 'agnitas'@'localhost';
GRANT SELECT ON mysql.proc TO 'agnitas'@'localhost';
GRANT RELOAD ON *.* TO 'agnitas'@'localhost';
GRANT SUPER ON *.* TO 'agnitas'@'localhost';
FLUSH PRIVILEGES;

Please replace placeholder <password> with a strong password.

You need the global permissions SELECT ON mysql.proc to read Stored Procedures, RELOAD to be able to flush the privileges and SUPER to be able to create the database triggers provided by the SQL files mentioned below. If you are not able to get permission SUPER, as a workaround you can set

log-bin-trust-function-creators = 1

in section [mysqld] of your database configuration file my.cnf.

Load the initial EMM database schema with

  • the EMM default content (file emm-mariadb-fulldb-basic.sql and emm-mariadb-fulldb-extended.sql)
  • the update files for the DB schema and content
  • the initial EMM messages table messages_tbl (file emm-messages.sql, holding all GUI translations)
  • and the initial permissions for EMM user groups (file userrights.sql)

into the database in this order:

mysql -u agnitas -p<pw> emm < emm-mariadb-fulldb-basic.sql
mysql -u agnitas -p<pw> emm < emm-mariadb-fulldb-extended.sql
mysql -u agnitas -p<pw> emm < emm-mariadb-update-13.7.471.sql
mysql -u agnitas -p<pw> emm < emm-mariadb-update-13.7.815.sql
mysql -u agnitas -p<pw> emm < emm-mariadb-update-13.10.047.sql
mysql -u agnitas -p<pw> emm < emm-mariadb-update-13.10.081.sql
mysql -u agnitas -p<pw> emm < emm-messages.sql
mysql -u agnitas -p<pw> emm < userrights.sql
mysql -u agnitas -p<pw> emm < emm-mariadb-migration.sql

where <pw> is the placeholder for the password of your database user agnitas.

The four lines starting with @emm-mariadb-update are placeholders for all scripts with updates of the DB schema and content.

Take care to execute the available files in the right sequence: Start with the file with the lowest numbers and end with the highest numbers available, like in the example above. File userrights.sql must be the last file to be executed!

For cross-checking, database table agn_dbversioninfo_tbl lists which SQL files of type emm-mariadb-update have been successfully executed.

If you do not want to execute the update files one by one, we provide a helper script that runs them in the correct sequence. In this case you only have to execute

mysql -u root -p emm < emm-mariadb-fulldb.sql

to setup the initial DB schema and content, and you can start the update script afterwards with

emm-mariadb-execute-missing-updates.sh dbcfg

as user console. The helper script uses the parameters from file dbcfg in directory /opt/agnitas.com/etc/ to access your database.

6.3 Mailer Server Configuration

For every mail server enter this statement and replace <mailer> with the hostname of the individual server.

INSERT INTO serverset_tbl (set_id, mailer) VALUES (0, '<mailer>');

Some more SQL for Oracle:

INSERT INTO serverprop_tbl (mailer_id, mailer, mvar, mval) VALUES (serverprop_tbl_seq.nextval, '<mailer>', 'active', 'true');
INSERT INTO serverprop_tbl (mailer_id, mailer, mvar, mval) VALUES (serverprop_tbl_seq.nextval, '<mailer>', 'fetchlog', 'true');
INSERT INTO serverprop_tbl (mailer_id, mailer, mvar, mval) VALUES (serverprop_tbl_seq.nextval, '<mailer>', 'user', 'mailout');

Some more SQL for MariaDB:

INSERT INTO serverprop_tbl (mailer, mvar, mval) VALUES ('<mailer>', 'active', 'true');
INSERT INTO serverprop_tbl (mailer, mvar, mval) VALUES ('<mailer>', 'fetchlog', 'true');
INSERT INTO serverprop_tbl (mailer, mvar, mval) VALUES ('<mailer>', 'user', 'mailout');

6.4 Filter Server Configuration

For every filter server enter this statement and replace <filter> with the hostname of the individual server:

INSERT INTO config_tbl (class, name, value) VALUES ('fetch', 'mailloop', 'mailloop@<filter>');

Some more SQL for Oracle:

INSERT INTO serverprop_tbl (mailer_id, mailer, mvar, mval) VALUES (serverprop_tbl_seq.nextval, '<filter>', 'fetchlog', 'true');
INSERT INTO serverprop_tbl (mailer_id, mailer, mvar, mval) VALUES (serverprop_tbl_seq.nextval, '<filter>', 'user', 'mailloop');

Some more SQL for MariaDB:

INSERT INTO serverprop_tbl (mailer, mvar, mval) VALUES ('<filter>', 'fetchlog', 'true');
INSERT INTO serverprop_tbl (mailer, mvar, mval) VALUES ('<filter>', 'user', 'mailloop');

6.5 EMM Server (Frontend) and Redirect Server Configuration

Enter this statement for every frontend and redirect server and replace <server> with the hostname of the individual server and replace <user> with user console or rdir:

INSERT INTO config_tbl (class, name, value) VALUES ('pickup', 'rdir', '<user>@<server>');

To set the redirect domain name include the protocol HTTP or HTTPS (strongly recommended) and use SQL statement

UPDATE company_tbl SET rdir_domain = 'https://<redirect domain name>';

To set the mailloop domain name (not the filter hostname) do not include the protocol and use SQL statement

UPDATE company_tbl SET mailloop_domain = '<mailloop domain name>';

6.6 Password for Superuser of EMM

The name of the superuser for EMM is emm-master (admin_id = 1). To be able to log into the GUI of EMM with this user, you have to set up an initial password first. For that reason, we provide either EST (EMM Support Tool) or shell script emm-create-initial-password.sh, which you can find on the server running the GUI service in directory /home/console/webapps/emm/WEB-INF/sql/mariadb/ or /home/console/webapps/emm/WEB-INF/sql/oracle/.

When executed, script emm-create-initial-password.sh makes up a new initial EMM password and create a hash based on the salt file emm.salt from directory /home/console/conf/keys/ (or symbolic link /home/console/tomcat/conf/keys). As a result, your new password and its hash will be displayed at the command line, and you will get a new SQL file named initialPassword.sql. This file contains an SQL statement like

UPDATE admin_tbl SET secure_password_hash = '<pw_hash>', pwdchange_date = CURRENT_TIMESTAMP, is_one_time_pass = 1 WHERE admin_id = 1;

As soon as you execute this statement, your new password is set. Since this is only a one-time password, EMM will ask you to change it after you have logged in to the GUI of EMM the first time.