4

I'm having some friction when it comes to showplan permission security.

Say there are 5 databases on a server. And each one has a test/ production split (so 10 total).

There is one sysadmin.

There is another person (non-sysadmin) who is responsible for the data, queries, tables, views, design, structure, indices, etc on DB Number #1. And that alone.

Do you grant SHOWPLAN/ View Server State permissions on DB Number #1 to the person responsible for its design, query optimization, etc? Or does that present an unacceptable security risks to a non sys-admin?

Perhaps it's a bit subjective or a gray area, but I wasn't sure. Are there specific patches/ procedures that can be put in place to mitigate these risks?

Are there any alternatives to SHOWPLAN when optimizing queries and query run time? What about sys.sql_expression_dependencies, also a related View Server State permission, correct?

Some may argue that you have a a DB_Development, DB_Test, and DB_Production split. Perhaps ideally, but when you only have the resources to implement a Test/ Production split, maybe access on Test alone is sufficient risk mitigation?

Paul White
  • 83,961
  • 28
  • 402
  • 634
user45867
  • 1,683
  • 5
  • 22
  • 38

2 Answers2

4

You can read more on the security risk in: SHOWPLAN Permission and Transact-SQL Batches:

Security Note
Users who have SHOWPLAN, ALTER TRACE, or VIEW SERVER STATE permission can view queries that are captured in Showplan output. These queries may contain sensitive information such as passwords. Therefore, we recommend that you only grant these permissions to users who are authorized to view sensitive information, such as members of the db_owner fixed database role, or members of the sysadmin fixed server role. We also recommend that you only save Showplan files or trace files that contain Showplan-related events to a location that uses the NTFS file system, and that you restrict access to users who are authorized to view sensitive information.

Paul White
  • 83,961
  • 28
  • 402
  • 634
Asteway
  • 173
  • 3
  • Yes I saw that. I'm just confused as to what queries exactly would be returning plain text passwords. How to mitigate this. How a 'query trace' or recording queries could be prevented (is that really necessary to tune up queries via the showplan? Unfettered recording to all activity on the database?) Like most technical stuff, devil's in the details. – user45867 Feb 08 '18 at 18:40
  • It wouldn't be so much that they return plain text passwords, but you might be doing a SELECT or a UPDATE on the field referencing a plain text stored password in the WHERE clause – Ali Razeghi - AWS Jul 11 '18 at 22:09
  • 3
    The right answer is to store salted hashes for user databases, and not to use a SQL Server instance as a credential store for other systems. – Emyr Jan 17 '20 at 16:17
0

There are some edge cases where you could use the plan to extract confidential informations.

If you write a

SELECT COUNT(*) from dbo.employees WHERE salery > 5000

and just show the estimated plan for it, you may get a more or less accurate result in the estimated rows, even if you have no permissions or the devs are working with data masking etc. to hide the salery.

Furthermore it could reveal buisness logic that is encapsulated in stored procedures (where you have no show definition permissions and / or the procedures are encrypted) and it could help an attacker to get more informations about your database structure when he is using sql injection (but in this case the SHOWPLAN permission is one of your smaller problems).

Thomas Franz
  • 865
  • 6
  • 11
  • Interesting thought. Well salary data needs to be locked down. But it tends to leak out in aggregated department OPEX reports. I was just thinking if salary was stored as a varchar, you could intuit the average length of field via storage space. But ... it probably wouldn't be. Sounds like a table that should be hidden or not in a reporting database outside of HR/ finance. I think in a Risk/ Reward case, your Intelligence analysts or Database Engineers/ devs absolutely need Showplan on the databases they are working on. Keep private info elsewhere. – user45867 Jun 01 '22 at 22:08