Create Databases and Database Users

This section describes database structures and users creation prior to 3DDashboard installation.

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: dashdb.

  2. Create the following database users.

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

    The following database users are required:

    • an application user, for example: 
      User='x3ddash'

      with the appropriate permissions on dashdb.*, to be used by the 3DDashboard service.

      The permissions on SQL Server are:

      • Read tables and rows (SELECT)
      • Insert rows (INSERT)
      • Update rows (UPDATE)
      • Delete rows (DELETE)
      • Alter table (ALTER)
    • a database administrator user, for example: 

      User='x3ddashadmin'

      with the appropriate permissions on dashdb.*, to be used by the 3DDashboard 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.

      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 SQL Server database and database user creation sample script: 

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

    Here is an Oracle sample script: 

    CREATE USER x3ddashadmin IDENTIFIED BY x3ddashadmin;
GRANT CREATE SESSION TO x3ddashadmin;
GRANT RESOURCE TO x3ddashadmin;
CREATE SMALLFILE TABLESPACE "x3ddashadmin" LOGGING DATAFILE '/tmp/x3ddashadmin.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
ALTER USER x3ddashadmin default tablespace "x3ddashadmin";
ALTER USER x3ddashadmin QUOTA UNLIMITED ON "x3ddashadmin";
GRANT UNLIMITED TABLESPACE TO x3ddashadmin;