Differences

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

--- cobi.wms:management_database [2022/10/25 07:11] – [Permission management] tkammer
+++ cobi.wms:management_database [2023/08/24 16:23] (current) – tkammer
@@ Line 31: / Line 31: @@
 **Tip:** You can use the character sequence ''{host}'' as part of the ''APIURL'' value, to make the app use the same host name (or IP address) as that of the management database.
+**Tip:** (only for Service Layer) If you want to enforce warehouse employees to use individual logins, you can leave the ''APIUsername'' and ''APIPassword'' fields NULL and only fill in the ''APIUser'' and ''APIPass'' fields in the Users table instead, as explained below in the section: [[#Separate login per user]]
+**Note:** If the ''ApiType'' is ''SL'', then the ''SQLDB'' column can be left empty, which will cause the app to use a pure Service Layer connection as if in a Cloud environment.  However, this is **strongly discouraged** because a direct database connection offers significantly higher performance and stability.  Therefore, the ''SQLDB'' column should always be filled for all On-Premises installations.
 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>
 insert into companies (companyId, sqlDb, apiType, apiUrl, apiId, apiUsername, apiPassword) values
-('01 - PROD', 'SBO_PROD', 'SL', 'http://{host}:50001/b1s/v1', 'SBO_PROD', 'manager', 'secret');
+('01 - PROD', 'SBO_PROD', 'SL', 'http://{host}:50001/b1s/v2', 'SBO_PROD', 'manager', 'secret');
 insert into companies (companyId, sqlDb, apiType, apiUrl, apiId, apiUsername, apiPassword) values
-('02 - TEST', 'SBO_TEST', 'SL', 'http://{host}:50001/b1s/v1', 'SBO_TEST', 'manager', 'secret');
+('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 58: / 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 64: / 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 129: / 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 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>
-use cobiwms;
-alter table users
-add apiUser nvarchar(255),
-    apiPass nvarchar(255);
--- Make the app re-generate stored procedures on next login
-drop view cwms__version;
-</code>
-After that, you can overwrite the SAP Business One login for a COBI.wms user the following way:
-<code sql>
-use cobiwms;
 update users
 set apiUser = 'sbo_username',
@@ Line 163: / Line 157: @@
 <code sql>
-use cobiwms;
 update users
 set apiUser = 'wms0001',
@@ Line 180: / 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 190: / 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');
@@ Line 195: / Line 209: @@
 </code>
-===== Assigning licenses =====
+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:
-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.
+<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>
-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.
+==== Assigning licenses ====
+=== Bulk-editing the LICENSES table ===
+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 215: / 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>
@@ Line 236: / Line 262: @@
 The settings for users take precedence over the settings for devices.  For example, you could block a number of modules for a certain device, but if a user logs in on that device who is explicitly given permissions for those modules, then the user can use those modules anyway.  Conversely, if a number of modules are explicitly blocked for a user, then no matter what device the user logs in to, those modules will not be available to that user.
-You can effectively block usage of the app without COBI.wms user login, by blocking all modules for all devices, and then giving permissions to the users.
+You can effectively block usage of the app without COBI.wms user login, by blocking all modules for all devices, and then giving permissions to the users.  (This can also be done by leaving the ''apiUsername'' and ''apiPassword'' fields in the Companies table empty, and only setting them for individual users.)
 To allow/block modules for devices/users, use the ''setDevicePermission'' and ''setUserPermission'' procedures:
@@ Line 270: / Line 296: @@
   * ''WTQ'': Inventory transfer request
   * ''PRINT'': Label printing
+For your convenience, here is a template for calling the ''setUserPermission'' procedure once for every module, which you can copy & paste into SQL Server Management Studio or HANA Studio:
+<code sql>
+-- MS SQL Server
+exec setUserPermission 'username', 'IGN', 1;
+exec setUserPermission 'username', 'IGE', 1;
+exec setUserPermission 'username', 'WTR', 1;
+exec setUserPermission 'username', 'PDN', 1;
+exec setUserPermission 'username', 'PKL', 1;
+exec setUserPermission 'username', 'DLN', 1;
+exec setUserPermission 'username', 'RPD', 1;
+exec setUserPermission 'username', 'RDN', 1;
+exec setUserPermission 'username', 'IPE', 1;
+exec setUserPermission 'username', 'IPN', 1;
+exec setUserPermission 'username', 'PRQ', 1;
+exec setUserPermission 'username', 'POR', 1;
+exec setUserPermission 'username', 'ITM', 1;
+exec setUserPermission 'username', 'INC', 1;
+exec setUserPermission 'username', 'WTQ', 1;
+exec setUserPermission 'username', 'PRINT', 1;
+-- SAP HANA
+call setUserPermission('username', 'IGN', 1);
+call setUserPermission('username', 'IGE', 1);
+call setUserPermission('username', 'WTR', 1);
+call setUserPermission('username', 'PDN', 1);
+call setUserPermission('username', 'PKL', 1);
+call setUserPermission('username', 'DLN', 1);
+call setUserPermission('username', 'RPD', 1);
+call setUserPermission('username', 'RDN', 1);
+call setUserPermission('username', 'IPE', 1);
+call setUserPermission('username', 'IPN', 1);
+call setUserPermission('username', 'PRQ', 1);
+call setUserPermission('username', 'POR', 1);
+call setUserPermission('username', 'ITM', 1);
+call setUserPermission('username', 'INC', 1);
+call setUserPermission('username', 'WTQ', 1);
+call setUserPermission('username', 'PRINT', 1);
+</code>
+Just change ''username'' to the actual username via search & replace in Notepad or the like, and switch the 1 to a 0 for the modules to disable.
+Here's the same for devices:
+<code sql>
+-- MS SQL Server
+exec setDevicePermission deviceID, 'IGN', 1;
+exec setDevicePermission deviceID, 'IGE', 1;
+exec setDevicePermission deviceID, 'WTR', 1;
+exec setDevicePermission deviceID, 'PDN', 1;
+exec setDevicePermission deviceID, 'PKL', 1;
+exec setDevicePermission deviceID, 'DLN', 1;
+exec setDevicePermission deviceID, 'RPD', 1;
+exec setDevicePermission deviceID, 'RDN', 1;
+exec setDevicePermission deviceID, 'IPE', 1;
+exec setDevicePermission deviceID, 'IPN', 1;
+exec setDevicePermission deviceID, 'PRQ', 1;
+exec setDevicePermission deviceID, 'POR', 1;
+exec setDevicePermission deviceID, 'ITM', 1;
+exec setDevicePermission deviceID, 'INC', 1;
+exec setDevicePermission deviceID, 'WTQ', 1;
+exec setDevicePermission deviceID, 'PRINT', 1;
+-- SAP HANA
+call setDevicePermission(deviceID, 'IGN', 1);
+call setDevicePermission(deviceID, 'IGE', 1);
+call setDevicePermission(deviceID, 'WTR', 1);
+call setDevicePermission(deviceID, 'PDN', 1);
+call setDevicePermission(deviceID, 'PKL', 1);
+call setDevicePermission(deviceID, 'DLN', 1);
+call setDevicePermission(deviceID, 'RPD', 1);
+call setDevicePermission(deviceID, 'RDN', 1);
+call setDevicePermission(deviceID, 'IPE', 1);
+call setDevicePermission(deviceID, 'IPN', 1);
+call setDevicePermission(deviceID, 'PRQ', 1);
+call setDevicePermission(deviceID, 'POR', 1);
+call setDevicePermission(deviceID, 'ITM', 1);
+call setDevicePermission(deviceID, 'INC', 1);
+call setDevicePermission(deviceID, 'WTQ', 1);
+call setDevicePermission(deviceID, 'PRINT', 1);
+</code>
+Change ''deviceID'' to the correct device ID number via search & replace, and switch the 1 at the end to a 0 for the modules to disable.