Create Databases and Database Users

This section describes database structures and users to be created before installing 3DNotification.

Oracle and SQL Server databases require the same kind of configuration.

On Oracle, database names must not begin with a number and must not exceed 8 characters (as specified in the Oracle documentation).

When creating the databases, you should specify the CHARACTER SET of the database to be able to support non-roman alphabet characters. The Oracle database character set must be set to AL32UTF8.

For SQL Server, you must also select a database collation that is case-insensitive. For instance, you can use the following collation database settings: 

CREATE DATABASE database_name COLLATE Latin1_General_100_CI_AS;
Note: Do not create database names identical to those used by other 3DEXPERIENCE platform services.

Refer to Microsoft documentation for connecting with encryption to SQL Server.

Make sure the SQL Server computer has a certificate provisioned and imported.

  1. Create a database.

    For example: x3dnotification.

  2. Create the following database users.

    For SQL Server, the names of the users used in the following section are the default values in the installer: x3ds and x3ds_admin which are their default names in the installer.

    Here is an SQL Server sample script:

    USE master;
-- Create database
CREATE DATABASE x3dnotification COLLATE Latin1_General_100_CI_AS;
-- Minimizing deadlock potential
ALTER DATABASE x3dnotification SET READ_COMMITTED_SNAPSHOT ON;
-- Create logins
CREATE LOGIN x3ds WITH PASSWORD = 'Passport#1';
CREATE LOGIN x3ds_admin WITH PASSWORD = 'Passport#1';
USE x3dnotification;
-- Create users on correct DATABASE (after use) and with correct schema
CREATE USER x3ds FOR LOGIN x3ds WITH DEFAULT_SCHEMA = x3dnotification;
CREATE USER x3ds_admin FOR LOGIN x3ds_admin WITH DEFAULT_SCHEMA = x3dnotification;
-- Create schema
CREATE SCHEMA x3dnotification AUTHORIZATION x3ds_admin;
-- Grant access
GRANT CREATE TABLE, ALTER, SELECT, INSERT, UPDATE, DELETE ON DATABASE::x3dnotification TO x3ds_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::x3dnotification TO x3ds;

    Oracle Database requires an Oracle instant client on Windows and Linux, depending on the Oracle server version installed (for example, Oracle instant client 19.6).

    During the preparation of the installation:

    • On Linux: install the oracle client (if not already done), for example oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64.rpm: take the RPM depending on the Oracle version installed (same version)
    • On Windows: install the oracle client (if not already done); before installation, check if the PATH environment variable is set with the path or oracle client.

    On Oracle, create for example a database administrator user, for example User='x3dnotif', with the appropriate permissions on DBNotification.*, to be used by the 3DNotification service.

    For Oracle, the recommendation for an installation is to use a single database user for installation of 3DNotification (the user must have database administrator user privileges).

    The permissions on Oracle are:

    • Read tables and rows (SELECT)
    • Update rows (UPDATE)
    • Delete rows (DELETE)
    • Insert rows (INSERT)
    • Alter tables (ALTER TABLE)
    • Create tables (CREATE TABLE)
    • Delete tables (DROP TABLE)
    • Create indexes and constraints.

    Here is an Oracle sample script:

    create user x3dnotif identified by x3dnotif;
grant CREATE SEQUENCE to x3dnotif;
grant CREATE SESSION to x3dnotif;
grant CREATE TABLE to x3dnotif;
grant CREATE VIEW to x3dnotif;
grant CREATE PROCEDURE to x3dnotif;
grant CREATE TRIGGER to x3dnotif;
CREATE SMALLFILE TABLESPACE "x3dnotif" LOGGING DATAFILE 'E:\app\install\oradata\X3DNOTIF\x3dnotification01.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
ALTER USER x3dnotif default tablespace "x3dnotif";
ALTER USER x3dnotif QUOTA UNLIMITED ON "x3dnotif";
GRANT UNLIMITED TABLESPACE TO x3dnotif;