Database setup

Preconditions

These are the preconditions for creating the database schema:

• There is a database service available
• The database roles and users are created by a database administrator (DBA). Examples for Oracle are provided below.

-- =========================================================================
-- =       Create table spaces
-- =========================================================================
CREATE TABLESPACE DATA_NEVISADAPT DATAFILE SIZE 1000M AUTOEXTEND ON;
CREATE TABLESPACE INDEX_NEVISADAPT DATAFILE SIZE 500M AUTOEXTEND ON;

-- =========================================================================
-- =       Create user NEVISADAPT1
-- =========================================================================
CREATE USER nevisadapt1 IDENTIFIED BY "<REPLACE_WITH_PASSWORD>"
DEFAULT TABLESPACE DATA_NEVISADAPT
QUOTA UNLIMITED ON DATA_NEVISADAPT
QUOTA UNLIMITED ON INDEX_NEVISADAPT;

-- =========================================================================
-- =       Create user NEVISADAPT2
-- =========================================================================
CREATE USER nevisadapt2 IDENTIFIED BY "<REPLACE_WITH_PASSWORD>"
DEFAULT TABLESPACE DATA_NEVISADAPT
QUOTA 10M ON DATA_NEVISADAPT;

-- =========================================================================
-- =       Create roles
-- =========================================================================

CREATE ROLE NEVISADAPT_OWNER;
CREATE ROLE NEVISADAPT_APP_USER;

-- =========================================================================
-- =       Grant Privileges for standard use to role NEVISADAPT_OWNER
-- =========================================================================

GRANT CREATE SESSION TO NEVISADAPT_OWNER;
GRANT ALTER SESSION TO NEVISADAPT_OWNER;
GRANT CREATE TABLE TO NEVISADAPT_OWNER;
GRANT CREATE VIEW TO NEVISADAPT_OWNER;
GRANT CREATE SEQUENCE TO NEVISADAPT_OWNER;
GRANT CREATE MATERIALIZED VIEW TO NEVISADAPT_OWNER;
GRANT CREATE PROCEDURE TO NEVISADAPT_OWNER;
GRANT CREATE TRIGGER TO NEVISADAPT_OWNER;
GRANT QUERY REWRITE TO NEVISADAPT_OWNER;

-- =========================================================================
-- =       Grant Privileges for standard use to role NEVISADAPT_APP_USER
-- =========================================================================

GRANT CREATE SESSION TO NEVISADAPT_APP_USER;
GRANT ALTER SESSION TO NEVISADAPT_APP_USER;

-- =========================================================================
-- =       Grant database owner role to user NEVISADAPT1
-- =========================================================================

GRANT NEVISADAPT_OWNER TO nevisadapt1;

-- =========================================================================
-- =       Grant application user role to user NEVISADAPT2
-- =========================================================================

GRANT NEVISADAPT_APP_USER TO nevisadapt2;

Creating and maintaining the schema

After the installation of nevisAdapt, it is possible to create and maintain the database schema. For this, you can use, for example, Flyway, an open source database migration tool.

By default, nevisAdapt uses the database connection configured in the file /var/opt/nevisadapt/conf/nevisadapt.properties. Flyway can be configured with a different set of credentials (database owner) for migration purposes if needed.

[root@nevisdetect-dev rpm]# /opt/nevisadapt/flyway/bin/flyway.sh info
Flyway 4.1.2 by Boxfuse

Database: jdbc:oracle:thin:@oravml11.zh.adnovum.ch:41010:DCHNEV01 (Oracle 11.2)

+-----------+---------+-----------------------------+------+---------------------+---------+
| Category  | Version | Description                 | Type | Installed On        | State   |
+-----------+---------+-----------------------------+------+---------------------+---------+
| Versioned | 1       | nevisAdapt Schema           | SQL  | 2023-11-11 11:39:54 | Success |
| Versioned | 2       | Geolocation Extended        | SQL  | 2023-11-11 11:39:54 | Success |
| Versioned | 3       | Session Trusted             | SQL  | 2023-11-11 11:39:54 | Success |
| Versioned | 4       | Browser Data                | SQL  | 2023-11-11 11:39:54 | Success |
| Versioned | 5       | Observation Reporting       | SQL  | 2023-11-11 11:39:55 | Success |
| Versioned | 6       | User Device                 | SQL  | 2023-11-11 11:39:55 | Success |
| Versioned | 7       | Remember Me                 | SQL  | 2023-11-11 11:39:55 | Success |
| Versioned | 8       | Risk Events                 | SQL  | 2023-11-11 11:39:55 | Success |
| Versioned | 9       | Aggregate User Observations | SQL  | 2023-11-11 11:39:55 | Success |
| Versioned | 10      | Optimize Statistics         | SQL  | 2023-11-11 11:39:55 | Success |
+-----------+---------+-----------------------------+------+---------------------+---------+

Flyway supports the following commands:

Name	Description
migrate	Migrates the database.
clean	Drops all objects in the configured schemas.
info	Prints the details and status information about all the migrations.
validate	Validates the applied migrations against the ones available on the classpath.
baseline	Baselines an existing database, excluding all migrations up to and including the baseline version.
repair	Repairs the schema history table.

Supported databases

nevisAdapt supports the following databases:

• MariaDB
• Oracle
• PostgreSQL

MariaDB

If you use MariaDB, do the following:

• Set the parameter useMysqlMetadata to "true" in your JDBC connection URL. For example: jdbc:mariadb://127.0.0.1:3306/nevisadapt?useMysqlMetadata=true
• Set the attribute lower_case_table_names to "1" in the MySQL settings (my.cnf).

Oracle

If you use an Oracle database, either copy or create a link to the JDBC drivers in the /var/opt/nevisadapt/libs directory.

PostgreSQL

If you use PostgreSQL with Flyway, the Flyway schema history table name has to be in lower case.