Create Databases and Database Users

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

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 the following databases.

    You need a maximum of three databases.

    Note: At installation, a check is performed and will not allow you to use same database for all three components, so it is mandatory to create distinct database schema to avoid collisions on certain tables.

    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;

    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 x3dswym_social database context.

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

    For Oracle, the names of the databases used in the following section are the default values in the installer:

    • DBSOCIAL database name for 3DSwym Content: database name where all community-related content will be stored.
    • DBMEDIA database name for 3DSwym rich media: database name where media metadata will be stored.
    • DBWIDGET database name for the widget platform.

    For SQL Server, the names of the corresponding database schemas used in the following section are the default values in the installer:

    • x3dswym_social database schema for 3DSwym Content: database name where all community-related content will be stored.
    • x3dswym_media database schema for 3DSwym rich media: database name where media metadata will be stored.
    • x3dswym_widget database schema for the widget platform.
    Note: Do not create database names identical to those used by other 3DEXPERIENCE platform services.
    Note: Do not use the same database schema for all three components: create separate schema for each component.

  2. Create the following database users.

    For Oracle, you need a maximum of three database users, and for SQL Server, you need a maximum of two database users.

    For Oracle, the names of the users used in the following section are the default values in the installer: x3dswym, x3dswym_media and x3dswym_widget.

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

    Oracle commands to be executed are:

    • CREATE SEQUENCE to user;
    • CREATE SESSION to user;
    • CREATE TABLE to user;
    • CREATE VIEW to user;
    • CREATE PROCEDURE to user;
    • CREATE TRIGGER to user;

    Here are a set of Oracle sample scripts on for creating users for the DBSOCIAL, DBMEDIA and DBWIDGET databases: 

    ############## CREATE DB DBSOCIAL
create user x3dswym identified by x3dswym;
grant CREATE SEQUENCE to x3dswym;
grant CREATE SESSION to x3dswym;
grant CREATE TABLE to x3dswym;
grant CREATE VIEW to x3dswym;
grant CREATE PROCEDURE to x3dswym;
grant CREATE TRIGGER to x3dswym;
CREATE SMALLFILE TABLESPACE "x3dswym" LOGGING DATAFILE '/home/data/ora12/oradata/DBNAME/DBSOCIAL.dbf' 
SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
ALTER USER x3dswym default tablespace "x3dswym";
ALTER USER x3dswym QUOTA UNLIMITED ON "x3dswym";
GRANT UNLIMITED TABLESPACE TO x3dswym;

############## CREATE DB DBMEDIA
create user x3dswym_media identified by x3dswym_media;
grant CREATE SEQUENCE to x3dswym_media;
grant CREATE SESSION to x3dswym_media;
grant CREATE TABLE to x3dswym_media;
grant CREATE VIEW to x3dswym_media;
grant CREATE PROCEDURE to x3dswym_media;
grant CREATE TRIGGER to x3dswym_media;
CREATE SMALLFILE TABLESPACE "x3dswym_media" LOGGING DATAFILE '/home/data/ora12/oradata/DBNAME/DBMEDIA.dbf' 
SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
ALTER USER x3dswym_media default tablespace "x3dswym_media";
ALTER USER x3dswym_media QUOTA UNLIMITED ON "x3dswym_media";
GRANT UNLIMITED TABLESPACE TO x3dswym_media;

############## CREATE DB DBWIDGET
create user x3dswym_widget identified by x3dswym_widget;
grant CREATE SEQUENCE to x3dswym_widget;
grant CREATE SESSION to x3dswym_widget;
grant CREATE TABLE to x3dswym_widget;
grant CREATE VIEW to x3dswym_widget;
grant CREATE PROCEDURE to x3dswym_widget;
grant CREATE TRIGGER to x3dswym_widget;
CREATE SMALLFILE TABLESPACE "x3dswym_widget" LOGGING DATAFILE '/home/data/ora12/oradata/DBNAME/DBWIDGET.dbf'
SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
ALTER USER x3dswym_widget default tablespace "x3dswym_widget";
ALTER USER x3dswym_widget QUOTA UNLIMITED ON "x3dswym_widget";
GRANT UNLIMITED TABLESPACE TO x3dswym_widget;

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

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

    If you have SQL Server 2017, run the following additional script:

    USE x3dswym_social;
-- Enable LEGACY_CARDINALITY_ESTIMATION setting
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;