SESSION Details

Limiting permissions in Dynamic SQL with Execute As

A tool many application developers use is dynamic SQL. But as DBAs we cringe at the security hole that opens. DBAs are well acquainted with horror stories of SQL injection, where hackers inject harmful SQL into web page text boxes: drop tables, create logins, you name it.

But there are some situations where dynamic SQL provides needed flexibility. How can we take advantage of dynamic SQL while reducing the security risks? Running dynamic SQL with a low permission user is the main tool for protecting against SQL injection

In this talk we will identify scenarios where dynamic SQL is helpful and how it allows harmful SQL to be run. I will then show how to force the dynamic SQL to run as a low permission user. We will create a low permission user in the database. We will run the dynamic code as that low permission user using the “Execute AS” command in T-SQL. We will demonstrate how this prevents the execution of high permission code.

This is one of many tools that need to be in place to secure an application using dynamic SQL.

Feedback link https://sqlb.it/?7109

Starts: 10:50 12th Mar 2022
Ends: 11:10 12th Mar 2022

Q&As

Short Description
- Dynamic SQL is great, until it is used to hack your site. In this session we demonstrate how using the T-SQL "Execute AS" command can limit what can be run using dynamic SQL.
Other technologies
- T-SQL

Speakers

Russ Loski

Tags

Platform AgnosticDataOpsData IntegratorDevelopingMonitoringSecurityTestingDatabase EngineSQL 2019BeginnerHow

The SQL Bits Story

SQLBits was formed in 2007 by a group of volunteers who were passionate about the SQL Server product suite and wanted to provide much-needed community-driven education to the data community.

As one of the largest data platform conferences in the world, we offer more opportunities to a wider audience.

15 Years

We’ve grown and expanded a lot since 2007.

2500 Participants

SQLBits is the best place to meet fellow data professionals.

82 Countries

We welcome data professionals from all over the globe.

1140 recorded sessions

All the live sessions are recorded and offered for free, year round.

Experience the SQLBits Conference

Previous
Next

Want to be part of the SQLBits community?

Attend the London conference in-person or virtually on 

March 8-12, 2022 at ExCel London, UK.