EEE SQL Query Executor - Revision history

Nivin at 23:18, 20 December 2023

2023-12-20T23:18:47Z

← Older revision		Revision as of 23:18, 20 December 2023
Line 71:		Line 71:
	=== Executing Stored procedures ===		=== Executing Stored procedures ===

	~~'''~~Use only if required~~'''~~		=====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.		In order to only allow executions of specific stored procedures, you'll need to manually grant the execute permission. e.g.
Line 96:		Line 96:

	=== Database Mail ===		=== Database Mail ===
	~~'''~~Use only if required~~'''~~		=====Use only if required=====

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

Nivin: /* Querying tables */

2023-12-20T23:18:26Z

Querying tables

← Older revision		Revision as of 23:18, 20 December 2023
Line 52:		Line 52:
	</nowiki>		</nowiki>

	~~'''~~Use only if required~~'''~~		=====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.		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.

Nivin at 23:16, 20 December 2023

2023-12-20T23:16:46Z

← Older revision		Revision as of 23:16, 20 December 2023
Line 96:		Line 96:

	=== Database Mail ===		=== Database Mail ===
			'''Use only if required'''

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

Nivin: /* Querying tables */

2023-12-20T23:15:24Z

Querying tables

← Older revision		Revision as of 23:15, 20 December 2023
Line 63:		Line 63:
	'''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.		'''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.

	<nowiki>		<nowiki>
	grant insert on dbo.Product to john_doe;		grant insert on dbo.Product to john_doe;
	GO</nowiki>		GO</nowiki>

	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!		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 ===		=== Executing Stored procedures ===

Nivin: /* Database role membership */

2023-12-20T23:14:39Z

Database role membership

← Older revision		Revision as of 23:14, 20 December 2023
Line 43:		Line 43:
	</nowiki>		</nowiki>

	=== ~~Database role membership~~ ===		=== 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:		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:

	<nowiki>USE EEE		<nowiki>USE EEE
Line 66:		Line 66:
	grant insert on dbo.Product to john_doe;		grant insert on dbo.Product to john_doe;
	GO</nowiki>		GO</nowiki>

			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 ===		=== Executing Stored procedures ===

Nivin: /* User Mapping */

2023-12-20T23:11:32Z

User Mapping

← Older revision		Revision as of 23:11, 20 December 2023
Line 42:		Line 42:
	CREATE USER john_doe FOR LOGIN john_doe;		CREATE USER john_doe FOR LOGIN john_doe;
	</nowiki>		</nowiki>


	=== Database role membership ===		=== Database role membership ===

Nivin: /* Database role membership */

2023-12-20T23:11:18Z

Database role membership

← Older revision		Revision as of 23:11, 20 December 2023
Line 51:		Line 51:
	GO		GO
	ALTER ROLE EEESQLQueryBuilder ADD MEMBER john_doe;		ALTER ROLE EEESQLQueryBuilder ADD MEMBER john_doe;
	GO
	</nowiki>		</nowiki>

Nivin at 23:10, 20 December 2023

2023-12-20T23:10:45Z

← Older revision		Revision as of 23:10, 20 December 2023
Line 36:		Line 36:
	=== User Mapping ===		=== User Mapping ===

	The SQL login must only be mapped to a user on the required EEE database(s). This will restrict the user from ~~accessing~~ other databases.		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:

			<nowiki>USE EEE
			GO
			CREATE USER john_doe FOR LOGIN john_doe;
			</nowiki>


	=== Database role membership ===		=== Database role membership ===

	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.		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:

			<nowiki>USE EEE
			GO
			ALTER ROLE EEESQLQueryBuilder ADD MEMBER john_doe;
			GO
			</nowiki>

	'''Use only if required'''		'''Use only if required'''
Line 51:		Line 63:
	GO</nowiki>		GO</nowiki>

	'''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 to the user rather than the role. i.e.		'''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.

	<nowiki>		<nowiki>

Nivin at 23:06, 20 December 2023

2023-12-20T23:06:36Z

← Older revision		Revision as of 23:06, 20 December 2023
Line 11:		Line 11:
	* Use a complex password for the SQL login in order to improve security.		* 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.		* 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 ==		== SQL Login Permissions ==
Line 30:		Line 32:
	'''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.		'''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.		Only the default (public) server role is recommended.

	=== User Mapping ===		=== User Mapping ===

	The SQL login must only be mapped to as a user on the ~~appropriate~~ EEE database. This will restrict the user from accessing other databases.		The SQL login must only be mapped to a user on the required EEE database(s). This will restrict the user from accessing other databases.

	=== Database role membership ===		=== Database role membership ===

	We recommend only granting the minimum permissions required for the ~~login:~~		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.

	* db_datareader - Grant membership to this role to allow reading data from all non-system tables in the database		'''Use only if required'''
	* db_datawriter - '''~~Only~~ if required'''~~. Grant membership to this role to allow insert/update/delete of data from all non-system tables in the database~~

	~~The above roles affect all non-system tables in the database. In order to only allow specific~~ permissions to specific tables, you'll need to manually choose to grant X permission to Y table. e.g.		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.

	<nowiki>USE EEE		<nowiki>USE EEE
	GO		GO
	~~GRANT SELECT~~ on Product to ~~sqlqueryexecutor~~		grant insert on dbo.Product to EEESQLQueryBuilder;
			GO</nowiki>

			'''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 to the user rather than the role. i.e.

			<nowiki>
			grant insert on dbo.Product to john_doe;
	GO</nowiki>		GO</nowiki>

Line 58:		Line 65:
	<nowiki>USE EEE		<nowiki>USE EEE
	GO		GO
	GRANT EXECUTE on usp_Article_Get to ~~sqlqueryexecutor~~		GRANT EXECUTE on usp_Article_Get to EEESQLQueryBuilder;
	GO</nowiki>		GO</nowiki>

	'''Warning''': Execute permissions are lost if ~~the~~ the stored procedure is deleted and re-created.		'''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.		For permissions to execute '''all''' stored procedures, create a new role for the dbo schema and grant execute permissions there.
Line 67:		Line 74:
	<nowiki>USE EEE		<nowiki>USE EEE
	GO		GO
	CREATE ROLE ~~sqlqueryexecutor_dbo~~		CREATE ROLE EEESQLQueryBuilder_dbo
	GO		GO
	GRANT EXECUTE ON SCHEMA::dbo to ~~role_exec_dbo~~		GRANT EXECUTE ON SCHEMA::dbo to EEESQLQueryBuilder_dbo
	GO		GO
	ALTER ROLE ~~sqlqueryexecutor_dbo~~ ADD MEMBER ~~sqlqueryexecutor~~		ALTER ROLE EEESQLQueryBuilder_dbo ADD MEMBER john_doe;
	</nowiki>		</nowiki>

Line 82:		Line 89:
	<nowiki>USE msdb		<nowiki>USE msdb
	GO		GO
	CREATE USER ~~sqlqueryexecutor~~ FOR LOGIN ~~sqlqueryexecutor~~		CREATE USER john_doe FOR LOGIN john_doe
	GO		GO
	ALTER ROLE DatabaseMailUserRole ADD MEMBER ~~sqlqueryexecutor~~		ALTER ROLE DatabaseMailUserRole ADD MEMBER john_doe
	GO		GO
	</nowiki>		</nowiki>

Nivin: Nivin moved page Eee sql query executor to EEE SQL Query Executor: fixed capitalisation

2019-11-21T04:49:47Z

Nivin moved page Eee sql query executor to EEE SQL Query Executor: fixed capitalisation

← Older revision	Revision as of 04:49, 21 November 2019
(No difference)