Managing DataSources

A DataSource is a Database published to a DataPortal™ Server that is available for transferring to a DataPortal™ client. DataSources are managed (added, modified, removed, monitored) through the DataPortal™ Server Web management area which can be accessed by pointing a browser to the URL:

http://DPhost:port/DataPortal/ManageDataPortal.jsp
(where DPhost:port is the DataPortal™ server host machine and port value, respectively)

A valid administrator user name and password must be entered to gain access to the management area. Once access is granted, the “Manage DataSources” link should be selected. The options within the DataSource management area are:

  • View Active Data Source Configuration
  • View Active DataSource Status
  • Modify Data Sources

Adding DataSources

When a DataPortal™ Server is first started, no DataSources will have been configured, so both the Configuration and Status areas will be empty. DataSources may be added within the “Modify DataSource” area. DataSources may be added, changed or deleted in the “Modify DataSource Area”. Select the “Add DataSource” link to add a DataSource. The “Add DataSource” area contains the fields required to make a connection with the Database to be published as the DataSource. These fields are:

    • DB Vendor Type
      The DBVendor Type is selected from the drop-down menu which contains all supported data source types.
    • Host
      The Host is the machine hosting the database. A port may be specified by appending a “:’ followed by the port number after the host name. Normally, a port does not need to be specified if the default port for the database is used.
    • DB Name
      The DB Name is the name of the database to be connected to.
    • DataSource Name
      The DataSource Name is the name that the DataSource will be published as, which is the name that will be presented to DataPortal™ clients. The actual database name is not available to clients.
    • User, Password
      The User and Password fields are used to specify the user and password values, if required, to connect to the database.
    • Min. Number of Connections, Max. Number of Connections
      The Min. Number of Connections and Max. Number of Connections are used to specify the range of connections used by a connection pool. DataPortal™ servers establish connection pools to DataSources. A connection pool is a collection of connections that can accommodate server load better than single connections. A DataPortal™ will start with the minimum number of connections and will add connections, as needed, up to the maximum number of connections specified. The connections in the pool are monitored and, if problems are detected, the DataPortal™ server will try to reestablish the connections.
    • Tables to be Shared
      A set of table names, separated by “|” , which will be shared. If any tables are listed in this set, only those tables will be shared. If no table names are listed, all tables will be shared.
      (these values may also be specified in a configuration file – see Table Selection and Filtering below for more details)
    • Tables Restricted by Column
      Tables listed here with associated columns (e.g. tableName1.columnName1|tableName2.columnName2) will be filtered by matching the authenticated name required for accessing the datasource against values in the named column. Where the values match, the corresponding row will be returned.
      (these values may also be specified in a configuration file – see Table Selection and Filtering below for more details)
    • Write Mode
      There are 3 modes for writing data

      • Replace Database
        will create a new database with the specified name at the specified location, writing over any pre-existing database that may be present
      • Edit
        will write new data into an existing database – if there is a conflict between new and pre-existing data,
        the new data will be written over the pre-existing data
      • Append
        new data will be appended along side of pre-exsiting data
    • Require Username/Password
      If access to the DataSource is to be restricted, the “yes” value of the “Require Username/Password for Access” field should be selected, otherwise, access will not be restricted.

A populated “Add DataSource” form is shown in Figure 1.

DataPortal Server DataSource Add

Figure 1) Add DataSource area.

Once the form fields have been populated, the “Add DataSource” button may be selected. A form displaying the newly configured DataSource(s) will be displayed. If the new configuration is acceptable, the “Update” button should be selected, otherwise, additional modifications may be made. Selecting “Update” will update the DataSource configuration, but will not  change the current active DataSources. The updated fields will be displayed but greyed out, indicating they are not editable. To apply the new configuration immediately, select the “Implement DataSource Modifications Now” link. The DataPortal™ server will attempt to make connection(s) to the newly configured datasource(s). The results will be displayed in a DataSource status page, indicating the values of each configure DataSource connection and whether the connection was successful or not.

Each connection for each connection pool is attempted multiple times, if necessary, so, if connections for a requested DataSource can not be made, there may be a noticeable time delay during the connection attempt period.
There are many database, network, and firewall configuration issues which may prevent connections from being made. These include, but are not limited to, access permission for the specified database user, correct connection port, network access to the database, enabling remote database connections, and firewall blocking of required port(s).

DataSource Username/Password Management

If a Username/Password for a DataSource is required, the corresponding values should be specified. This is done by navigating to the “Data Source User Names and Passwords” area (e.g. through the “Manage DataSources” area), (see Figure 2).

Figure 2) Manage DataSource Username/Password area.

The top text area is used for adding, editing and deleting user/password pairs. User/password pairs may be entered, one per line, user name first separated from the password with the “|” symbol. Once all pairs have been entered, the “UPDATE” button may be selected to commit the changes, which take effect immediately. The values may be changed, at any time, by repeating this process and editing, removing or adding pairs.

The lower area is used to associate available user/password pairs (as entered in the upper area) with DataSources (or vice versa). The type of action is selected by choosing a mode, one from the choices:

    1. Show User(s)/Password(s) for Selected DataSource (Select 1)
    2. Show DataSource(s) for Selected User/Password (Select 1)
    3. Apply Selected User(s)/Password(s) to Selected DataSource

If the first mode choice is selected, a datasource is selected from the available list and clicking the “APPLY” button displays the associated user/password pair value(s). Similarly, if the second mode is selected, a user/password pair is chosen and clicking the “APPLY” button displays the associated datasource(s). In the third mode, both user/password pair value(s) and datasource value(s) are selected. Clicking the “APPLY” button then implements the selected associations. The result is that the selected user/password pair(s) will be used for the selected datasource(s)

Table Selection and Filtering

As described above, access to a given data source can be granted or denied at the global level based on authenticating a name/password pair.

There are additional options for controlling how data in a datasource will be shared.

A subset of tables may be selected for sharing. The subset may be selected by entering the table list (delimited with “|”) in the Tables to be Shared data source form field (or in the DataSourceAccessControl file, as described below). If the subset of tables is provided, only those tables will be shared. If no subset is entered, all tables will be shared.

More granular control over datasource access by users can also be specified. A user (authenticated with an assigned password, as described above) can be granted or denied access to any row of an available table based on a match of the name with the value in a specified column. For each available table, a user (authenticated with a matching password, as described above) may be granted access to only those rows in the table where the value of the specified column matches the name value. If there is no table/column restriction (specified in a list of the form Table1.column1|Table2.column2… in a Data Source Web form or in a configuration file as described below), then, as long as the table is available, all data in that table will be shared.

Specifying which tables will be made available and, within a table, which rows will be available to users can be done with the Data Source Web form or the DataSourceAccessControl configuration directory. The details for using the Web form are described above. To use configuration files:

File Names and Location:
Table selection and filtering information is placed in a file with the same name as the Data Source in the  directory CONFIGserver/DataSourceAccessControl below the root directory where the DataPortal™ server is installed. If there is no such file for a given datasource, access to that datasource is not restricted.

File Contents:
Each user access control file should consist of one block of lines for each table that a user should be granted access to, where each line should have the form: Field Name_k = Field Value, where Field Names are “Table”, “Available” and “UserColumn” and “k” is the table number, starting at 1.

Each table block has the following structure, where table “1” is shown:

Field Name = Field Value
Table_1 = Table Name
Available_1 = Whether the table is available to users(Values: “TRUE”,”FALSE”)
UserColumn_1 = Column whose value must match the user value to return the row
 Additional Rules:

  • If no access control file exists with the same name as a datasource, access to that datasource is NOT RESTRICTED
  • If there is no block (in an access control file) associated with a table in the database, that table will NOT BE AVAILABLE
  • If a UserColumn line is blank or has no value, NO USER VALUE FILTERING is performed for that table – so all or none of the data is returned, depending on the “Available_k” value

By way of example, assume a company keeps a mix of information, including employee and customer order information, in an “OPERATIONS” database. Further, assume that the company wants to use this database to deliver current order status data to its customers. The complete “OPERATIONS” database consists of the tables:

CUSTOMERS, EMPLOYEES, ORDERS, PRODUCTS, SUPPLIERS, SHIPPERS, TERRITORIES

The company would like each customer to receive each of the rows in the “ORDERS” table that correspond to the customer’s name, as stored in the database. Further, the company would like to provide the complete “SHIPPERS” and “PRODUCTS” data to each customer so the customers can follow up directly with shippers, if necessary, and also consider ordering additional items from their product list. The company does not want to provide any other information from the “OPERATIONS” database – e.g. customers have no need to get access to employee information.

The “OPERATIONS” database is published to a DataPortal™ server with the name “OPEN_ORDERS” and a set of customer names and passwords is assigned for each customer given access to the “OPEN_ORDERS” datasource. Order items exist as rows in the “ORDERS” table and the column “CUSTOMERID” is used to specify the customer for each order item. The user/password pairs are assigned to be consistent with values used in the “CUSTOMERID” column of the “ORDERS” table.

Source “ORDERS” Table:

The table actually received by the user/customer “VINET” is:

“ORDERS” Table Received by User/CUSTOMER “VINET”:

The user/customer “VINET” has received the rows from the source “ORDERS” table where the value in the “CUSTOMERID” field matched “VINET”. So, the “VINET” customer has received all the order data, and ONLY the order data they should receive.

Then a user access control file, named “OPEN_ORDERS” is placed in the  DataSourceAccessControl directory and has the following contents:

OPEN_ORDERS:

Table_1=ORDERS
Available_1=TRUE
UserColumn_1=CUSTOMERID

Table_2=SHIPPERS
Available_2=TRUE
UserColumn_2=

Table_3=PRODUCTS
Available_3=TRUE

Alternatively, to use the Web Data Source form, the following entries to fields would be made:

Table Filtering From