Speedgain for Databases

5.5. Grant Minimum rights required for monitoring user

On a database that is to be monitored with Speedgain, the user used must have the appropriate rights. This differs between RDBMS and can be even more specific for some collectors.

5.5.1. Db2

User must have connect and sqladm auth and some read access rights.

For a restrictive database, execute the following grant statements on the monitored database:

db2 grant connect,sqladm on database to user <UserUsedForMonitoring>
db2 grant usage on workload SYSDEFAULTUSERWORKLOAD to user <UserUsedForMonitoring>;
for pkg in $(db2 -x "select pkgname from syscat.packages where pkgschema='NULLID'"); do
    db2 grant execute on package nullid.$pkg to user <UserUsedForMonitoring>;
done
db2 grant select,insert,update,delete on db2monitor.lock_s4dbs_lock_monitor to user <UserUsedForMonitoring>
db2 grant select,insert,update,delete on db2monitor.lock_participants_s4dbs_lock_monitor to user <UserUsedForMonitoring>
db2 grant select,insert,update,delete on db2monitor.lock_participant_activities_s4dbs_lock_monitor to user <UserUsedForMonitoring>
db2 grant select,insert,update,delete on db2monitor.lock_activity_values_s4dbs_lock_monitor to user <UserUsedForMonitoring>

for a non-restrictive database:

db2 grant connect,sqladm on database to user <UserUsedForMonitoring>
db2 grant select,insert,update,delete on db2monitor.lock_s4dbs_lock_monitor to user <UserUsedForMonitoring>
db2 grant select,insert,update,delete on db2monitor.lock_participants_s4dbs_lock_monitor to user <UserUsedForMonitoring>
db2 grant select,insert,update,delete on db2monitor.lock_participant_activities_s4dbs_lock_monitor to user <UserUsedForMonitoring>
db2 grant select,insert,update,delete on db2monitor.lock_activity_values_s4dbs_lock_monitor to user <UserUsedForMonitoring>

5.5.2. PostgreSQL

The user used for PostgreSQL monitoring has to have connect authority and some read auths. Following statements can be used to create a new monitoring role and a new user that gets assigned to that monitoring role.

create role s4dbsmonitoring;
grant connect on database <DatabaseToMonitor> to s4dbsmonitoring;
grant pg_read_all_stats to s4dbsmonitoring;
grant pg_read_all_settings to s4dbsmonitoring;
grant pg_monitor to s4dbsmonitoring;
create user <NewUserName> with encrypted password '<ChoosePW>';
grant s4dbsmonitoring to <NewUserName>;

5.5.3. SQL Server

On a SQL Server instance the user used for monitoring has to be a SQL Server user and be able to connect to any database, read DDLs and view the performance state for server and databases. You may use the following script in SQL Server Management Studio to create this SQL Server Login.

Version 2022 and newer

/*
	In SQL Server Management Studio
	use CTRL + Shift + M to open the Parameter-Dialog and
	replace the default values for username and password with your values
*/
USE [master]
GO
CREATE LOGIN [<LoginName, sysname, usr_s4dbs>] WITH PASSWORD=N'<Password, string, My!SecureP@ssw0rd>', CHECK_EXPIRATION=OFF
GO
ALTER SERVER ROLE [##MS_ServerPerformanceStateReader##] ADD MEMBER [<LoginName, sysname, usr_s4dbs>]
GO
ALTER SERVER ROLE [##MS_DatabaseConnector##] ADD MEMBER [<LoginName, sysname, usr_s4dbs>]
GO
ALTER SERVER ROLE [##MS_DefinitionReader##] ADD MEMBER [<LoginName, sysname, usr_s4dbs>]
GO

Older SQL Server versions

/*
	In SQL Server Management Studio
	use CTRL + Shift + M to open the Parameter-Dialog and
	replace the default values for username and password with your values
*/
USE [master]
GO
CREATE LOGIN [<LoginName, sysname, usr_s4dbs>] WITH PASSWORD=N'<Password, string, My!SecureP@ssw0rd>', CHECK_EXPIRATION=OFF
GO
GRANT CONNECT ANY DATABASE TO [<LoginName, sysname, usr_s4dbs>]
GO
GRANT VIEW ANY DATABASE TO [<LoginName, sysname, usr_s4dbs>]
GO
GRANT VIEW ANY DEFINITION TO [<LoginName, sysname, usr_s4dbs>]
GO
GRANT VIEW SERVER STATE TO [<LoginName, sysname, usr_s4dbs>]
GO

5.5.4. Azure SQL Database

For Azure SQL Database the user needs permissions to connect to the database, read the database state, and read any definition. You can set these permissions either in the master database of a logical SQL Server for all databases mapped to this server or in each Azure SQL Database directly.

You may use below sample scripts to create such a user.

Set Permissions for all databases of a logical server

/*
	In SQL Server Management Studio
	use CTRL + Shift + M to open the Parameter-Dialog and
	replace the default values for username and password with your values
*/
CREATE LOGIN [<LoginName, sysname, usr_s4dbs>] WITH PASSWORD = '<Password, string, My!SecureP@ssw0rd>'
GO
ALTER SERVER ROLE [##MS_ServerStateReader##] ADD MEMBER <LoginName, sysname, usr_s4dbs>
GO
ALTER SERVER ROLE [##MS_DatabaseConnector##] ADD MEMBER <LoginName, sysname, usr_s4dbs>
GO

Set Permissions directly in an Azure SQL Database

/*
	In SQL Server Management Studio
	use CTRL + Shift + M to open the Parameter-Dialog and
	replace the default values for username and password with your values
*/
CREATE USER [<UserName, sysname, usr_s4dbs>] WITH PASSWORD = '<Password, string, My!SecureP@ssw0rd>'
GO
GRANT VIEW DATABASE STATE TO [<UserName, sysname, usr_s4dbs>]
GO
GRANT VIEW DEFINITION TO [<UserName, sysname, usr_s4dbs>]
GO

5.5.5. Linux

User must have ssh login auth, a shell and read access on /proc.