关于SQL Server中的动态SQL,使用动态SQL时不会检查权限

发布于 2024-10-19 16:28:04 字数 535 浏览 3 评论 0原文

什么是动态SQL?

我在一本书上读到,当我们使用动态SQL时,SQL会检查权限。但是当我们不使用它时,就不会进行权限检查。为什么会这样呢?

我创建了一个存储过程,它更新一个表。

create proc steve_SP AS update steveTABLE set eid = 2 where ename = 'henry'

steve_SP 的所有者史蒂夫桌子是史蒂夫。

我授予新用户“Bob”执行 steve_SP 的权限,但没有授予 steveTABLE 的任何权限。

现在,当 Bob 执行该过程时,它就会成功执行。

但是,当过程的代码更改为以下代码(使用动态 SQL)时。

create proc steve_SP AS EXECUTE (update steveTABLE set eid = 2 where ename = 'henry' )

然后,Bob 执行该过程。这次 steve_SP 执行失败。为什么会这样呢?

What is dynamic SQL?

I read in a book that when we use dynamic SQL, SQL checks for permissions. But when we do not use it, then there is no check of permissions. Why so?

I made a stored procedure, which updates a table.

create proc steve_SP AS update steveTABLE set eid = 2 where ename = 'henry'

OWNER of steve_SP & steveTABLE is STEVE.

I gave permission to a new user, 'Bob', to execute steve_SP, but not any permission on steveTABLE.

Now, when Bob executes the procedure, then it executes successfully.

But, when the code of the procedure is changed (dynamic SQL is used) to code below.

create proc steve_SP AS EXECUTE (update steveTABLE set eid = 2 where ename = 'henry' )

And then, Bob executes the procedure. This time the execution of steve_SP FAILS. Why so?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

只是偏爱你 2024-10-26 16:28:05

过程是您控制对表的访问的一种方法。

您可以拒绝用户对表的所有访问路径(除了通过 DML 存储过程),因此在普通 CRUD 对用户不起作用的情况下,过程中的 CRUD 可以起作用是有意义的。

但是,当您运行动态 SQL 时,它实际上是在单独的 SPID(进程)中运行,因此就好像用户在新的查询窗口中运行动态 SQL 中的代码一样。

因此,第一个场景:

create proc steve_SP AS update steveTABLE set eid = 2 where ename = 'henry'

用户执行 steve_SP - 在内部进行更新 -> OK

第二:

create proc steve_SP AS
EXECUTE ('update steveTABLE set eid = 2 where ename = ''henry''')

用户有权访问 steve_SP,因此可以运行。其中运行 EXECUTE,它会启动并在另一个进程中运行它:

update steveTABLE set eid = 2 where ename = 'henry'

直接更新? ->不。您可以使用 WITH EXECUTE AS 选项来解决此问题。

Procedures are a way for you to control access to a table.

You can deny a user all access paths to a table - except via DML stored procs - so it makes sense that CRUD within procs would work where a normal CRUD wouldn't work for the user.

However, when you run dynamic SQL, that is actually run in a separate SPID (process) thus it is as if the user is running the code in the dynamic SQL in a new Query Window.

Therefore, first scenario:

create proc steve_SP AS update steveTABLE set eid = 2 where ename = 'henry'

User executes steve_SP - which internally makes the update -> OK

Second:

create proc steve_SP AS
EXECUTE ('update steveTABLE set eid = 2 where ename = ''henry''')

The user has access to steve_SP, so that runs. In there is runs EXECUTE, which spins up and runs this in another process:

update steveTABLE set eid = 2 where ename = 'henry'

Update directly? -> no. You can use WITH EXECUTE AS options to get around this.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文