Required Database Permissions

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. 

IBM DB2 (LUW) Database Permissions

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:

• grant select on SYSIBMADM.MON_CURRENT_SQL to user appd4db
• grant select on SYSIBMADM.MON_LOCKWAITS to user appd4db
• grant execute on function SYSPROC.MON_GET_CONNECTION to user appd4db
• grant EXECUTE on function SYSPROC.MON_GET_PKG_CACHE_STMT to user appd4db

Microsoft SQL Server Database Permissions

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

Windows Authentication

If you would like to use a Windows authenticated account to connect to the SQL Server database, the following is required:

• When creating the collector from the Collector Administration -> Add Collector window, do not specify a username and password.
• Configure the AppDynamics for Databases collector windows service to log on as the desired windows account with SQL Server access. 
  1. Go to the Windows Services GUI from Control Panel. 
  2. Select the AppDynamics for Databases collector service, DBTuna Agent - SQL Server,  and right click to display the properties. 
  3. Click on Log On and then enter the credentials for the user.
• Configure the AppDynamics for Databases GUI windows service, DBTuna GUI,  to log on as the desired windows account with SQL Server access. 
  1. Go to the Windows Services GUI from Control Panel. 
  2. Select the AppDynamics for Databases GUI service, DBTuna GUI, and right click to display the properties. 
  3. Click Log On and then enter the credentials for the user.

If you are running your AppDynamics for Databases software on Linux, then you must use SQL Server authentication.

Minimum Permissions Required

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.

1. Using SQL Server Management Studio, create a new login for AppDynamics for Databases.
   

    

2. From the User Mapping tab, map the new user to the master and msdb databases.
   

1. Once you have created the login, give the following privileges to the user, substituting <userName> with the name you specified on the Login - New window:

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]

Mongo Database Permissions

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.

MySQL Database Permissions

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;

Oracle Database Permissions

For versions of Oracle prior to 10g

The permissions required for the Oracle user are:

• CONNECT
• SELECT ANY DICTIONARY

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;

For versions of Oracle 10g and later

The permissions required for the Oracle user are :

• CREATE SESSION
• SELECT_CATALOG_ROLE

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;

PostgreSQL Database Permissions 

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. 

Sybase ASE Database Permissions

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.

SQL Azure Database Permissions

The monitoring user requires  “VIEW DATABASE STATE” permission.

Permissions Required for Explain Plans

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