Differences

This shows you the differences between two versions of the page.

--- cobi.wms:management_database [2023/04/19 09:41] – tkammer
+++ cobi.wms:management_database [2023/08/24 16:23] (current) – tkammer
@@ Line 38: / Line 38: @@
 See also: [[COBI.wms:Architecture]]
-Example for adding a productive and a test connection:
+Example for adding a productive and a test connection for an on-premises environment, using unencrypted communication with Service Layer so it doesn't require a valid SSL Certificate:
 <code sql>
@@ Line 47: / Line 47: @@
 ('02 - TEST', 'SBO_TEST', 'SL', 'http://{host}:50001/b1s/v2', 'SBO_TEST', 'manager', 'secret');
 </code>
+**Note:** The examples above use **''http:''** instead of **''https:''** and the port number **50001** instead of **50000**.  This means that communication with Service Layer will be unencrypted, and the app will skip over the Load Balancer and directly access Node 1 of Service Layer.
+If it's important for you to have encrypted communication with Service Layer, and/or you experience performance issues due to the Load Balancer being skipped, then you must ensure that a valid SSL Certificate is installed for Service Layer, and change the ''http'' above to ''https'' and the port number 50001 to 50000.
 ==== Optional columns ====
@@ Line 62: / Line 66: @@
 | HANAProxyPort | Text                  | HANA Proxy port number                        |
 | Profile       | Text                  | Code to enable a customer-specific profile    |
+| PrintService  | Text                  | Address of the COBI.wms Print Service         |
 The columns ''DBType'', ''SQLHost'', ''SQLUser'', and ''SQLPass'' only need to be filled if the SAP Business One company database resides on a different database server than the one on which the management database is installed.  I.e. you can redirect the app to a different server/database by filling these columns.  The ''SQLPort'' column only needs to be filled if the database server listens on a different port than the default (1433 for MS SQL Server, 30015 for SAP HANA).
@@ Line 68: / Line 73: @@
 The ''Profile'' column is used to enable customer-specific specializations in the app and should be left empty unless instructed.
+The ''PrintService'' column can be used to centrally define the address of the [[Print Service|COBI.wms Print Service]].  If not defined here, it has to be set on each Android device in the [[Print Settings]] screen of COBI.wms.  When using the standard port of the COBI.wms Print Service, you should only enter the host name or IP address in this column.  When using a non-standard port number, you can specify it by entering the value ''HOST:PORT'' in this column where ''HOST'' is the host name or IP address and ''PORT'' is the TCP port number.
@@ Line 133: / Line 140: @@
 ==== Separate login per user ====
-Note: This feature requires the app to be connected via Service Layer.
 You can specify a separate SAP Business One login for each COBI.wms user or device.  This way, the Change Log in SAP Business One can correctly display which COBI.wms user or device booked or updated a document.
-To make use of this feature, first make sure that the users table in the **COBI.wms Management Database** contains the ''apiUser'' and ''apiPass'' columns.  If they are missing, add them like this:
+Specify the SAP Business One login for a COBI.wms user by executing the following SQL command on the Management Database:
-<code sql>
--- MS SQL Server -----------
-use cobiwms;
-alter table users
-add apiUser nvarchar(255),
-    apiPass nvarchar(255);
-drop view cwms__version;
-----------------------------
--- SAP HANA  ---------------
-set schema cobiwms;
-alter table users
-add ( apiUser nvarchar(255),
-      apiPass nvarchar(255) );
-drop view cwms__version;
-----------------------------
-</code>
-(The "drop view" at the end will make the app re-generate some stored procedures on next login.)
-After that, you can overwrite the SAP Business One login for a COBI.wms user the following way:
 <code sql>
@@ Line 189: / Line 172: @@
 Once you've updated the ''users'' table with these commands, just restart the COBI.wms Android app and the change will take effect.  You can make a test booking with the app and check the Change Log in SAP Business One to make sure that it worked.
-**WARNING:** When you save the SAP Business One user's password in the ''apiPass'' field as shown above, the password will be seen in plain text in the management database.  (This should generally not be an issue because untrusted persons should not have access to your database server.)
+**WARNING:** When you save the SAP Business One user's password in the ''apiPass'' field as shown above, the password will be seen in plain text in the management database, just like the ''apiPassword'' column of the ''companies'' table.  This should generally not be an issue because untrusted persons should not have access to your database server.  However, if this poses an issue for you, see the next section.
+==== Avoiding plaintext passwords in the database ====
+Normally, the password of an SAP Business One user has to be specified either in the ''apiPassword'' column of the ''companies'' table, or in the ''apiPass'' column of the ''users'' table.
+(Technical details: It makes no sense to encrypt these columns, because the key to decrypt them would need to be deployed as part of the app, such that anyone could access it by analyzing the Android app.  We cannot store a hashed value either, because the app needs to forward the plaintext password to Service Layer when logging in to SAP Business One.)
+To avoid this issue, the following strategy can be used:
+  - Leave the ''apiPassword'' and ''apiPass'' columns in the database empty
+  - Create COBI.wms users with the same username and password as SAP Business One users
+When you enter a username & password in the COBI.wms login screen, the app first uses these to perform a COBI.wms user login.  It then tries to find a username & password for Service Layer by checking the ''apiPassword'' and ''apiPass'' columns mentioned above.  However, if these columns are empty, the app will simply take the username & password that were entered for the COBI.wms user login, and try to use these for the Service Layer login as well.  So, if the username & password of the COBI.wms user is the same as an SAP Business One user, it will work.
+(Technical details: The password of a COBI.wms user is //not// stored in plaintext in the database, only a secure hash value of it is stored, since it doesn't need to be forwarded anywhere.)
 ===== License management =====
+==== Licensing model ====
+Every parallel access to COBI.wms requires a license.  For example, if a maximum of 3 people will use COBI.wms at the same time, you will need 3 licenses.  However, whether you want to license devices or users is up to you.
+You could assign your licenses to three devices, so anyone can use those devices with or without a COBI.wms user.  Or you could assign your licenses to three COBI.wms users, so those users can use COBI.wms on any number of Android devices by using their login.  You can also mix the two models.  For example, you could assign licenses to two devices so anyone can use them, and assign a third license to a user so that user can use any Android device to log in.
 ==== Importing licenses ====
@@ Line 199: / Line 203: @@
 <code sql>
+-- Change LICENSE_1, LICENSE_2 etc. to the actual license, keep the apostrophes.
 insert into licenses (license) values ('LICENSE_1');
 insert into licenses (license) values ('LICENSE_2');
 insert into licenses (license) values ('LICENSE_3');
+</code>
+The licenses table also has an optional ''notes'' column which you can use for notes about the license.  For example, if you have both regular COBI.wms licenses as well as COBI.ppc licenses in the same database, you can differentiate them through these notes.  Or, when importing test licenses that are only valid for a limited time, you can enter this as a note.  Examples:
+<code sql>
+insert into licenses (license, notes) values ('LICENSE_1', 'WMS');
+insert into licenses (license, notes) values ('LICENSE_2', 'PPC');
+insert into licenses (license, notes) values ('LICENSE_3', 'PPC, valid until November 2023');
 </code>
 ==== Assigning licenses ====
-Every parallel access to COBI.wms requires a license.  For example, if a maximum of 3 people will use COBI.wms at the same time, you will need 3 licenses.  However, whether you want to license devices or users is up to you.
+=== Bulk-editing the LICENSES table ===
-You could assign your licenses to three devices, so anyone can use those devices with or without a COBI.wms user.  Or you could assign your licenses to three COBI.wms users, so those users can use COBI.wms on any number of Android devices by using their login.  You can also mix the two models.  For example, you could assign licenses to two devices so anyone can use them, and assign a third license to a user so that user can use any Android device to log in.
+The information of which user or device a license is assigned to is found directly in the LICENSES table of the management database.  If you want to make a large number of changes, it might be easiest to directly modify this table.
+For example, in MS SQL Server Management Studio, you can right-click on the Devices table and select "Edit top 200 rows" and directly edit the "UserID" or "DeviceID" column of each license.  (For each license, only one of the columns can be filled, the other must be NULL.)
+=== Using stored procedures ===
-To assign licenses to devices and/or users, use the ''assignDeviceLicense'' and ''assignUserLicense'' procedures.  These will automatically check whether you have any free (unassigned) licenses and use one of them:
+To assign licenses to devices and/or users, you can also use the ''assignDeviceLicense'' and ''assignUserLicense'' procedures.  These will automatically check whether you have any free (unassigned) licenses and use one of them:
 <code sql>
@@ Line 224: / Line 241: @@
 </code>
-For revoking licenses, use ''revokeDeviceLicense'' and ''revokeUserLicense'':
+For revoking licenses, you can use the ''revokeDeviceLicense'' and ''revokeUserLicense'' procedures.  This will free up the license that is currently used by a given user or device, so you can then assign it to another user or device:
 <code sql>