EEE SQL Query Executor

As of EEE version 1.84.09 a new SQL Query Executor function is available within EEE.

This screen allows users to run ad-hoc T-SQL queries against the EEE database using an appropriate SQL authentication credential.

Although EEE issue warnings if the query text contains specific keywords, EEE does not analyse the syntax of the query nor attempt to prevent execution of destructible queries.

As such, the following security considerations are recommended:

• Disable the screen for all but specific users who have knowledge of creating and running T-SQL queries. This can be done via user privileges.
• Set up the SQL authentication login with the minimum permissions required (see below).
• Use a complex password for the SQL login in order to improve security.
• Create separate SQL logins for different EEE databases to further compartmentalise access.

A new user privilege, "Allow executing ad-hoc queries against this EEE database" is also required for the user to be able to access the screen in EEE.

SQL Login Permissions

How to restrict access to the SQL login via permissions

Deny visibility of other databases

Initially run the following to hide other database names to logins using the public server role.

USE MASTER
GO
DENY VIEW ANY DATABASE TO PUBLIC
GO

Server roles

Never grant the sysadmin server role to an SQL login intended for use with the SQL Query Executor page. This would yield unrestricted access on the SQL server to the login.

Only the default (public) server role is recommended.

User Mapping

The SQL login must only be mapped to a user on the required EEE database(s). This will restrict the user from executing queries against other databases. e.g. creating a database user john_doe in the EEE database mapped to the SQL login of the same name:

USE EEE
GO
CREATE USER john_doe FOR LOGIN john_doe;

Querying tables

We recommend only granting the minimum permissions required for the user. EEE databases now have an EEESQLQueryBuilder role that automatically grants *read-only* permissions to all non-system tables and views, which can be used for this purpose. e.g. Adding the EEE database user john_doe as a member of the role:

USE EEE
GO
ALTER ROLE EEESQLQueryBuilder ADD MEMBER john_doe;

Use only if required

If write permissions to specific tables are required, you'll need to manually choose to grant X permission (insert, update, delete) to Y table. e.g.

USE EEE
GO
grant insert on dbo.Product to EEESQLQueryBuilder;
GO

Warning: If the role has multiple members, granting permission to the role will grant permission to all its members. To only grant it to specific users, grant it direct to the user rather than the role. i.e.

grant insert on dbo.Product to john_doe; GO

The built-in db_datawriter role can be used to allow insert/update/delete of data from all non-system tables in the database - *if required*. Use with extreme caution!

Executing Stored procedures

Use only if required

In order to only allow executions of specific stored procedures, you'll need to manually grant the execute permission. e.g.

USE EEE
GO
GRANT EXECUTE on usp_Article_Get to EEESQLQueryBuilder;
GO

Warning: Execute permissions are lost if the stored procedure is deleted and re-created.

For permissions to execute all stored procedures, create a new role for the dbo schema and grant execute permissions there.

USE EEE
GO
CREATE ROLE EEESQLQueryBuilder_dbo
GO
GRANT EXECUTE ON SCHEMA::dbo to EEESQLQueryBuilder_dbo
GO
ALTER ROLE EEESQLQueryBuilder_dbo ADD MEMBER john_doe;

Warning: Use this permission with extreme caution. Incorrect usage of EEE stored procedures can have unintended consequences and may cause irreparable damage or loss of data

Database Mail

To allow the SQL login to send emails via SQL Server Database Mail, create and map the login to a user on the msdb database, and grant the DatabaseMailUserRole role.

USE msdb
GO
CREATE USER john_doe FOR LOGIN john_doe
GO
ALTER ROLE DatabaseMailUserRole ADD MEMBER john_doe
GO