确定当前正在进行的安全检查 (SQL Server) [英] Determining the current security checks being made (SQL Server)

问题描述

我一直讨厌的一件事比 MS SQL Server 中的任何东西都更糟糕的是安全性的工作方式.如果您有趣地查看服务器，那么安全上下文会不断切换，而且通常很难(无论如何对我来说)预测或调试.

One thing that I've always hated more than just about anything in MS SQL Server is the way that security works. The security context constantly switches if you look at the server funny and it's often very hard (for me anyway) to predict or debug.

在处理今天的一个问题时，我虽然，我希望我可以在我的代码中添加一行，以显示当此代码运行时 SQL Server 正在使用的安全上下文."有这样的命令吗?例如，SELECT security_context()

In dealing with an issue today, I though, "I wish I could just add a line to my code that would display the security context that SQL Server is using when this code runs." Does such a command exist? For example, SELECT security_context()

更清楚一点...如果我在一个存储过程中并且因此受到 SP 所有者的安全上下文的影响，那么我希望看到这一点.如果我在 sp_executesql 调用的代码中并且它导致安全性在 SQL Server 服务帐户的上下文中，那么我希望看到它.

To be a little clearer... if I'm in a stored procedure and am therefor subject to the security context of the owner of the SP then I'd like to see that. If I'm in code that was called by sp_executesql and it's causing the security to be under the context of the SQL Server service account, then I would want to see that.

至少到那时我也许能够弄清楚为什么 SQL Server 认为我不应该访问某些东西.

At least then I might be able to figure out why SQL Server thinks that I shouldn't have access to something.

谢谢！

示例

-- Set up
CREATE USER Test_User WITHOUT LOGIN
CREATE TABLE Test_Security_Context (my_id INT)
INSERT INTO Test_Security_Context VALUES (1)
DENY SELECT ON Test_Security_Context TO Test_User
GO
CREATE PROCEDURE Test_Security_Context_SP
AS
  SELECT SUSER_SNAME()
  SELECT * FROM Test_Security_Context  -- This will return ok
  EXEC('SELECT SUSER_SNAME(); SELECT * FROM Test_Security_Context')  -- SUSER_SNAME() will match above but select fails
GO
GRANT EXECUTE ON Test_Security_Context_SP TO Test_User
GO

-- Switch to the new user
SETUSER 'Test_User'
GO

-- Do the test
EXEC Test_Security_Context_SP
GO

-- Clean up
SETUSER
DROP PROCEDURE Test_Security_Context_SP
DROP TABLE Test_Security_Context
DROP USER Test_User
GO

推荐答案

是的，有这样一对视图代表你当前的安全上下文，考虑到所有细节，比如 EXECUTE AS 或代码签名:

Yes, there is such a pair of views that represents your current security context, considering all the details like EXECUTE AS or code signing:

• sys.login_token 用于服务器范围的上下文
• sys.user_token 用于当前数据库上下文

您获得的每一次访问最终都来自这些结果返回中的一行.请注意，某些访问权限来自硬编码角色成员资格(如 db_datareader 数据库角色或 sysadmin 服务器角色).

Every single access you get is ultimately derived from a row in the return of these results. Note that some access are implicit from hard coded role membership (like db_datareader database role or sysadmin server role).

除此之外:

• 所有权链与安全上下文无关:您不在 SP 所有者的上下文"之下.所有权链只是指出，对于与当前对象(SP、View)拥有相同所有者的对象，将跳过访问检查.
• sp_executesql不会以任何方式改变安全上下文

• ownership chaining is not related to security context: you are not under the 'context' of the SP owner. Ownership chaining simply states that access checks are skipped for objects owned by the same owner as current object (SP, View).
• sp_executesql does not change the security context in any way

这篇关于确定当前正在进行的安全检查 (SQL Server)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！