Designing a database security plan

These questions are based on: 70-229 – Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition Microsoft Self-Test Software Practice Test.


Objective: Designing a database security plan
SubObjective: Define object-level security including column-level permissions by using GRANT, REVOKE and DENY


Item No. 70-229.6.2.4
Single Answer, Multiple Choice


You are the database developer for your company’s Inventory database. The database contains a table named Products. George is a member of the Windows 2000 Sales group.


The Sales group is a member of the Sales database role, which has been granted SELECT permissions to all columns in the Products table. George’s database user account has been denied SELECT permissions on the InStock and Discount columns of the Products table. George now requires access to all the data in the Products table. Your solution must follow recommended security practices.


What should you do?



  1. Add George to the db_datareader database role.
  2. Add George to the db_accessadmin database role.
  3. Grant SELECT permission on the InStock and Discount columns of the Products table for George’s database user account.
  4. Revoke SELECT permission on the InStock and Discount columns of the Products table for George’s database user account.

Answer:



  1. Revoke SELECT permission on the InStock and Discount columns of the Products table for George’s database user account.

Tutorial:
You should revoke SELECT permission on the InStock and Discount columns of the Products table for George’s database user account because George’s database user account has been explicitly denied SELECT permission on the InStock and Discount columns of the Products table. Revoke removes a current permission, whether it is a deny or grant statement, and sets the permissions to a neutral state. George will then inherit the SELECT permission for these columns through his membership in the Sales database role.


You should not add George to the db_datareader database role. This solution will allow him to read all data in the database. But because George’s account has been explicitly denied SELECT permission for the InStock and Discount columns, he will not be able to view them.


You should not add George to the db_accessadmin database role. This solution will allow him to add or remove user IDs. This role cannot revoke an explicitly denied permission, however.


You should not grant George’s database user account SELECT permission on the InStock and Discount columns of the Products table because he can inherit these permissions through his membership in the Sales database role. Permissions should be granted to groups and roles where possible.


Reference:
1. MCSE Training Kit Microsoft SQL Server 2000 Database Design and Implementation – Designing and Administering SQL Server 2000 Security
- Lesson 1: Overview of SQL Server 2000 Security – Authorization

Like what you see? Share it.Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone
cmadmin

ABOUT THE AUTHOR

Posted in Archive|

Comment: