EQuIS Professional users may be configured at the database level, i.e. with a direct login to the EQuIS Database where security is controlled by the SQL Server Database and is managed by the DBA. This is a database-level security or DLS user. Since this type of user connects directly to the EQuIS Database, there is greater access by default, e.g. there is no limitation on facility access unless manually configured in SQL Server by the DBA. A DLS login can be granted permissions on database operations and objects such as create/update/insert/delete actions, tables, fields, and table listings.
EQuIS Professional also supports Application Level Security (ALS) users with facility-specific and other permission options configured by EQuIS administrators in EQuIS Enterprise. EQuIS Enterprise logins can be granted permissions on EQuIS objects and operations such as viewing, editing, and creating of facilities, reports, dashboards, and widgets. For more information, see the Permissions chapter.
EQuIS ALS users access Professional via an ALS role (a role with a DLS user in the connection string), retaining their EQuIS Enterprise permissions in Professional. This facilitates EQuIS user management and decreases the effort required by the DBA to create and maintain EQuIS Professional users.
Create an Application Level Security role with the following steps:
For more information on the connections and mechanisms involved, see How Application Level Security Works.
Warning: Potential Security Vulnerability: When using ALS, EQuIS Professional still uses a database connection string to make a direct TCP/IP connection to the database server. The encrypted connection string is sent via HTTPS response from EQuIS Enterprise to EQuIS Professional. EQuIS Professional decrypts the connection string and establishes the TCP/IP connection to the database using the credentials provided in the connection string.
EQuIS Professional hides the connection string from the user, so a typical user will never see the connection string or the credentials used to connect to the database. Without access to the connection string, a typical user will only be able to log in to the database using EQuIS Professional, and will only be able to use facilities they are subscribed to.
First, determine what actions, tables, and fields the users should have access to. For example, users might only need read-only permissions (and be denied the INSERT, UPDATE, or DELETE actions). There might be multiple configurations needed, for example:
•starting in EQuIS 7.23.2, the ES_PUBLIC database role for access to necessary EQuIS functions and stored procedures
•read-only users (such as the standard SQL db_datareader role, although there are further EQuIS requirements as well)
•read-write users (such as the standard SQL db_datawriter role)
•a group of users that can read/write data tables (i.e. DT_*), but can only read reference tables (i.e. RT_*).
Note: Only one Application Level Security role can be assigned per user.
For EQuIS functionality, read-only users also need read/write access to the following tables/views:
•ST_USAGE: Report use logging
•ST_USER_REPORT: Save User Reports
•ST_USER_REPORT_PARAMETER: Save User Report parameters
•ST_LICENSE_USE: Use network license
•ST_USER: Update Login Time
•ST_ID: Lists EQuIS Unique Identifiers (EUIDs) and other system IDs
For Groups functionality, EarthSoft recommends insert/update/delete on the following tables (optional):
For reading the default values of database fields, the GRANT VIEW DEFINITION permission is needed. For example, the Workflow Status column can be added to DT_WATER_LEVEL as a required column with a set default value. However, standard formats for water level data typically do not include DT_WATER_LEVEL.WORKFLOW_STATUS. Attempting to load water level data without GRANT VIEW DEFINITION on the DT_WATER_LEVEL table, 'dbo' schema, or database will lead to the following error:
Column 'workflow_status' does not allow nulls.
Note: Grant additional permissions for each role (and for specific users) with the permissions grid within the Enterprise Role Profile Editor. Examples include access to specific facilities, reports, and/or buttons within EQuIS Professional. By default, ALS roles created prior to EQuIS 126.96.36.19944 will be automatically granted Viewer permission to all Ribbon Tools in EQuIS Professional upon upgrade. However, newly created ALS roles will not have any permissions in the permissions grid; these will need to be assigned.
Once the necessary roles have been defined, the DBA needs to create a separate database login for each role. The login created should have the appropriate database permissions (and synonyms, where applicable) for the group of users that will be using that role. This includes the following:
•tables and views mentioned in the previous section
•the ES_PUBLIC role created in EQuIS 7.23.2, which provides access to necessary EQuIS functions and stored procedures
After creating the database login for each role, update the CONNECTION_STRING field for each record added to the ST_ROLE table. The connection string should be a valid .NET database connection string of the following form:
Data Source=dbserver;User ID=username;Password=password;Database=dbname;
Note that any advanced connection parameters, such as Connection Timeout, should be added here for the desired roles. When connecting to a self-hosted SQL Server instance that is not configured with a SSL certificate issued by a known certificate authority (CA), include the following advanced connection parameter in your connection string:
Warning: SkipIsSameConnection: In order to ensure that both EQuIS Professional and EQuIS Enterprise are connected to the same database, the ALS web service (e.g. ./equis/remoteLogin.asmx) will compare the connection string from ST_ROLE.CONNECTION_STRING with the connection string in the web.config file. If the Data Source attribute or the database of those connection strings is different, the user will not be able to log in to Professional. In some situations, the Data Source must be different (e.g. an external IP address versus internal IP address). Disable the comparison by adding the following <appSetting> to the Enterprise web.config file:
<add key="SkipIsSameConnection" value="true" />
Read SqlConnection.ConnectionString Property for more about Microsoft SQL connection strings.
The following examples presume a connection to a server named CorpDBMS01 with a database named 'EQuIS', username 'EQuIS_service', and password 'pa$$w0rd'.
Microsoft SQL Server using SQL Credentials
Data Source=CorpDMS01; Database=EQuIS; User ID=EQuIS_service; Password=pa$$w0rd;
Microsoft SQL Server using Windows Active Directory Credentials
Data Source=CorpDMS01; Database=EQuIS; Integrated Security=SSPI;
Note: The first time a user logs in to EQuIS Professional using ALS, the ST_ROLE.CONNECTION_STRING will be automatically encrypted in the database.
1.Create the corresponding ALS role:
a.To create a new role, select the plus icon + in the Enterprise Role Manager widget.
b.In the Role Profile Editor that appears, provide a name and the connection string from the previous section. Save.
2.If EQuIS Enterprise users do not yet exist, an administrator should create user accounts in the Enterprise User Manager widget. Alternately, if configured, users can register for their own accounts.
3.The EQuIS administrator must activate new users and assign them to the ALS role. Assign only one ALS role per Enterprise user account. A user is not limited in the number of other Enterprise roles (e.g. Administrator, Manager, and/or User) they can have.
4.After assigning the role (and any other permissions the users need), ensure that the user has at least Viewer permission on one or more facilities as well as access to required buttons on the EQuIS Professional ribbon.
When the previous steps have been completed, log in to EQuIS Professional using ALS. On the EQuIS Professional Connection screen, enter the following information as shown in the example below.
•Server Name: <the full URL to the EQuIS Enterprise application>
•Server Type: EQuIS Enterprise Login
•User Name: <the username of the EQuIS Enterprise user account created>
•Password: <the password of the EQuIS Enterprise user account created>
Note: If the server name begins with HTTPS, the EQuIS Enterprise login radio button will automatically be selected, as only connections with HTTPS in the Server Name will use Enterprise.
Application Level Security (ALS) is a feature implemented in EQuIS that allows the integration of Enterprise logins with EQuIS Professional.
Using ALS, the DBA only needs to create a handful of user account types in the database. The EQuIS Enterprise administrator can assign access to these accounts to EQuIS Professional users by role assignment. Once a user has been assigned an ALS login by their DBA, they will then use their Enterprise login (username and password) when logging into EQuIS Professional.
The ability for the user to log in to either application is based on the roles assigned. For example, there may be some users that can log in to Enterprise, but not Professional and vice versa. There may also be some users that can log in to both Enterprise and Professional.
Using EQuIS Professional with ALS executes these steps:
1.EQuIS Professional makes an HTTPS request to the ./remoteLogin.asmx web service at whatever Enterprise URL is provided by the user (i.e. http://www.someserver.com/equis/remoteLogin.asmx).
2.EQuIS Professional passes the username and password (encrypted) provided by the user as parameters to the web service.
3.The web service uses the "databaseConnection" connection string (in web.config) to connect to the EQuIS Database.
4.The web service validates the username and password against the ST_USER table in the database.
5.If username and password are valid, the web service looks in ST_ROLE and ST_USER_ROLE to determine if the user is assigned to an ALS role.
6.If the user is assigned to an ALS role, the web service retrieves the connection string from ST_ROLE.CONNECTION_STRING for the appropriate role.
7.The web service compares the ALS connection string with the databaseConnection string (in web.config) to make sure both connection strings point to the same server and database (but not necessarily the same SQL login). This comparison ensures that the user is actually connected to the correct database.
8.If the connection strings use the same server and database, the web service encrypts the ALS connection string and returns the encrypted connection string as the web service response.
9.EQuIS Professional decrypts the ALS connection string and opens a database connection over TCP/IP. SQL Server, and any applicable firewalls must be configured to allow TCP/IP connections from the end-user workstation using the ALS connection string.
Note: Users assigned to the "Admin" role in EQuIS Enterprise also need an ALS role assigned to log in to EQuIS Professional. In builds 7.20.2 and earlier, administrators could make ALS connections regardless of ALS role assignment. In builds 7.20.3 and up, administrators must be members of an ALS role to use an ALS connection.
Copyright © 2023 EarthSoft, Inc. • Modified: 15 Nov 2023