What are SHOWPLAN security risks? Should a query optimizer person (not DBA) have access to it?

Question

What are SHOWPLAN security risks? Should a query optimizer person (not DBA) have access to it?

peter_parker

Old Hand

Points: 308
More actions
February 23, 2018 at 3:49 pm

#344168

Strange situation.
Myself and one other person (the DBA/ sysadmin) --- manage a Data Warehouse for a company BI.
I'm a subject matter expert in one domain, so generally I designed and built out the ETL scripts/ table structures/ views for say the Supply Chain department. And by generally I mean I built the entire thing and generally admin the whole BI stack for this domain, from the source systems to the ETL data loads to the end reporting interfaces.
The Data Warehouse is divided into multiple databases for each "Department/ Domain" you can say. Let's just say there are 5 total.
I've been tuning/ optimizing/ refactoring the Supply Chain Database tables for about 2 years. Building new tables and views and optimizing/ improving the query speeds for this database.
However, I never was the overall Data Warehouse DBA. I was not responsible for the hardware, db software, ensuring uptime, database backups, etc.
Now suddenly, the DBA wants to revoke access to all 'View Server State' permissions for me for the Supply Chain Database.
Among other things, this means no access to:
SHOWPLAN
sys.tables and sys.views among other sys.objects
sys.sql_dependencies
Bear in mind that essentially the DBA and I together are a 2-man team that manages this entire Data Warehouse ourselves, and we are already short-handed.
The DBA argues that View Server State presents security risks. With enough know-how, I would be able to sniff out passwords (such as his admin password) possibly by using Tracer queries of some sort or logging automated tasks against the Supply Chain Database, acquiring his password or a higher-level password.
That may be a valid concern (trust issues aside) -- however, is this reasonable?
Especially since I'm tasked with optimizing/ refactoring/ reducing query times of the current Supply Chain Database.
How is it possible to research and reduce query times without SHOWPLAN and viewing the exact planned and actual query plan and time of each step? Isn't that a fundamental tool? I certainly use it quite often.
Obviously viewing dependencies of views (of which there are many to manage) is also very useful without going to 'alter to' of each and every view.
What's the best way forward here?
This topic is so highly technical, it's difficult to assess what is common sense, and what is reasonable, especially to those not mired in these database technicalities. Is there a way to satisfy us both, to allow me to have access to read-only data like SHOWPLAN and sys.objects, without the associated security risks or holes? To patch up those holes somehow? Is the concern justified if I'm charged with many database design and optimization tasks?
Just wanted to seek an expert opinion here. I'm not a seasoned DBA by any means, but I know a great deal about tuning queries - have been working in SQL for about 6 years now.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Alexander Zhang Default port Points: 1497 More actions · Answer 1

peter_parker - Friday, February 23, 2018 3:49 PM
Strange situation.
Myself and one other person (the DBA/ sysadmin) --- manage a Data Warehouse for a company BI.
I'm a subject matter expert in one domain, so generally I designed and built out the ETL scripts/ table structures/ views for say the Supply Chain department. And by generally I mean I built the entire thing and generally admin the whole BI stack for this domain, from the source systems to the ETL data loads to the end reporting interfaces.
The Data Warehouse is divided into multiple databases for each "Department/ Domain" you can say. Let's just say there are 5 total.
I've been tuning/ optimizing/ refactoring the Supply Chain Database tables for about 2 years. Building new tables and views and optimizing/ improving the query speeds for this database.
However, I never was the overall Data Warehouse DBA. I was not responsible for the hardware, db software, ensuring uptime, database backups, etc.
Now suddenly, the DBA wants to revoke access to all 'View Server State' permissions for me for the Supply Chain Database.
Among other things, this means no access to:
SHOWPLAN
sys.tables and sys.views among other sys.objects
sys.sql_dependencies
Bear in mind that essentially the DBA and I together are a 2-man team that manages this entire Data Warehouse ourselves, and we are already short-handed.
The DBA argues that View Server State presents security risks. With enough know-how, I would be able to sniff out passwords (such as his admin password) possibly by using Tracer queries of some sort or logging automated tasks against the Supply Chain Database, acquiring his password or a higher-level password.
That may be a valid concern (trust issues aside) -- however, is this reasonable?
Especially since I'm tasked with optimizing/ refactoring/ reducing query times of the current Supply Chain Database.
How is it possible to research and reduce query times without SHOWPLAN and viewing the exact planned and actual query plan and time of each step? Isn't that a fundamental tool? I certainly use it quite often.
Obviously viewing dependencies of views (of which there are many to manage) is also very useful without going to 'alter to' of each and every view.
What's the best way forward here?
This topic is so highly technical, it's difficult to assess what is common sense, and what is reasonable, especially to those not mired in these database technicalities. Is there a way to satisfy us both, to allow me to have access to read-only data like SHOWPLAN and sys.objects, without the associated security risks or holes? To patch up those holes somehow? Is the concern justified if I'm charged with many database design and optimization tasks?
Just wanted to seek an expert opinion here. I'm not a seasoned DBA by any means, but I know a great deal about tuning queries - have been working in SQL for about 6 years now.

First, I think SHOWPLAN is sort of MUST to query optimizer person. Second, it does bring some security concern. So, there is no easy answer. In the company I'm working for, it depends on data in it.
1. If it need top secure, we use HSM. Even DBA can't access them directly. Instead, DBA need to request a firecall Id, then login a jumpbox, and then access DB server by DBA account.
2. DBA will do everything(code review, release, monitoring, troubleshooting, performance tunning, etc.). Developers are not allowed to touch DB server directly.
3. DBA on PROD, Developers on lower environment. Means, developers have SHOWPLAN previlege in DEV/QA env for tunning purpose.
......

GASQL.com - Focus on Database and Cloud

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

IMHO, the two of you make up the "DBA Team". The other person is the "system" DBA and you are the "application" DBA. Both of you need sysadmin privs to do your jobs effectively and, in the spirit of DevOps, the two you need to make each other aware of what is happening, help each other, be accountable to each other, and generally operate as if you two were one incredible person that knew how to do both of your jobs. Them locking you out is OK provided that they know you will no longer be able to do your job effectively and the "system" DBA does not know what you know and won't be able to do it nearly as effectively as you.
.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

peter_parker Old Hand Points: 308 More actions · Answer 3

Thanks for the replies both. They are both pretty useful and generally my thought process.

It sounds like one possible solution is the Test-Dev and Production duality environments as you mentioned, which would be quite rational and make sense.

I'm afraid the DBA might still have concerns that there would still be security concerns on 'Test/ Development' (I may in some ways be able to reach further than the Supply Chain database, though I have absolutely no interest in doing so) ... but perhaps is enough mitigation.

Out of curiosity, how exactly does HSM work for database security? Does that require a paid tool of some sort?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

peter_parker - Monday, February 26, 2018 2:01 PM
Thanks for the replies both. They are both pretty useful and generally my thought process.
It sounds like one possible solution is the Test-Dev and Production duality environments as you mentioned, which would be quite rational and make sense.
I'm afraid the DBA might still have concerns that there would still be security concerns on 'Test/ Development' (I may in some ways be able to reach further than the Supply Chain database, though I have absolutely no interest in doing so) ... but perhaps is enough mitigation.
Out of curiosity, how exactly does HSM work for database security? Does that require a paid tool of some sort?

We have a Dev, Staging/UAT, PreProd, and Production servers. Only the DBAs (that's usually just me) can deploy code through the environment chain. It's a great idea and works very well although it's a real PITA when it comes to having data in the lesser environments.

Can't help you on the HSM question because I've never used one. We have a security team that manages all of that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alexander Zhang Default port Points: 1497 More actions · Answer 5

peter_parker - Monday, February 26, 2018 2:01 PM
Thanks for the replies both. They are both pretty useful and generally my thought process.
It sounds like one possible solution is the Test-Dev and Production duality environments as you mentioned, which would be quite rational and make sense.
I'm afraid the DBA might still have concerns that there would still be security concerns on 'Test/ Development' (I may in some ways be able to reach further than the Supply Chain database, though I have absolutely no interest in doing so) ... but perhaps is enough mitigation.
Out of curiosity, how exactly does HSM work for database security? Does that require a paid tool of some sort?

HSM, hardware security modules, is generally used in some high level security scenario where the build-in encryption doesn't meet the requirement. In short, it includes USB device, driver and software. The software is provided some dll file which can be loaded into MSSQL as cryptographic provider.
CREATE CRYPTOGRAPHIC PROVIDER xxx FROM File='xxx.dll'

With the provider, you can create symmetric / asymmetric keys for TDE or other purposes. Generally, it also needs some 3rd part software for key management. So, it's not cheap.

GASQL.com - Focus on Database and Cloud