-
Title
Can user account used to launch SQL Tuning be changed? -
Description
When selecting "tune query" option from the Performance Analysis console, it launches SQL tuning session using the quest database account. Thus, anybody who has access to PA, can get unlimited access to the database. Can this behavior be changed, so the user would have to log to the database using his/her own database account?
-
Resolution
The DB username and passwd used to launch SQL Tuning from Performance Analysis cannot be changed. This is the user which is used by PA to log in to the Monitored DB and the same is passed by PA to SQL Tuning application. PA creates a COM object and passes the SQL statement, the userid who executed this statement and the login parameters to log into the Monitored DB. The same DB login parameters are used by Middleware too for DB Services. So if the DB username and passwd that PA uses were to be changed, that will cause Middleware to fail.
However, two points should be taken into consideration:
1. From PA version 6.5 onwards, the DB username and passwd used by PA to log into the Monitored DB are encryptd with a strong encryption (AES encryption) and the password is NOT visible in plain text at all in any config file. Access to PA can easily be restricted from the general public by (a) using a random port number during installation so that the default is not used and hence PA connection is not possible without knowing the Agent port and (b) the quest_launcher user "quest/quest" can be changed - the password can be changed and verify accounts should be run after that. A strong passwd for quest user can be used so that once again general public cannot use PA to view SQL statements from the DB without access being granted.
2. It is not necessary for PA user in Monitored Schema to have DBA role. This role can be revoked. However, special attention should be made to make sure that all privileges are granted as described in the PAO Installation Guide.