AppDynamics for Databases
2.9.x Documentation
On this page:
Each monitored database requires permissions for the AppDynamics for Databases user so that it can gather important monitoring data. The database user is specified when you are adding a collector. Before adding the collector, ensure a user for the collector is available with the required permissions as stated below. The monitor user must be able to connect to the database remotely from the AppDynamics for Databases machine. The permissions required are database dependent.
For complete AppDynamics for Databases functionality the "TIMESTAMP" monitoring switch of the DB2 server needs to be enabled:
You can enable the "TIMESTAMP" monitoring switch by the following commands:
update dbm cfg using dft_mon_timestamp on;
The monitoring user needs SYSMON authority and connect privileges to monitor. In general, this user must be a part of the sysmon_group.
The monitoring account, appd4db in the examples below, must be granted privileges as follows:
The user account used for monitoring can be a Windows authenticated account (if AppDynamics for Databases is running on Windows) or SQL Server authenticated (if AppDynamics for Databases is running on Windows or Linux).
If you would like to use a Windows authenticated account to connect to the SQL Server database, the following is required:
If you are running your AppDynamics for Databases software on Linux, then you must use SQL Server authentication.
You can use the procedure below to create a user with the minimum permissions required.
If you are using Windows Authentication, leave the username/password blank when you add the collector. You then have to change the Logon account for the Windows AppDynamics for Databases collector service. See Using Windows Authentication to Monitor SQL Server.
Use the following to create a logon user that provides the minimal level of permissions required in order to gain full AppDynamics for Databases/SQL Server functionality.
Viewing Object Information
To view object information on the AppDynamics for Databases Objects tab, map the monitoring user to the databases of interest.
Note: You can execute the following as a batch from a query window in Management Studio. The example shows grants to appdynamics_user, remember to change this if you have set up a different login.
use master GRANT VIEW ANY DATABASE TO appdynamics_user; GRANT VIEW ANY definition to appdynamics_user; GRANT VIEW server state to appdynamics_user; GRANT SELECT ON [sys].[sysaltfiles] TO [appdynamics_user] GRANT execute on sp_helplogins to appdynamics_user GRANT execute on sp_readErrorLog to appdynamics_user
use msdb GRANT SELECT on dbo.sysjobsteps TO [appdynamics_user] GRANT SELECT on dbo.sysjobs TO [appdynamics_user] GRANT SELECT on dbo.sysjobhistory TO [appdynamics_user]
For MongoDB versions prior to 2.6.X, the readAnyDatabase and ClusterAdmin built-in roles are required in order to monitor using AppDynamics for Databases.
For MongoDB 2.6 and later, the new clusterMonitor built-in role in addition to readAnyDatabase is required.
The MySQL user AppDynamics for Databases uses to monitor the MySQL database, must have "SELECT", "PROCESS", and "SHOW DATABASES" privileges on all databases.
If you do not have a suitable existing user, you can use a command such as the following to create a new user; where “host” is the hostname or IP address of the machine running AppDynamics for Databases, and “password” is a suitably secure password:
GRANT SELECT,PROCESS,SHOW DATABASES on *.* to 'appd4db'@'host' identified by 'password'; FLUSH privileges;
The permissions required for the Oracle user are:
To create a user with these permissions, you can run the following SQL. In this SQL, change "password" to a safe and secure password, and change the tablespace names, "users" and "temp" to those available in your Oracle instance.
CREATE user appd4db IDENTIFIED BY password default tablespace users temporary tablespace temp; GRANT CONNECT, SELECT ANY DICTIONARY, resource to appd4db;
The permissions required for the Oracle user are :
To create a user with these permissions, you can run the following SQL. In this SQL, change "password" to a safe and secure password, and change the tablespace names, "users" and "temp" to those available in your Oracle instance.
CREATE USER appd4db IDENTIFIED BY password default tablespace users temporary tablespace temp; GRANT CREATE SESSION, SELECT_CATALOG_ROLE TO appd4db;
The monitoring user must either have a role of superuser or be granted access to the pg_stat_activity table using the technique described below.
The following script allows you to create a SECURITY DEFINER function that is owned by the superuser, and runs the query you want, thus allowing non-superusers to see the contents of pg_stat_activity
by calling the get_sa()
function.
CREATE FUNCTION get_sa() RETURNS SETOF pg_stat_activity AS $$ SELECT * FROM pg_catalog.pg_stat_activity; $$ LANGUAGE sql VOLATILE SECURITY DEFINER; CREATE VIEW pg_stat_activity_allusers AS SELECT * FROM get_sa(); GRANT SELECT ON pg_stat_activity_allusers TO public;
The monitoring user must also be able to connect remotely to the PostgreSQL instance from the AppDynamics for Databases machine.
For complete AppDynamics for Databases functionality, the monitoring user requires the sa_role and mon_role privilege.
To create a new dedicated user for AppDynamics for Database, you can use following sample user creation script. Before running the script, change "password" to a more secure value.
exec sp_addlogin 'appd4db', 'password', @defdb='master', @deflanguage='us_english', @fullname='appd4db monitoring account', @auth_mech = 'ANY' go exec sp_locklogin 'appd4db', 'unlock' go exec sp_role 'grant', 'sa_role', 'appd4db' go exec sp_role 'grant', 'mon_role', 'appd4db' go
Also, the following configuration parameters must be set to 1 (true) in order to monitor the Sybase ASE database with AppDynamics for Databases: "enable monitoring", "wait event timing", "SQL batch capture", and "object lockwait timing". You should also set "max SQL text monitored" to at least 8192 (8kB).
Here is an example of the commands required to configure these settings:
sp_configure "enable monitoring", 1 go sp_configure "wait event timing", 1 go sp_configure "SQL batch capture", 1 go sp_configure "object lockwait timing", 1 go sp_configure "max SQL text monitored", 8192 go
If the value for "max SQL text monitored" was previously less than 4096, then increasing this setting will require that you restart the Sybase ASE instance.
The monitoring user requires “VIEW DATABASE STATE” permission.
AppDynamics for Databases can generate explain plans within its SQL drilldown window. To enable this functionality, you must have a plan table accessible to the AppDynamics for Databases schema user. You can create this plan table with the following command from sqlplus when logged on as the AppDynamics for Databases monitoring user:
Windows:
@?\rdbms\admin\utlxplan.sql
Linux:
@?/rdbms/admin/utlxplan.sql
5 Comments
Tandava Krishna Dutta
for DB2 10.5, the following would help
Create new user account called appd4db. Ensure that it has SYSMON authority.
grant select on SYSIBMADM.mon_current_sql to user appd4db
grant select on SYSIBMADM.SNAPSTMT to user appd4db
grant select on SYSIBMADM.SNAPAPPL_INFO to user appd4db
grant execute on function SYSPROC.MON_GET_PKG_CACHE_STMT to user appd4db
Jacqueline Finney
Hi Tandav,
Thanks for your input! I'll run your suggestion by the experts and get back to you in a day or two.
Regards,
Jacquie
Jacqueline Finney
Hi Tandav,
I'll add the info you provided. Thanks!
Cheers,
Jacquie
Unknown User (newmansteve@johndeere.com)
Where are the Required Database Permissions for Mongo?
Jacqueline Finney
My understanding is that users don't usually password protect their MongoDB databases, however I'm double-checking this with the developer and will get back to you asap.
Cheers,
Jacquie
Senior Technical Writer - AppDynamics for Databases