Speedgain for Databases

5.5. Grant Minimum required rights 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 server state. You may use the following script in SQL Server Management Studio to create this SQL Server Login.

/*
	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. Linux

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