Create Databases and Database Users

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

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 an Oracle database and database users.

    For example: X3DCOMNT.

  2. Create an Oracle database administrator user, for example User='x3dcomment', with the appropriate permissions on X3DCOMNT.*, to be used by the 3DComment service.

    For Oracle, the recommendation for an installation is to use a single database user for installation of 3DComment (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 x3dcomment identified by x3dcomment;
grant CREATE SEQUENCE to x3dcomment;
grant CREATE SESSION to x3dcomment;
grant CREATE TABLE to x3dcomment;
grant CREATE VIEW to x3dcomment;
grant CREATE PROCEDURE to x3dcomment;
grant CREATE TRIGGER to x3dcomment;
CREATE SMALLFILE TABLESPACE "x3dcomment" LOGGING DATAFILE '/home/data/oracle/oradata/DBNAME/x3dcomment.dbf'
SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
ALTER USER x3dcomment default tablespace "x3dcomment";
ALTER USER x3dcomment QUOTA UNLIMITED ON "x3dcomment";
GRANT UNLIMITED TABLESPACE TO x3dcomment;

  3. Create a SQL Server database.

    For example: x3dcomment.

    On SQL Server 2017, the database compatibility level must be set to 110, 130 or 140 (recommended). When set to 130 or 140, the LEGACY_CARDINALITY_ESTIMATION setting must be set to ON in the x3dcomment database context.

    On SQL Server, the default compatibility level should be kept.

  4. Create the following SQL Server database users:
    1. Create an application user, for example User='x3ds', with the appropriate permissions on x3dcomment.*, to be used by the 3DComment service.

      The permissions on SQL Server are:

      • Read tables and rows (SELECT)
      • Insert rows (INSERT)
      • Update rows (UPDATE)
      • Delete rows (DELETE)
      • Alter table (ALTER)

    2. Create a database administrator user, for example User='x3ds_admin', with the appropriate permissions on x3dcomment.*, to be used by the 3DComment service.

    The permissions on SQL Server are:

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

    Here is an SQL Server database and database user creation sample script: 

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

    If you have SQL Server 2017, run the following additional script:
    USE x3dcomment;
-- Enable LEGACY_CARDINALITY_ESTIMATION setting
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;