Which action should you take?

Correct Answer for the following Question is given below

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?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 roleCREATE APPLICATION ROLE [MyAppRole] WITH DEFAULT_SCHEMA = [dbo], PASSWORD = ‘1234567’GRANT SELECT ON [dbo].[Books] TO [MyAppRole]GO– Create a login and userCREATE LOGIN [John] WITH PASSWORD = ‘1’, CHECK_POLICY = OFFGOCREATE 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: JohnSELECT * 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: MyAppRoleSELECT * FROM Books– Return content of the tableEXEC sp_unsetapprole @cookie;– The application role is no longer active.– The original context has now been restored.GOSELECT USER_NAME();–Return: JohnREVERT;GOYou 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.

Question:

Which action should you take?

Options:

Create an application role for each department. Add each employee to the appropriate application role.

Create a view to be used by each application. Grant each application permission to use the appropriate view.

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

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

The Correct Answer for this Question is

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

Explanation

The Question – Which action should you take? has been answered correctly and answers for the question is 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

More about these Exams

These Exam Questions and the order of these questions keep changing. but the answers are obviously same. so if you don’t find a question after another we suggest you search it in the search box and we are sure you’ll find it. you can bookmark this site for Quick access in future.

We hope you found it helpful don’t forget to leave a comment if you feel a need to correct or ask we’re always here to help.

you can find more here at Certout

Feel free to contact via comment or email.

Happy Learning

Cheers, Team Certout

Leave a Comment