<< Click to Display Table of Contents >> Navigation:  Administration and Configuration > User Administration > Application-Level Security

Design Role(s) for EQuIS Professional Users

Create Database Login(s)

Create EQuIS Enterprise Role and Assign Permissions

Log In to EQuIS Professional

How Application Level Security Works

 

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:

1.Design Role(s) for EQuIS Professional Users

2.Create Database Login(s)

3.Create EQuIS Enterprise Role and Assign Permissions

4.Log In to EQuIS Professional

 

For more information on the connections and mechanisms involved, see How Application Level Security Works.

 

Design Role(s) for EQuIS Professional Users

 

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_*).

 

For EQuIS functionality, read-only users also need read/write access to the following tables/views:

 

 

For Groups functionality, EarthSoft recommends insert/update/delete on the following tables (optional):

•RT_GROUP

•RT_GROUP_MEMBER

•RT_MTH_ANL_GROUP

•RT_MTH_ANL_GROUP_MEMBER

 

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:

 

 

 

Create Database Login(s)

 

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:

 

TrustServerCertificate=True;

 

 

Read SqlConnection.ConnectionString Property for more about Microsoft SQL connection strings.

 

Example 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;

 

Create EQuIS Enterprise Role and Assign Permissions

 

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.

 

Log In to EQuIS Professional

 

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>

 

 

How Application Level Security Works

 

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 file) 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.