Create Databases and Database Users

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

During the installation of the 3DPassport, the installer only checks that connection to the database can be performed with the inputs provided by the user. There is no schema initialization or table creation. It will be during the first launch of 3DPassport that the application will initialize itself in two steps:

  • it will read information about the current level of database schema to perform queries on the database to have a database structure that is coherent with its code level. If you experience problems during this first step, all the logs you need will be in the app server log files.
  • When the first step is done, 3DPassport calls itself (without using the network though, so no need for a reverse proxy) to initialize its data via calls to its own API. For example, when it will create the repositories, the users, ... as of the beginning of this step, all logs will be in the database. During this step, TomEE is listening for requests but the 3DPassport blocks them until all calls have been performed. Some requests are allowed though so you can for example connect to the admin console and check the logs to debug any problem that might have occurred. All these steps rely on a set of scripts whose execution is ordered and that order depends on their name. Once they have been executed completely, the 3DPassport will update its current version so they will no longer be executed.

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

    You require two databases. The names of the databases used in the following section are the default values in the installer, passdb and passtkdb:

    • passdb: this database stores most of the 3DPassport data and configuration; it hosts the tables used by 3DPassport to perform all operations not related to SSO. This database contains the users' information, service and field description, and trusted certificates.
    • passtkdb: this database hosts the tables used by 3DPassport to hold data related to the CAS protocol (like the tokens exchanged between the 3DPassport and client applications, services allowed to use the 3DPassport, …).

    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.

    Oracle and SQL Server databases require the same kind of configuration. When the 3DPassport application is launched for the first time, a series of scripts is executed and all the tables that 3DPassport requires are created in the Oracle and SQL Server databases. These tables have the same name and the same structure in all databases.

  2. Create the following database users.

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

    For Oracle, the names of the users used in the following section are the default values in the installer: x3dpassadmin and x3dpasstokens.

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

    For the passdb database:

    • one database applicative user with read/write access, for example x3dpass; this is a general database account used by the application for all users connected to 3DPassport.

      The permissions on SQL Server are:

      • Read tables and rows (SELECT)
      • Insert rows (INSERT)
      • Update rows (UPDATE)
      • Delete rows (DELETE)
      • Alter table (ALTER)
    • one database user with read/write access, for example x3dpassadmin; this database account is used by the application when the administrator user is connected to 3DPassport.

      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

      which are translated in the sample script by the following commands executed on the user x3dpassadmin:

      • CREATE SEQUENCE
      • CREATE SESSION
      • CREATE SYNONYM
      • CREATE TABLE

    For the passtkdb database, create one database applicative user with read/write access, for example x3dpasstokens.

    Do not use same schema for both 3DPassport application and 3DPassport tokens.

    The permissions for user x3dspasstokens are the same as for the x3dspassadmin user described above on SQL Server and ORACLE.

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

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

    Here is an Oracle sample script:

    ############## CREATE DB passdb
CREATE SMALLFILE TABLESPACE "x3dpassadmin" LOGGING DATAFILE 'F:\Oracle\oradata\RD2TEST\passdb.dbf' SIZE 10M AUTOEXTEND
ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create user x3dpassadmin identified by x3dpassadmin;
grant CREATE SEQUENCE to x3dpassadmin;
grant CREATE SESSION to x3dpassadmin;
grant CREATE SYNONYM to x3dpassadmin;
grant CREATE TABLE to x3dpassadmin;
ALTER USER x3dpassadmin default tablespace "x3dpassadmin";
ALTER USER x3dpassadmin QUOTA UNLIMITED ON "x3dpassadmin";

############## CREATE DB passtkdb
CREATE SMALLFILE TABLESPACE "x3dpasstokens" LOGGING DATAFILE 'F:\Oracle\oradata\RD2TEST\passtkdb.dbf' SIZE 10M AUTOEXTEND 
ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create user x3dpasstokens identified by x3dpasstokens;
grant CREATE SEQUENCE to x3dpasstokens;
grant CREATE SESSION to x3dpasstokens;
grant CREATE SYNONYM to x3dpasstokens;
grant CREATE TABLE to x3dpasstokens;
ALTER USER x3dpasstokens default tablespace "x3dpasstokens";
ALTER USER x3dpasstokens QUOTA UNLIMITED ON "x3dpasstokens";

    The quota values specified in the script are for illustration purposes only.