User Permissions

Question

User Permissions

Khalid-151305

Right there with Babe

Points: 737
More actions
April 4, 2005 at 1:45 pm

#87182

I am confused between answer A and D. What would be the best answer and why?
Q.You are the database developer for your company`s Accounting database. The database contains a table named Employees. Tom is a member of the Accounting department. Tom`s database user account has been denied SELECT permissions on the salary and BonusPercentage columns of the Employees table. Tom has been granted SELECT permissions on all other columns in the table. Tom now requires access to all the data in the Employees table.
What should you do?
Possible Answers

A. Revoke SELECT permissions on the Salary and BonusPercentage columns of the employees table for Tom`s database user account
B. Add Tom to the db_datareader database role
C. Add Tom to the db_accessadmin database role
D. Grant SELECT permissions on the salary and bonusPercentage columns of the Employees table for Tom`s databas

Viewing 4 posts - 1 through 3 (of 3 total)

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

As with the other exam-ish question you posted... if you had to pick an answer (A or D), which one would you pick, and why? If you are confused between the two answers, explain your reasoning as to why you think both A and D might be valid answers.

A lot of times I find that as I sit down to explain my confusion, I uncover the issue I had with understanding it in the first place. That's what I'm trying to get you to do here.

K. Brian Kelley
@kbriankelley

Khalid-151305 Right there with Babe Points: 737 More actions · Answer 2

Hi

I think D is the best choice becuase it Grants permission on required columns while option A revokes permissions and do not grant permission again?

Khalid

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

D is correct and your reasoning is sound. Permissions in SQL Server are actually at the column level. Therefore, when the DENY is run blocking access to Salary and BonusPercentage, SQL Server actually gets rid of the SELECT permission on the whole Employee table. It replaces that SELECT permission with the two DENY entries for Salary and Bonus Percentage along with SELECT permissions for every other column. You can test this by doing the following in a test database:

CREATE TABLE Employee (
EmployeeID int,
Salary int,
BonusPercentage int,
FullName varchar(50)
)
GO

Create a login for Tom, then grant Tom database access. Execute the following:

GRANT SELECT ON Employee TO Tom
GO

EXEC sp_helprotect 'Employee'
GO

Now, execute the DENY statement:

DENY SELECT ON Employee (Salary, BonusPercentage) TO Tom
GO

EXEC sp_helprotect 'Employee'
GO

You can see how SQL Server broke out the permissions. Now, if you just revoke select on Salary and BonusPercentage, Tom is never given access to those columns again. You can see this by:

REVOKE SELECT ON Employee (Salary, BonusPercentage) TO Tom

EXEC sp_helprotect 'Employee'
GO

SQL Server didn't restore the "table-level" select. It simply undid the DENY on Salary and BonusPercentage. That means Tom doesn't have access to those columns (based on how the question reads). Now, clean up the permissions:

REVOKE SELECT ON Employee  FROM Tom
GO

EXEC sp_helprotect 'Employee'
GO

With everything clean, set the permissions again:

GRANT SELECT ON Employee TO Tom
DENY SELECT ON Employee (Salary, BonusPercentage) TO Tom
GO

EXEC sp_helprotect 'Employee'
GO

Now issue the one GRANT SELECT and watch how everything is fixed.

GRANT SELECT ON Employee TO Tom
GO

EXEC sp_helprotect 'Employee'
GO

That's why D is correct.

K. Brian Kelley
@kbriankelley