Configuring the Database
This section describes the process to configure the database server.
The IDPV server currently supports the following databases (MySQL, MariaDB, MSSQL, PostgreSQL, ORACLE).
The following section provides the details on configuration of these databases.
This table lists the IDPV Server parameters that must be configured in the appsettings.yml
file available under the /var/thales/config
directory.
Parameters | Description | Example |
---|---|---|
DatabaseProvider |
Database Provider Name Type: Mandatory (Case Insensitive) |
Select one of the following: • MySQL • MariaDB • MSSQL • PostgreSQL • ORACLE |
ConnectionString |
• Database Connection String • Establishes database connection Type: Mandatory |
Example for Secured MYSQL:ConnectionString: server=10.100.99.11;port=3306; user=dbuser;password=strongdbpwd; database=IDPrimeVirtualServer; SslMode=<VerifyFull>;SslCa=<ca.pem>; SslCert=<client-cert.pem>; SslKey=<client-key.pem>; Example for MySQL/MariaDB: ConnectionString: server=10.100.99.11;port=3306; user=dbuser;password=strongdbpwd; database=IDPrimeVirtualServer; Example for MSSQL: ConnectionString: server=10.100.99.11;port=1433\\SQLEXPRESS; Database=IDPrimeVirtualServer; User Id=dbuser;Password=strongdbpwd; TrustServerCertificate=true;Encrypt=false; Note: The CA certificate should be installed on the machine where the MSSQL is located. If CA certificate is unavailable, then disable the security settings of MSSQL server by providing the following attributes: TrustServerCertificate=true;Encrypt=false; Example for PostgreSQL: ConnectionString: server=10.100.99.11;port=5432; User Id=dbuser;Password=strongdbpwd; Database=IDPrimeVirtualServer; Example for ORACLE: ConnectionString:``Data Source=10.100.99.11:1521/<database_name>; User Id``=dbuser; Password=strongdbpwd; Note: In the MSSQL and PSQL connection string, there is one space between User and Id. For example: User Id |
To ensure the successful execution of the IDPV Server, it is essential to configure the user privileges within the database accordingly. Hence, we have provided below a comprehensive user privileges configuration, along with detailed instructions for enabling the TLS service for MySQL database.
Setting up and Accessing MySQL Server
Perform the following steps to setup and access MySQL server:
The validated version of MySQL Server with IDPrime Virtual Server is 8.0.17. Use MySQL Server version greater than 5.7.26 or 8.0.15.
-
Ensure the Database Name is
IDPrimeVirtualServer
in yourappsettings.yml
file under/var/thales/config/
. -
Perform the following steps on the MySQL server. Update values in
for the required parameters. When values updated, remove the angle brackets. idprimevirtualuser
is the same user name configured in theappsettings.yml
file under DB connection string.-
Create a user for your SafeNet IDPrime Virtual Server Docker container using the following command:
mysql> CREATE USER ‘idprimevirtualuser’@’<docker-machine-IP>’ IDENTIFIED BY ‘<DB-Password>’;
-
Grant permissions to the user for accessing the IDPrimeVirtualServer database using the following command. This will grant the user permissions.
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, REFERENCES, INDEX, ALTER ON IDPrimeVirtualServer.* TO ‘idprimevirtualuser’@’<docker-machine-IP>’
The Docker Container IP (
<docker-machine-IP>
) is your current server IP. You can also use the fully qualified host (server) name. -
Save the changes by typing
FLUSH PRIVILEGES
command from the mysql prompt:mysql> FLUSH PRIVILEGES;
-
Configuring MySQL to Enable TLS (Optional)
Perform the following steps to configure MYSQL to accept TLS connections:
-
Generate the SSL Certificates for client and server. For detailed instructions, refer to https://dev.mysql.com/doc/refman/8.0/en/creating-ssl-files-using-openssl.html.
-
Configure MYSQL to use Encrypted Connections. For detailed instructions, refer to https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/using-encrypted-connections.html.
-
Use MYSQL Configuration .ini file to set the following variables under the [mysqld] and [client] sections. Update the Certificate Path(s) according to the location of these files:
To view properties of the require_secure_transport variable, refer to https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_require_secure_transport.
-
Verify if the server is SSL-enabled. You can query SHOW VARIABLES LIKE ‘%ssl%’ to confirm that the server is configured in the secure mode.
For detailed instructions, refer to Configuring the Server.
-
Test the server connection with a MYSQL client that provides the required certificates.
Setting up and Accessing MariaDB Server
Perform the following steps to setup and access MariaDB server:
The validated version of MariaDB with IDPrime Virtual is 10.3.
The steps for setting up and accessing MariaDB are same as that of MySQL but the commands (database queries) may differ.
In DB connection string, idprimevirtualuser
username is the same as configured in appsettings.yml
file.
-
Create a user for your SafeNet IDPrime Virtual Server Docker container using the following command:
mysql> CREATE USER ‘idprimevirtualuser’@’<docker-machine-IP>’ IDENTIFIED BY ‘<DB-Password>’;
-
Grant permissions to the user for accessing the IDPrimeVirtualServer database using the following command. This will grant the permissions to the user.
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, REFERENCES, INDEX, ALTER ON IDPrimeVirtualServer.* TO ‘idprimevirtualuser’@’<docker-machine-IP>’
The Docker Container IP
(<docker-machine-IP>)
is your current server IP. You can also use the fully qualified host (server) name. -
Save the changes by typing FLUSH PRIVILEGES command from the mysql prompt:
mysql> FLUSH PRIVILEGES;
Setting up and Accessing MSSQL Server
Perform the following steps to setup and access MSSQL server:
-
Install the MSSQL Server, refer to SQL Server Installation Guide.
The validated version of MSSQL with IDPrime Virtual is 14.0.3048.4.
-
After the MSSQL Server is installed, perform the following steps to set the database configuration and user privileges:
-
Create a blank database with IDPrimeVirtualServer as its name (without tables). This name can be any name but must be used accordingly in the connectionString parameter given in the table above.
-
Create a user with the following permissions on the created database:
-
db_ddladmin (this role is only required to create or update the database or schema)
-
db_datareader
-
db_datawriter
These roles are used by the IDPrime Virtual Server to communicate with the database server.
Once the IDPrime Virtual Server is setup (tables created), the db_ddladmin role can be removed from the user roles. Removal of this role enhances the security of the database.
This removed role can be required again, if a migration or upgrade is performed.
-
-
Setting up and Accessing PostgreSQL Server
Perform the following steps to setup and access PostgreSQL server:
-
Install the PostgreSQL server, refer to PostgreSQL Installation Guide
The validated version of PostgreSQL with IDPrime Virtual is 14.2.
-
After PostgreSQL server is installed, ensure the database user mentioned in connection string should have permissions to create schema and perform CRUD operations.
Setting up and Accessing Oracle DB Server
Perform the following steps to set up and access Oracle server:
-
Install the Oracle DB Server, refer to Oracle Server Installation Guide Oracle Server Installation Guide
The validated version of Oracle Database with IDPrime Virtual is Oracle Database Enterprise/ Express Edition 21.3.0.0.0.
-
After the Oracle DB Server Express Edition 21.3.0.0.0 is installed, perform the following steps to set the database configuration and the user privileges:
Update values in
for the required parameters. When the values are updated, remove the angle brackets. For example: idprimevirtualuser
is the same user name configured in theappsettings.yml
file under DB connection string.-
Connect to Oracle DB server as sysdba.
Enter user-name: / as sysdba
-
Create a blank database with IDPrimeVirtualServer as its name (without tables). This name can be any name but must be used accordingly in the
connectionString
parameter. -
Connect to the system account.
connect sys/oracle@<hostname>:<port>/<DatabaseName> as sysdba; For example: connect sys/oracle@localhost:1521/IDPrimeVirtualServer as sysdba;
-
Create a user with the following permissions on the created database:
Create user <idprimevirtualuser> identified by <password> default tablespace users quota unlimited on users;
-
Run the following command to grant permissions to the user for accessing the database:
GRANT connect to <idprimevirtualuser>; GRANT all privileges to < idprimevirtualuser >;
-
In case of Docker deployments, specific credentials are stored in the form of plain text in the appsettings.yml file. Therefore, access to the system is important and must be monitored.