Filtering SQL Server Columns Using Column Level Permissions
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Filtering SQL Server Columns Using Column Level Permissions,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4827字,纯文字阅读大概需要7分钟。
内容图文
Problem
I have a table where some of the columns should not be queryable by all users. How can I filter the data appropriately so that not everyone can select the data? In a previous tip, Filtering Columns in SQL Server Using Views we looked at using Views. In this tip we cover how this can be done with column-level permissions.
Solution
One of the easiest ways to do this is through the use of views. However, if that isn‘t possible, there is another way: column-level permissions. They are a little harder to see and require a bit more diligence to keep track of, but they work just fine. So what‘s the difference between column level permissions and, say, table level permissions?
First, let‘s set up a table to use:
CREATE TABLE dbo.Employee ( EmployeeID INT IDENTITY(1,1), FirstName VARCHAR(20) NOT NULL, MiddleName VARCHAR(20) NULL, SurName VARCHAR(20) NOT NULL, SSN CHAR(9) NOT NULL, Salary INT NOT NULL, CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID) );
And we‘ll go ahead and load it up with a couple of entries for a proof of concept:
INSERT INTO dbo.Employee (FirstName, MiddleName, SurName, SSN, Salary) VALUES (‘John‘, ‘Mark‘, ‘Doe‘, ‘111223333‘, 50000); INSERT INTO dbo.Employee (FirstName, MiddleName, SurName, SSN, Salary) VALUES (‘Jane‘, ‘Eyre‘, ‘Doe‘, ‘222334444‘, 65000);
Let‘s go ahead and set up two users and two roles for this demonstration:
CREATE ROLE HR_Employee; GO CREATE ROLE HR_Intern; GO CREATE USER SalaryPerson WITHOUT LOGIN; GO EXEC sp_addrolemember @membername = ‘SalaryPerson‘, @rolename = ‘HR_Employee‘; GO CREATE USER SummerIntern WITHOUT LOGIN; GO EXEC sp_addrolemember @membername = ‘SummerIntern‘, @rolename = ‘HR_Intern‘; GO
This sets up two levels of users: HR Employees (role HR_Employee, of which SalaryPerson is one) and HR Interns (role HR_Intern, played by SummerIntern). Now, when we normally grant permissions, we do so against the whole object or schema. For instance, this grants SELECT permission against the dbo.Employee table to HR_Employee role members:
GRANT SELECT ON dbo.Employee TO HR_Employee;
Now, we don‘t want interns to have this level of permissions. We only want them to have access to specific columns. There‘s a way to do this. Immediately after the table name, we can specify the columns we want to grant permission to (or DENY, if we needed to do that) within a set of parentheses, like so:
GRANT SELECT ON dbo.Employee (EmployeeID, FirstName, MiddleName, SurName) TO HR_Intern;
Now, if you prefer the GUI, you can do and see the same thing in SQL Server Management Studio, it just takes a little closer eye. Note the difference between Figure 1 (a checkbox, signifying complete permissions against the table) and Figure 2 (a green square, indicating that there are some permissions, but we‘ll have to take a closer look).
(Figure 1)
(Figure 2)
If we click on the Select row, the button for Column Permissions activates. Clicking on that shows that we do indeed have permissions at the column level. Note there is no checkbox beside SSN nor Salary (Figure 3).
(Figure 3)
Therefore, the HR_Intern role cannot query these columns. They can find out that they are there, but they can‘t retrieve data. If you want to see these permissions in action, execute the following snippets. This should work just fine, because HR_Employees can SELECT against the whole table:
EXECUTE AS USER = ‘SalaryPerson‘; GO SELECT * FROM dbo.Employee; GO REVERT; GO
This will fail with a couple of access denied errors, listing the columns the user cannot access:
EXECUTE AS USER = ‘SummerIntern‘; GO SELECT * FROM dbo.Employee; GO REVERT; GO
The errors you should see:
Msg 230, Level 14, State 1, Line 2 The SELECT permission was denied on the column ‘SSN‘ of the object ‘Employee", database ‘MSSQLTips‘, schema ‘dbo‘. Msg 230, Level 14, State 1, Line 2 The SELECT permission was denied on the column ‘Salary‘ of the object ‘Employee", database ‘MSSQLTips‘, schema ‘dbo‘.
This will work, because the columns in the query are accessible to HR_Intern:
EXECUTE AS USER = ‘SummerIntern‘; GO SELECT EmployeeID, FirstName, SurName FROM dbo.Employee; GO REVERT; GO
And that‘s how to restrict using column permissions. Incidentally, you can do the same for DENY. Therefore, if a group of users already have access to columns they shouldn‘t, and you can‘t rework security in this manner, you could use DENY if you had to, like so:
DENY SELECT ON dbo.Employee (SSN, Salary) TO HR_Intern;
Since DENY trumps any other permissions, this will effectively block access to those columns. This should be used as a last resort, obviously, because the use of DENY is not intuitive. And DENY at the column level is another step removed from what we‘re used to when looking at permissions.
原文:http://www.cnblogs.com/junkai/p/4700987.html
内容总结
以上是互联网集市为您收集整理的Filtering SQL Server Columns Using Column Level Permissions全部内容,希望文章能够帮你解决Filtering SQL Server Columns Using Column Level Permissions所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。