Database Preparation for PostgreSQL

Database Preparation for PostgreSQL

This section describes the necessary database preparation that must be completed before any of the SICS software can be executed.

Your DBA will be responsible for correctly configuring and creating the SICS database.

Database setup #

Before any of the SICS tables can be created, you will need to create the files necessary for the storage of the database using the DBMS tools. This will generally involve creating the instance, specifying where the tablespace files will be stored, creating and configuring the database for your specific requirements.

See Database Structure for guidance on SICS-specific issues.

NOTE:
It is highly recommended that the user/login name who will ‘own’ the database (create/drop tables, indexes, constraints, etc) is defined in UPPERCASE. If this rule is not followed, SICS may not work properly.

NOTE:
SQL statements generated by SICS do NOT use delimited identifiers. This means that all PostgreSQL identifiers (database names, schema names, login names, user names, table names, etc) must adhere to the PostgreSQL rules for regular (undelimited) identifiers: The name of an identifier must start with a letter, _,@, or #; followed by one or more letters, numbers, @, $, #, or _.

Create the Environment table #

The environment table CNU_ENVIRONMENT must be created in its own schema. The suggested schema is SICS. No other tables should be created in this schema. Specifically, one should not create P&C or Life tables in this schema.

  • Create a database user SICS (note: user name in UPPERCASE) with the appropriate database privileges.
  • Run the SICS System Administration Utility. Logon as SICS.
  • When asked for an environment, choose Personal Database.
  • Choose the Database Setup, Manage Environments option.

Insert the required environments into this table (Right-click to add an environment.) Change the name of the Owner as required. This should correspond to the planned SICS Table Owner as described in Creating the SICS Table Owner.

Environment Table Owner Privileges #

Create a login for the SICS user, with access to the SICS database (as default), and assign db_datareader and db_datawriter roles on the database. From the database properties/permissions page, grant Create Table permissions to this user.

Environment Table for P&C and Life co-existence #

It is possible to run both the Life and the P&C system over the same set of database tables. This is described in section Co-existence of SICS P&C and Life. With respect to the environment table, two rows/environments must be inserted into the table. Make one environment for P&C and another for Life, both pointing to the same schema owner.

Database Users file setup #

The file SysUsers.txt is used during SICS Database Tables creation (see section Create Database Tables) to create the initial SICS users. A database record is created for all users defined in this file. The delivered file contains the definition for two sample users named User1 and User2.

Each user entry has the following format:

USERID <tab> LAST NAME <tab> FIRST NAME

You will need to edit this file to include as many initial users of the SICS database as required. Please note that you must follow this format strictly. Empty lines and spaces instead of tabs will halt the installation-process.

SysUsers.txt can be found in the <installation_root>\setup folder.

Before a SICS user name can be used for logging on to SICS, the user name must be created as a user on the target database management system and privileges assigned. This can be done after the Sysusers.txt file has been loaded when loading base data.

See section Create Database Tables for details.

After the initial users have been created during system setup, additional users may be added as required by using the SICS Create User option.

See section Create Additional Users for details.

Creating the SICS Table Owner #

Before continuing with the next phase of the setup, an initial DBMS user called the ‘Schema Owner’ or ‘Table Owner’ must be created. The suggested name for the table owner is SICSPC for SICS P&C or SICSLF for SICS Life. Each SICS database/environment/schema must have its own, unique table owner user.

NOTE:
It is highly recommended that the user/login name of the table owner is defined in UPPERCASE. If this rule is not followed, SICS may not work properly.

The user should be created with the appropriate database privileges. See the sections below for your specific DBMS.

When you later run the SICS System Administration Utility to create the SICS tables, you must use this user name to log on to the database.

The SICSPC (SICSLF) user will then become the owner of the SICS tables.
The delivered SICS reporting universe files reference a database user named SICSPC (SICSLF). Customers using the SICS reporting solution without the Business Objects Designer module, cannot change this database connection information and must therefore use the suggested names above as the owners of the SICS tables.
Therefore, if you create the tables with another owner, you will not be able to use the SICS Reporting facility (unless you have the Business Objects Designer module).

Note that the SICSPC (SICSLF) user does not have to be included in the SysUsers.txt file, since the SICS Create Tables function (see section Create Database Tables) will automatically create a SICS user for the table’s owner. This SICS user will be assigned the System Administrator role within the SICS security system.

SICS Table Owner privileges #

Create the SICSPC or SICSLF user and grant Create Table privileges to this user.

Grant SELECT permissions on the CNU_ENVIRONMENT table to the user. This can be done by choosing ‘Properties’ from the sub-menu for the CNU_ENVIRONMENT table, clicking ‘Permissions…’, checking SELECT for the SICSPC or SICSLF user and then clicking the OK button.

Create a View for this user to the CNU_ENVIRONMENT table

CREATE VIEW SICSPC.CNU_ENVIRONMENT AS SELECT * FROM SICS.CNU_ENVIRONMENT

Creating SICS Database tables #

System Users File #

It is assumed that the SysUsers.txt file has been modified to include all required SICS users.
Please refer to section Database Users file setup for details.

Create Database Tables #

Start the SICS System Administration Utility by selecting SICS P&C (Life) System Administration Utility from the SICS menu item on the Windows Programs menu. Login as the SICS Table Owner as defined in Creating the SICS Table Owner. When prompted to select the environment, choose an entry with an empty owner (typically, ‘Personal Database’).

Open the Database Setup Folder. You have the following options:

  • Manage Environments: With this option, you can add, remove and rename the different environments specified in the CNU_ENVIRONMENT table. See Create the Environment table for configuration details of this table.
  • Manage Tables: This option will present you with a window, allowing you to selectively create or drop tables and views, constraints, indexes and table comments.

ManageDatabaseTables_3.png

If you create tables using this option, you must also create base data and sample reference data, if required.

Manage Tables - Options:

  • The Options tab on this window allows you to specify the Tablespaces where you wish to put the tables.
  • SICS does not support tablespace options for PostgreSQL.
  • Create Base Data: This option will create all data that is necessary to run the SICS system. This includes Reference Data (System Defined values only, not Sample values); Countries, States, Currencies, Users and Security Information.
  • Create Sample Reference Data: This option will create additional Reference Data values (additional to the System Defined values). If the sample values are not created, many of the reference data types will not contain any values, or only a limited set of system defined values. This will limit the usage of the system. Hence, you will have to go through the Reference Data Types and add your own values. All Sample Reference Data values can be deleted/edited by the System Administrator. For more information on Reference Data, refer to the SICS User’s Guide.
  • Create Sample Data: This option will load the SICS development team sample data into the database. This option is only relevant if you want to create a test database. This option should not be used when setting up the production environment. Some sample Business Partners, Reporting Units, Entry Codes, etc. will be created.
Dropping All Tables And Views #

The SICS System Administration Utility ‘Database Setup’ -> ‘Manage Tables’ -> ‘Drop All User Tables’ option will drop all tables owned by the logged on user. The option ‘Drop All User Views’ will drop all views owned by the logged in user. In other words, the drops are not confined to tables (and views) created/used by SICS.

Creation of the SICS Role / Group #

The SICS Role is used to assign the relevant database privileges to the users of the system. The creation of this role negates the need for assigning select, insert, update and delete privileges to all tables to all users, and also allows the users to read the CNU_ENVIRONMENT table. By specifying these privileges once only in a role and assigning the role to all users, this drastically cuts down the user creation time.

Note:
You can not complete the following steps until the SICS database tables have been created (as described in Create Database Tables).

SICS Role #

Create a new role (suggested name SICSG2).This can be done by choosing ‘New Database Role…’ from the sub-menu under Roles off the SICS database.

Select the Properties of the role, and then click on the Permissions button, and grant select on the SICS.CNU_ENVIRONMENT table to the group.

Grant select, update, insert and delete rights on all of the SICS tables created earlier under the SICSPC or SICSLF user to the group. This can be done by executing the following command and then executing its results:

SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON SICSPC.' + NAME + ' TO SICSG2' FROM SYSOBJECTS WHERE TYPE = 'U' AND UID = (SELECT UID FROM SYSUSERS WHERE NAME = 'SICSPC')

Also grant select on SICS views (not including views used by the Business Objects reporting universes) with this following command:

SELECT 'GRANT SELECT ON SICSPC.' + NAME + ' TO SICSG2' FROM SYSOBJECTS WHERE TYPE = 'V' AND NAME NOT LIKE 'V_%' AND UID = (SELECT UID FROM SYSUSERS WHERE NAME = 'SICSPC')

Note:
You need to be logged in as the owner of the tables in order to grant the above privileges.

One and Two Level Roles / Groups #

For a One-Level Login (see One Level Logins), the above role/group/procedure can be used for the First Level user.

For a Two-Level Login (see Two Level Logins), the role/group/procedure created above will be assigned to the Second Level User. The first level users will simply have SELECT privileges on the SICS.CNU_ENVIRONMENT, SICSPC.CNU_USER and SICSPC.CNU_SECUREOPTS tables (see User Database Privileges for more information).

If the User Authorization Interface (UAI) is in use, then the first level users also need SELECT privileges on the SICSPC.SYSTEM_PARAMETERS and SICS4PC.SYS_PARAM_VALUES tables.

The privileges for the first level user can also be assigned to a role or group (suggested name SICSG1.) This will make it easier to create new users in the database. Simply grant the role to the user (or assign the new user to the group) for the user to inherit the privileges.

SICS Configuration #

After the database has been loaded with startup data, as described in the previous section, the SICS System Administrator must configure various system functions before general operation of the system.

As mentioned earlier, the User ID that is used when the database is loaded with initialization data is automatically designated as the system administrator.

User Security Configuration #

The following sections describe how to define users to the system and assign general categories of allowable business functions (User Roles).

One and Two Level Logins #

SICS handles it’s own security internally, utilizing the user role and use cases concepts to facilitate this, and thus the software reads data from the database to control user access. This means that the user has to have database access to all of the data so that the security components of SICS can be utilized. These access privileges are controlled by the database Role (see Creation of the SICS Role / Group.)

This Role or Group has to have select, update, insert and delete privileges to all of the SICS tables in the database. SICS itself then controls whether a particular table can be manipulated by the user, based on his or her user role.

Therefore, a user will have a specific user ID, with SICS Role or Group access that has select, update, insert and delete on all SICS tables and select on all SICS views (and select on the CNU_ENVIRONMENT table.) This is defined as a One-Level Login.

SICS has it’s own application-level security system, preventing unauthorized access to application functions. On a database level, however, a user needs access to all tables. Because of this, there exists a possibility that tables can be accessed and altered from an external source, such as ISQL_w. To solve this security problem, a second level of security has been developed in SICS that removes this possibility. This solution is called the Two-level Login.

The Two-Level Login utilizes two user IDs on the database, one of which is hidden from the user. The SICS System Administrator will control the user ID and password from within the Security window in the SICS System Administration utility. This will only be available to view by users with SICS Security Administration rights.

install_24.png

Figure 9 - One-Level Login

In a One-Level login, the DB User ID shown above has to be empty or equivalent to the Logon ID. This tells SICS that only the One-Level Login is in use. Note that the DB Password field should be left empty.

The Logon ID will be the ID that the user utilizes to log onto SICS.

If this login method is being used, then the user will only require one user ID on the Database, and one equivalent user defined in the SICS Security System. In this example, these will both be the user JOEB. This user will require access to all of the SICS tables and views in the database, which is assigned through the Role or Group. See User Database Privileges below for details on how to assign the Role or Group to the user.

Figure 10 - Two Level Login

In a Two-Level Login system, the Logon ID will be the ID that the user logs on to SICS with, in this case JOEB. This is the first user ID, which MUST be defined on the database with simply SELECT privileges on the CNU_ENVIRONMENT, SICSPC.CNU_USER and SICSPC.CNU_SECUREOPTS tables. (If the User Authorization Interface (UAI) is in use, then the first level users also need SELECT privileges on the SICSPC.SYSTEM_PARAMETERS and SICS4PC.SYS_PARAM_VALUES tables.)

The second user ID is given by the DB User ID field in the window above, in this case F67PK09. This tells SICS that the Two-Level Login is being used, and so after the Environment table and User table have been accessed, the first user will be logged out of the database. The second user will then be logged back into the database automatically, using the password given in the DB Password field. This must match the password for the second-level user on the database or the connection will fail. This user will require access to all of the SICS tables and views in the database, which is assigned through the Role or Group.

Note that in a two-level login system, all Events in SICS will be logged under the first user ID. The second level ID should not be defined in SICS Security. The user will not be aware of the second user ID on the database, and should never be allowed to view the user settings. If the user manages to obtain their second level login ID and password, then they could login to the SICS database using an external program, and perform updates and deletes using SQL.

See User Database Privileges below for details on how to assign the various privileges, Roles or Groups to the two user ID’s.

If security is a key concern in your organisation, then it is recommended to use the Two-Level Login. This will complicate the administration of the system due to the hidden second user ID, and will increase the required time to add new users to the system, but will provide a secure SICS database.

Additional Information for logging into SICS Environment #

For Creation of SICS user you also need to create an equivalent database user through pgAdmin.

Database Level: Need to create role and Database using following command :

CREATE ROLE hjordan WITH LOGIN SUPERUSER CREATEDB CREATEROLE INHERIT REPLICATION CONNECTION LIMIT -1 PASSWORD 'xxxxxx';

CREATE DATABASE hjordan WITH OWNER = hjordan ENCODING = 'UTF8' CONNECTION LIMIT = -1;

The Database is "hjordan"
The Database owner is "hjordan"
The Database schema is "public"
The datasource name is "postgreSQL_DXCDARCOP002"

SICS Level: Launch SICS System Administration utility and login with “hjordan” as a Database owner.

SICS Desktop -> System Administration -> Security -> Users

install_27.png

install_28.png

install_29.png

<source name="postgreSQL_DXCDARCOP002">
    <driver>org.postgresql.Driver</driver>
    <accessor>com.csc.troll.accessor.JDBCDatabaseAccessor</accessor>
    <interface>com.csc.troll.accessor.PostgresDatabaseInterface</interface>
    <dataSource>jdbc:postgresql://DXCDARCOP002v.dk.emea.csc.com/</dataSource>
    <caseSensitiveUserNames>true</caseSensitiveUserNames>
    <optimizedMode>true</optimizedMode>
    <useParameterizedSql>true</useParameterizedSql>
    <statementCacheSize>100</statementCacheSize>
  </source>

Create Additional Users #

After the initial users have been created during system setup, additional users may be added by using the SICS Create User option (accessible from the SICS Desktop -> System Administration -> Security -> Users).

  • Login to the SICS Workstation as the system administrator and double-click the Security icon under the System Administration folder.
  • Use the Access Security Manager window to create any additional system users that were not defined in the SysUsers.txt file. From the System Users tab, right click and select New… from the popup menu.

You will also need to give the user privileges to the appropriate tables, and also assign SICS user roles.

NOTE:
If you intend to export the data from one schema into another, then the owner of the target schema should be defined as a SICS user with System Administrator role before the export takes place. Otherwise SICS access security will prevent the target schema owner to log in as himself.

NOTE:
If you intend to export the data from one schema into another, then the owner of the target schema should be defined as a SICS user with System Administrator role before the export takes place. Otherwise SICS access security will prevent the target schema owner to log in as himself.

User Database Privileges #

One-Level Logins #

For each SICS user that you define - either by adding names to the SysUsers.txt file, or by creating users from the SICS System Administration Utility - you must also:

  • Create an equivalent database user.
  • Assign the role SICSG2 to the user.
  • Create a view on the SICS.CNU_ENVIRONMENT table for this user. This can be done with the following command:
CREATE VIEW <USERID>.CNU_ENVIRONMENT AS SELECT * FROM SICS.CNU_ENVIRONMENT
Two-Level Logins #

For each SICS user that you define - either by adding names to the SysUsers.txt file, or by creating users from the SICS System Administration Utility - you must also:

  • Create two database users, one for the first level login and one for the second level login. A general second level user can be used if preferred (i.e. Use the same second level login for each user.)
  • Assign the SICSG1 role to the 1st level user.
  • Assign the SICSG2 role to the 2nd level user.
  • Create a view on the SICS.CNU_ENVIRONMENT table for the 1st level user.
  • Create a view on the SICS.CNU_ENVIRONMENT table for the 2nd level user.
CREATE VIEW <USERID>.CNU_ENVIRONMENT AS SELECT * FROM SICS.CNU_ENVIRONMENT
General Information #

You can not complete the above steps until the SICS group/role has been created (as described in Creation of the SICS Role / Group).

NOTE:
When you have successfully created new SICS users (both in SICS and on the database) you can log on to SICS using the new user names. These users will not have any initial SICS Access Rights. Thus, initially, the SICS Workstation will appear blank (no folders will be visible).

You have to grant the SICS users access rights, as described in section Assign Users to User Profiles below.

Assign Users to User Profiles #

A few sample, predefined User Roles are created as part of loading the Base Data. In addition, the system defined User Role “System Administrator” is created.

Logon to the SICS System Administration Utility as the System Administrator (the owner of the tables) and choose System Administration, Security.

  • From the User Roles tab, double-click a user role, select the Members tab, right-click on the user list, and select Assign Members… from the popup menu.

Note that if a new SICS user has not been assigned to a user role, the user will not have access to any of the SICS functions.

If you login to SICS as a user without any access rights, the SICS Workstation will appear ‘blank’ (no system folders will be visible).

Verify System Parameters #

The System Parameters have been set up by default as part of the database creation process, but they may be inappropriate for your operation. Therefore, you will need to check and/or alter them as appropriate.

Login to the SICS Workstation as the system administrator and double-click the System Parameter Maintenance icon under the System Administration folder.

Verify or modify the System Parameters as necessary.