Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents

Question

Post reply

Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents

Viewing 4 posts - 166 through 168 (of 168 total)

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

Solomon Rutzky SSCoach Points: 16368 More actions · Answer 1

Jeff Moden wrote:

Solomon Rutzky wrote:

P.S. I will also be presenting on the topic of Module Signing at the upcoming PASS Data Community Summit 2021: Module Signing: Use Certificates to Grant Higher-level Permissions Without Compromising Security. Please keep in mind that the focus will not be on signing assemblies, but you can find info on that topic via the two links I noted above.

Will that include anything on the use of WITH EXECUTE AS OWNER?

Hi Jeff. I usually mention the WITH EXECUTE AS clause in brief as to what it does and why it should be avoided, but I don't usually spend much time on it or go into the specific nuance of the OWNER option (in most cases it merely equates to WITH EXECUTE AS 'dbo' ). The focus of the presentation is showing how to avoid using the WITH EXECUTE AS clause, TRUSTWORTHY ON, and Cross-DB Ownership Chaining.

Take care,

Solomon...

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

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

Thanks for the info, Solomon.

--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)
Intro to Tally Tables and Functions

Solomon Rutzky SSCoach Points: 16368 More actions · Answer 3

Solomon Rutzky wrote:

Jeff Moden wrote:

Solomon Rutzky wrote:

P.S. I will also be presenting on the topic of Module Signing at the upcoming PASS Data Community Summit 2021: Module Signing: Use Certificates to Grant Higher-level Permissions Without Compromising Security. Please keep in mind that the focus will not be on signing assemblies, but you can find info on that topic via the two links I noted above.

Will that include anything on the use of WITH EXECUTE AS OWNER?

Hi Jeff. I usually mention the WITH EXECUTE AS clause in brief as to what it does and why it should be avoided, but I don't usually spend much time on it or go into the specific nuance of the OWNER option (in most cases it merely equates to WITH EXECUTE AS 'dbo' ). The focus of the presentation is showing how to avoid using the WITH EXECUTE AS clause, TRUSTWORTHY ON, and Cross-DB Ownership Chaining.

Take care, Solomon...

Hello again, Jeff. I need to revise my previous answer to your question:

Yes, I do cover WITH EXECUTE AS OWNER in more depth than I originally thought I would be able to. In fact, I have an entire slide and demo (towards the end of the presentation) devoted to the issue of ownership -- object, schema, and database -- with respect to both WITH EXECUTE AS 'dbo' and WITH EXECUTE AS OWNER . I show the impact of changing ownership at each of those three levels and how it affects the validity of any signature for modules using WITH EXECUTE AS { OWNER | 'dbo' } . And I show that IS_OBJECTSIGNED() and sys.fn_check_object_signatures (which merely uses IS_OBJECTSIGNED()) are intended specifically for these ownership issues as there is no other way to have such insight (I mention these 2 functions because, as far as I can tell, while they have been around since SQL Server 2005, nobody has ever known what they actually do, and the documentation doesn't say anything meaningful).

I do not say anything about the security implications of what can be done with elevated permissions (such as in the common case of using OWNER and the owner of the schema being dbo ) as that's more for a general security talk. My point here is that it can change from what was originally there (and hence what was agreed upon) and there is no indication of that happening, unless you are using Module Signing.

Take care,

Solomon...

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

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

So... here we are near the end of the year 2021. SQL Server 2022 has just been announced, miracles of both hardware and software have and continue to occur, MS finally made it so that BULK INSERT and BCP actually work with true CSV (after more than 2 decades of first appearance), and the cloud is actually a viable thing.

And, still... there is no simple, supported, DIR function and there is still no BULK EXPORT in SQL Server. There are tons of other simple but incredibly useful operability missing, as well (don't get me started on the decades old mistakes currently known as REORGANIZE and REBUILD). Instead, people are relegated to rolling their own in one form or another and not just for this type of thing.

It's pretty disgusting that, on a product that costs between 4 and 7 THOUSAND USD per core, people STILL have to resort to the likes of CLR or Powershell or buy some bloody app just to do the simple stuff when I've got a phone that will convert my words to text and a camera that will find people's faces and magical, automatic fail-over from on-premise to cloud.

I know what's next because a whole lot of people have said it before. "Well, geeee-whiz, Jeff! That's what things like SSIS are for!".

Yeah... that just made my point. 🙁

--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)
Intro to Tally Tables and Functions