You are a database administrator on an instance of SQL Server 2008. You maintain a database named Prod that contains information on products manufactured by your company, including product assemblies, bills of materials, work order information, and related component and finished-goods inventory information.
Employees in the inventory department use a custom ASP.NET application to manage the details about products that are received in inventory and finished goods that are transferred from inventory locations to fill and ship customer orders.
Employees in the assembly department access bills of materials, update component inventories, and update work order status in the database using a Windows Forms application.
You want to minimize administrative effort, ensure that employees can only access the necessary data in the Prod database, and prevent users from directly querying database tables.
Which action should you take?
A. Create an application role for each department. Add each employee to the appropriate application role.
B. Create a view to be used by each application. Grant each application permission to use the appropriate view.
C. Create an application role for each department, assign each departmental application role the required permissions, and code each application to authenticate users and activate the appropriate application role
D. Create a database role for each department, assign the required object permissions to each database role, and add each employee to the appropriate database role.
Correct Answer: C
Explanation/Reference:
You should create an application role for each department, assign each departmental application role the required permissions, and code each application to authenticate users and activate the appropriate application role. Application roles allow users to use the database only through a custom application. The users are not directly assigned permissions, but rather are allowed the permissions granted to the application role. Using application roles prevents users from directly accessing database objects.
The following steps make up the process by which an application role switches security contexts:
1. A user executes a client application.
2. The client application connects to an instance of SQL Server as the user.
3. The application then executes the sp_setapprole stored procedure with a password known only to the application.
4. If the application role name and password are valid, the application role is enabled.
5. At this point the connection loses the permissions of the user and assumes the permissions of the application role.
To implement an application role, you create an application role with a password using the CREATE APPLICATION ROLE statement. An application role does not contain users, only a password. Then, you assign the permissions required by the application to the application role. Finally, you code the application to authenticate users and activate the appropriate application role using the sp_setapprole system stored procedure. If users have been granted other database permissions, those permissions are disregarded. In this scenario, you would create an application role for each custom application and grant each application role only the necessary permissions. Then, you would have each application authenticate users and activate the appropriate application role.
Here is an example:
-- Create an application role
CREATE APPLICATION ROLE [MyAppRole] WITH DEFAULT_SCHEMA = [dbo], PASSWORD = ‘1234567’
GRANT SELECT ON [dbo].[Books] TO [MyAppRole]
GO
-- Create a login and user
CREATE LOGIN [John] WITH PASSWORD = ‘1’, CHECK_POLICY = OFF
GO
CREATE USER [John] FOR LOGIN [John]
WITH DEFAULT_SCHEMA = [dbo]
GO
-- The client application connects to an instance of SQL Server as the user.
EXECUTE AS USER = ‘John’
SELECT CURRENT_USER
--Return: John
SELECT * FROM Books
-- Return: Error Msg 229, The SELECT permission was denied on the object ‘Books’.
GO
-- The application then executes the sp_setapprole stored procedure with a password known only to the application.
DECLARE @cookie varbinary(8000);
EXEC sp_setapprole ‘MyAppRole’, ‘1234567’
, @fCreateCookie = true, @cookie = @cookie OUTPUT;
-- The application role is now active.
SELECT USER_NAME();
-- Return: MyAppRole
SELECT * FROM Books
-- Return content of the table
EXEC sp_unsetapprole @cookie;
-- The application role is no longer active.
-- The original context has now been restored.
GO
SELECT USER_NAME();
--Return: John
REVERT;
GO
You should not create a database role for each department, assign the required object permissions to each database role, and add each employee to the appropriate database role. Database roles allow users to directly access the database, not to access the database via a custom application. A database role would allow users to directly access database objects, which should not be allowed in this scenario.
You should not create an application role for each department and add each employee to the appropriate application role. Users cannot be assigned to application roles.
You should not create a view to be used by each application and grant each application permission to use the appropriate view because this would allow users direct database access.