Version: 2024.1.1

Restricted database access

Overview​

New way of deployment on systems, where changing the database objects is restricted. It is required to have 3 inPoint environments:

Test system (T)​
  • System with all required database rights described in this section.
  • Contains copy of the production database.
  • System for preparing the deployment for the Q and P systems.
  • Each DDL (Data Definition Language) command is logged.
  • Each DML (Data Manipulation Language) command on system related data is logged.
  • DML (Data Manipulation Language) commands on the user related data executed by the inPoint.Wizard/inPoint.Admin are logged too , but only the first X changes (by default it's the first 10 changes).
    User related data can be for example:
    • folder or document in the global site
    • inPoint users
    • inPoint groups
    • etc.
Quality system (Q)​
  • Restricted database access.
  • Contains copy of the production database.
  • Executing any DDL (Data Definition Language) command by the inPoint.Wizard/inPoint.Admin is prohibited.
  • Executing any DML (Data Manipulation Language) command on system related data by the inPoint.Wizard/inPoint.Admin is prohibited.
  • Executing any DML (Data Manipulation Language) command on the user related data by the inPoint.Wizard/inPoint.Admin is allowed. The first 10 commands of this type are reported in the script package file during the export and can be reviewed by the admin.
Production system (P)​
  • Restricted database access.
  • Executing any DDL (Data Definition Language) command by the inPoint.Wizard/inPoint.Admin is prohibited.
  • Executing any DML (Data Manipulation Language) command on system related data by the inPoint.Wizard/inPoint.Admin is prohibited.
  • Executing any DML (Data Manipulation Language) command on the user related data by the inPoint.Wizard/inPoint.Admin is allowed. The first 10 commands of this type are reported in the script package file during the export and can be reviewed by the admin.

Database script execution modes​

In the inPoint.Wizard it's possible to define the database script execution mode, which defines whether to execute any DDL (Data Definition Language)/DML (Data Manipulation Language) commands during the deployment.

Possible database scripts execution modes:

Ausführen​
  • Default settings, with no logging.
Ausführen und protokollieren​
  • Each DDL (Data Definition Language) command is logged.
  • Each DML (Data Manipulation Language) command on system related data is logged.
  • Logging the first 10 DML (Data Manipulation Language) command on the user related data.
  • Used only on the T system without any database restrictions.
Nicht durchführen​
  • Setup is deploying the assemblies.
  • Executing any DDL (Data Definition Language) command is prohibited.
  • Executing any DML (Data Manipulation Language) command on system related data is prohibited.
  • Executing any DML (Data Manipulation Language) command on the user related data is allowed.
  • Used only on the Q and P system with database restrictions.

It is allowed to change the database script execution mode in the inPoint.Wizard only in fresh install in section Änderungen (DDL).

inpointwizard-benutzerdefiniert

inpointwizard-benutzerdefiniert

Process flow diagram​

The process flow diagram in restricted database access:

processflowdiagram_restrictedaccess

Deployment Life Cycle in restricted database access​

Phases of the Deployment Life Cycle (DLC) in restricted database access:

deployment_life_cycle

Prerequisites​

Prerequisites on the T system​

  • Before the Phase 1 of the DLC the T system already contains a fresh or running inPoint environment.

Prerequisites on the Q system​

  • Before the Phase 1 of the DLC the Q system is empty or contains a running inPoint environment, where the database script execution mode is set to "Nicht durchführen".

Prerequisites on the P system​

  • Before the Phase 1 of the DLC the P system is empty or contains a running inPoint environment, where the database script execution mode is set to "Nicht durchführen".

  • Before the Phase 1 of the DLC all settings are configured in "Admin" Window of the inPoint.Client connecting to the the production system environment (source system), including:

    • Settings in the Menus tab
    • Settings in the General Settings tab
    • Settings in the E-mail templates tab
    • Settings in the Templates tab

  • After starting the Phase 1 of the DLC it's prohibited to create/change/delete any inPoint system objects (for example: creating Tenant/GSR/Flat archive unit/etc... ) on the production system till the last phase Phase 10 of the DLC is completed. If this condition cannot be met, then the whole DLC process has to be cancelled and rolled back.

Phase 1. Cloning the production database to T and Q systems​

The very first step of the DLC is to clone the production database to the T and Q systems.

  • The structure/declaration/body of the database objects must be the same between the P, Q and T systems.
  • The content of the inPoint system objects must be the same between the P, Q and T systems.
  • The clone must be applied on all databases used by inPoint.

Phase 2. Executing the wizard or creating/modifying objects in inPoint.Admin on the T system​

When executing the inPoint.Wizard on fresh T system, choose the database script execution mode: "Ausführen und protokollieren"!

phases2_setup_update_saveddl

Once the installation with inPoint.Wizard was processed, executing the inPoint.Wizard in another DLC is optional, necessary only if the inPoint server upgrade is required.

Before doing any change on the T system, the following server settings needs to be modified in the "Admin" Window of the inPoint.Client connecting to the T system environment:

  • Fulltext related:
    • Elasticsearch User
    • Elasticsearch Password
    • Elasticsearch URL
  • Workflow related:
    • Activate Workflow
    • HSService url
    • Server url
    • PortalConnector url
    • Portal
  • Purge Request:
    • Purge Request Folder
    • Confirmation Workflow
    • Audit Folder
  • Others:
    • Show/hide Global Sites
    • Show/hide My Site
    • Show/hide Shared Sites
    • Show/hide Recycle Bin
    • Show/hide Mailbox
    • Report portal url
    • ArchiveDialogDirectItemUri
    • Report ribbongroup
    • Default selected Search Form
    • Folder query hints
    • Document query hints

These settings are excluded from logging and are not transferred to the P and Q systems.

After adjusting the server settings mentioned above, it's allowed to:

  • manipulate with the inPoint system objects using the inPoint.Admin including:
    • Tenants
    • GSRs
    • Flat archive units
    • Jobs
    • etc...
  • manipulate with the inPoint objects using the inPoint.Client/inPoint.Web including:
    • Documents
    • Folders
    • Workflows
    • etc...

IMPORTANT
The export executed later in Phase 4 of the DLC will fail if:

  • there is any failed script occuring in the current phase
  • there is an inconsistent state in the logging tables
    • not committed scripts
    • not committed setup
    • etc...

In this case the whole DLC process has to be cancelled and rolled back in order to get back to a consistent state.

Phase 3. Testing on the T system​

Before exporting the changes implemented on the T system, it's important to test the changes.

IMPORTANT
The export executed later in Phase 4 of the DLC will fail if:

  • there is any failed script occuring in the current phase
  • there is an inconsistent state in the logging tables
    • not committed scripts
    • etc...

In this case the whole DLC process has to be cancelled and rolled back in order to get back to a consistent state.

Phase 4. Export from the T system (creating script packages)​

If the test succeeds, it's allowed to export the changes into the script package. The export is done by the ScriptPackageManager tool ("ScriptPackageManagerTool.exe") , which is included in the "inPoint_Tools_<current release>_Release.zip" package.

Copy this tool to any location, and execute the export with the following command:

TIP: Use "^" at the end of a line, allows to continue the command in the next line to make it more readable!

ScriptPackageManagerTool.exe /Export            ^
  /ArchiveConfig "C:\Program Files (x86)\HS Europe\inPoint.Archive\inPoint.Archive.config"    ^
  /HybridStoreConfig "C:\Program Files (x86)\HS Europe\inPoint.HybridStore\appSettings.json"                    ^
  /PackageRootPath "C:\ScriptPackages"          ^
  /PackageName "MyFirstScriptPackage"

Response

Package created (PackageName: 'MyFirstScriptPackage', PackageRootPath: 'C:\ScriptPackages').
Elapsed Time is 00:00:01.894

For the help of all commands execute

"ScriptPackageManagerTool.exe /?"

All settings are explained in this documentation.

During one export as many script packages are created as many databases are used by inPoint.

For example:
If inPoint is using separate database for HybridStore and the inPoint server then 2 script packages will be created with the following naming convention:
"<package id>_<package name>_(<database hosting server name>_<database name>_<schema name>).zip"

, where:

Package id​

<package id>
Is a unique sequence number generated by the system.

Package name​

<package name>
Name of the package entered in the command line arguments for the export.

Database hosting server name​

<database hosting server name>
Specifies the name of the host server, where the database is running.

Database name​

<database name>
Specifies the name of the database. In case of MSSQL it's the database name, in case of Oracle it's the service name.

Schema name​

<schema name>
Name of the database schema, used by inPoint or HybridStore.

In this phase of the DLC it is possible to execute the export multiple times. Each export contains the delta from the previous export. If there is nothing to export the script package is not created.

Phase 5. Importing the script packages to the Q system​

After the Phase 4 of the DLC is finished, the database changes done on the T system needs to be imported to the Q system using the ScriptPackageManagerTool.

Before import​

  • Stop each inPoint service, if the inPoint is already installed.
  • Stop the Pam.Web application pool, if the inPoint is already installed.
  • Copy the created script package file/files in Phase 4 to the Q system.
  • The ScriptPackageManagerTool is not transferring the config file changes, therefore it's necessary to:
    • in case of fresh install, copy the "inPoint.Archive.config" and the "inPoint.HybridStore\appSettings.json" files from the the T system to the Q system, by modifying the database connection string accordingly in both files.
    • in case of update, override the "inPoint.Archive.config" and the "inPoint.HybridStore\appSettings.json" files on the Q system with the config files from the T system, by modifying the database connection string accordingly in both files.
  • Validate if the user used for inPoint default schema has the following system privileges in Oracle.
  • Validate if the user used as a database connection user in the ScriptPackageManagerTool has the following system privileges in Oracle.
  • In case we have a different connection user with limited access, which connects to the inPoint default schema, execute the following script in the inPoint default schema for all inPoint/custom views containing tables/views from Oracle's sys or other schemas:
REVOKE SELECT ON <inPoint/custom view> FROM <connection user>;
REVOKE UPDATE ON <inPoint/custom view> FROM <connection user>;
REVOKE INSERT ON <inPoint/custom view> FROM <connection user>;
REVOKE DELETE ON <inPoint/custom view> FROM <connection user>;

inPoint contains only one view containing views from Oracle's sys schema:

REVOKE SELECT ON PAM_USERTABLES FROM <connection user>;
REVOKE UPDATE ON PAM_USERTABLES FROM <connection user>;
REVOKE INSERT ON PAM_USERTABLES FROM <connection user>;
REVOKE DELETE ON PAM_USERTABLES FROM <connection user>;

This is required to avoid the "ORA-01720: Grant Option Does Not Exist" exception when recreating the views containing tables/views from different schemas where the connection user doesn't have access.

According to Oracle (detailed description see here):

"The code was changed in 11.2.0.4 so that create view behavior is similar to grant. If you try to make a GRANT on an existing view and the view owner does not have the grant option
, then ORA-1720 is the expected result (even in 11.2.0.3). In 11.2.0.4, we have simply made CREATE VIEW consistent with the GRANT behavior, i.e. if an incompatible grant exists
, then the new view definition must not be allowed (even with FORCE). In other words, we do not allow incompatible grants to coexist with the view definition and so an error must be thrown. 
The behavior in release 11.2.0.3 (and earlier) was incorrect; the new behavior in 11.2.0.4 is intentional and correct."

To correct:

Remove all grants on the view before REPLACING the view. This will ensure that no incompatible grants exist.
  • Create a global environment variable with the following command in administrative command prompt, which sets the database script execution mode to: "Nicht durchführen":
setx inPoint_DatabaseRestriction "DbNoDdl" /M

Import​

The import is done by the ScriptPackageManager tool ("ScriptPackageManagerTool.exe") , which is included in the "inPoint_Tools_<current release>_Release.zip" package.

Copy this tool to any location, and execute the import for each script package file created on the T system with the following command:

TIP: Use "^" at the end of a line, allows to continue the command in the next line to make it more readable!

ScriptPackageManagerTool.exe /Import            ^
  /ArchiveConfig "C:\Program Files (x86)\HS Europe\inPoint.Archive\inPoint.Archive.config"    ^
  /HybridStoreConfig "C:\Program Files (x86)\HS Europe\inPoint.HybridStore\appSettings.json"                    ^
  /PackageFile "C:\ScriptPackages\<package id>_<package name>_(<database hosting server name>_<database name>_<schema name>).zip"           ^

Response

...
Executing script: 'C:\ScriptPackages\....sql'
Elapsed Time is 00:00:01.860

For the help of all commands execute

"ScriptPackageManagerTool.exe /?"

All settings are explained in this documentation.

IMPORTANT
IN THE CASE OF MULTIPLE SCRIPT PACKAGE FILES IT IS IMPORTANT TO IMPORT THEM IN THE SAME ORDER THEY WERE CREATED!

After import​

After the import it's necessary to grant object privileges for all objects owned by the inPoint database owner to inPoint connection user. This must be configured manually on the Q system using the following script.

IMPORTANT

  • After import, a restart of each inPoint service and restart of the Pam.Web application pool is required! The command line tool will not do that!
  • During this phase it's NOT allowed to manipulate with the inPoint objects using the inPoint.Admin.

Phase 6. Executing the wizard on the Q system only if it was executed also on the T system​

When all scripts package files are imported execute the inPoint.Wizard on the Q system only in the case it has been executed in Phase 2. In the inPoint.Wizard the database script execution mode will be already set to "Nicht durchführen"!

phases6_setup_update_noddl

Before testing on the Q system, the following server settings needs to be modified in the "Admin" Window of the inPoint.Client connecting to the Q system environment:

  • Fulltext related:
    • Elasticsearch User
    • Elasticsearch Password
    • Elasticsearch URL
  • Workflow related:
    • Activate Workflow
    • HSService url
    • Server url
    • PortalConnector url
    • Portal
  • Purge Request:
    • Purge Request Folder
    • Confirmation Workflow
    • Audit Folder
  • Others:
    • Show/hide Global Sites
    • Show/hide My Site
    • Show/hide Shared Sites
    • Show/hide Recycle Bin
    • Show/hide Mailbox
    • Report portal url
    • ArchiveDialogDirectItemUri
    • Report ribbongroup
    • Default selected Search Form
    • Folder query hints
    • Document query hints

After adjusting the server settings mentioned above it's allowed to start the tesing phase Phase 7 mentioned below.

IMPORTANT
During this phase it's NOT allowed to manipulate with the inPoint objects using the inPoint.Admin.

Phase 7. Testing on the Q system​

Before switching to the P system, it's important to test the changes on the Q system.

IMPORTANT
During this phase it's NOT allowed to manipulate with the inPoint objects using the inPoint.Admin.

Phase 8. Importing the script packages to the P system​

After the Testing phase Phase 7 of the DLC is finished on the Q system, the database changes done on the T system needs to be imported to the P system using the ScriptPackageManagerTool.

Before import​

  • Stop each inPoint service, if the inPoint is already installed.
  • Stop the Pam.Web application pool, if the inPoint is already installed.
  • Copy the created script package file/files in Phase 4 to the P system.
  • The ScriptPackageManagerTool is not transferring the config file changes, therefore it's necessary to:
    • in case of fresh install, copy the "inPoint.Archive.config" and the "inPoint.HybridStore\appSettings.json" files from the the T system to the P system, by modifying the database connection string accordingly in both files.
    • in case of update, override the "inPoint.Archive.config" and the "inPoint.HybridStore\appSettings.json" files on the P system with the config files from the T system, by modifying the database connection string accordingly in both files.
  • Validate if the user used for inPoint default schema has the following system privileges in Oracle.
  • Validate if the user used as a database connection user in the ScriptPackageManagerTool has the following system privileges in Oracle.
  • In case we have a different connection user with limited access, which connects to the inPoint default schema, execute the following script in the inPoint default schema for all inPoint/custom views containing tables/views from Oracle's sys or other schemas:
REVOKE SELECT ON <inPoint/custom view> FROM <connection user>;
REVOKE UPDATE ON <inPoint/custom view> FROM <connection user>;
REVOKE INSERT ON <inPoint/custom view> FROM <connection user>;
REVOKE DELETE ON <inPoint/custom view> FROM <connection user>;

inPoint contains only one view containing views from Oracle's sys schema:

REVOKE SELECT ON PAM_USERTABLES FROM <connection user>;
REVOKE UPDATE ON PAM_USERTABLES FROM <connection user>;
REVOKE INSERT ON PAM_USERTABLES FROM <connection user>;
REVOKE DELETE ON PAM_USERTABLES FROM <connection user>;

This is required to avoid the "ORA-01720: Grant Option Does Not Exist" exception when recreating the views containing tables/views from different schemas where the connection user doesn't have access.

According to Oracle (detailed description see here):

"The code was changed in 11.2.0.4 so that create view behavior is similar to grant. If you try to make a GRANT on an existing view and the view owner does not have the grant option
, then ORA-1720 is the expected result (even in 11.2.0.3). In 11.2.0.4, we have simply made CREATE VIEW consistent with the GRANT behavior, i.e. if an incompatible grant exists
, then the new view definition must not be allowed (even with FORCE). In other words, we do not allow incompatible grants to coexist with the view definition and so an error must be thrown. 
The behavior in release 11.2.0.3 (and earlier) was incorrect; the new behavior in 11.2.0.4 is intentional and correct."

To correct:

Remove all grants on the view before REPLACING the view. This will ensure that no incompatible grants exist.
  • Create a global environment variable with the following command in administrative command prompt, which sets the database script execution mode to: "Nicht durchführen":
setx inPoint_DatabaseRestriction "DbNoDdl" /M

Import​

The import is done by the ScriptPackageManager tool ("ScriptPackageManagerTool.exe") , which is included in the "inPoint_Tools_<current release>_Release.zip" package.

Copy this tool to any location, and execute the import for each script package file created on the T system with the following command:

TIP: Use "^" at the end of a line, allows to continue the command in the next line to make it more readable!

ScriptPackageManagerTool.exe /Import            ^
  /ArchiveConfig "C:\Program Files (x86)\HS Europe\inPoint.Archive\inPoint.Archive.config"    ^
  /HybridStoreConfig "C:\Program Files (x86)\HS Europe\inPoint.HybridStore\appSettings.json"                    ^
  /PackageFile "C:\ScriptPackages\<package id>_<package name>_(<database hosting server name>_<database name>_<schema name>).zip"           ^

Response

...
Executing script: 'C:\ScriptPackages\....sql'
Elapsed Time is 00:00:01.860

For the help of all commands execute

"ScriptPackageManagerTool.exe /?"

All settings are explained in this documentation.

IMPORTANT
IN THE CASE OF MULTIPLE SCRIPT PACKAGE FILES IT IS IMPORTANT TO IMPORT THEM IN THE SAME ORDER THE WERE CREATED!

After import​

After the import it's necessary to grant object privileges for all objects owned by the inPoint database owner to inPoint connection user. This must be configured manually on the P system using the following script.

IMPORTANT

  • After import, start each inPoint service and restart the Pam.Web application pool! The command line tool will not do that!
  • During this phase it's NOT allowed to manipulate with the inPoint objects using the inPoint.Admin.

Phase 9. Executing the wizard on the P system only if it was executed also on the T system​

When all scripts package files are imported execute the inPoint.Wizard on the P system only in the case it has been executed in Phase 2. In the inPoint.Wizard the database script execution mode will be already set to "Nicht durchführen"!

phases6_setup_update_noddl

Before testing on the P system, the following server settings needs to be modified in case of fresh installation in the "Admin" Window of the inPoint.Client connecting to the P system environment:

  • Fulltext related:
    • Elasticsearch User
    • Elasticsearch Password
    • Elasticsearch URL
  • Workflow related:
    • Activate Workflow
    • HSService url
    • Server url
    • PortalConnector url
    • Portal
  • Purge Request:
    • Purge Request Folder
    • Confirmation Workflow
    • Audit Folder
  • Others:
    • Show/hide Global Sites
    • Show/hide My Site
    • Show/hide Shared Sites
    • Show/hide Recycle Bin
    • Show/hide Mailbox
    • Report portal url
    • ArchiveDialogDirectItemUri
    • Report ribbongroup
    • Default selected Search Form
    • Folder query hints
    • Document query hints

After adjusting the server settings mentioned above it's allowed to start the tesing phase Phase 10 mentioned below.

IMPORTANT
During this phase it's NOT allowed to manipulate with the inPoint objects using the inPoint.Admin.

Phase 10. Testing on the P system​

Before publishing the production environment, it's important to test the changes on the P system as well.

Excluded HybridStore configuration from logging​

All configuration entries for HybridStore are excluded from logging:

  • Create/update/delete location
  • Create/update/delete scheme
  • Create/update/delete job
  • etc

These entries must be configured manually on the Q and P systems using the HybridStoreSv.exe�CONFIG.

Excluded server settings from logging​

The following server settings available in the "Admin" Window of the inPoint.Client are not transferred during the DLC:

  • Fulltext related:
    • Elasticsearch User
    • Elasticsearch Password
    • Elasticsearch URL
  • Workflow related:
    • Activate Workflow
    • HSService url
    • Server url
    • PortalConnector url
    • Portal
  • Purge Request:
    • Purge Request Folder
    • Confirmation Workflow
    • Audit Folder
  • Others:
    • Show/hide Global Sites
    • Show/hide My Site
    • Show/hide Shared Sites
    • Show/hide Recycle Bin
    • Show/hide Mailbox
    • Report portal url
    • ArchiveDialogDirectItemUri
    • Report ribbongroup
    • Default selected Search Form
    • Folder query hints
    • Document query hints