September 11, 2007
Try your hand at the next batch of questions from DB2 9 Fundamentals Certfication Study Guide, reprinted with permission of MC Press.
Question 7
A table named DEPARTMENT has the following columns:
DEPT_ID
DEPT_NAME
MANAGER
AVG_SALARY
Which of the following is the best way to prevent most users from viewing AVG_SALARY data?
A. Encrypt the table’s data
B. Create a view that does not contain the AVG_SALARY column
C. Revoke SELECT access for the AVG_SALARY column from users who should not see AVG_SALARY data
D. Store AVG_SALARY data in a separate table and grant SELECT privilege for that table to the appropriate users
Question 8
In which of the following scenarios would a stored procedure be beneficial?
A. An application running on a remote client needs to be able to convert degrees Celsius to degrees Fahrenheit and vice versa
B. An application running on a remote client needs to collect three input values, perform a calculation using the values provided, and store the input data, along with the results of the calculation in two different base tables
C. An application running on a remote client needs to track every modification made to a table that contains sensitive data
D. An application running on a remote client needs to ensure that every new employee that joins the company is assigned a unique, sequential employee number
Answer 7
The correct answer is B. A view is a virtual table residing in memory that provides an alternative way of working with data that resides in one or more base tables. For this reason, views can be used to prevent access to select columns in a table. While it is possible to encrypt the data stored in the DEPARTMENT table or move the AVG_SALARY data to a separate table (you cannot revoke SELECT privilege for a column), the best solution is to create a view for the DEPARTMENT table that does not contain the AVG_SALARY column, revoke SELECT privilege on the DEPARTMENT table from users who are not allowed to see AVG_SALARY data, and grant SELECT privilege on the new view to users who need to access the rest of the data stored in the DEPARTMENT table.
Answer 8
The correct answer is B. A scalar user-defined function would be the best option for the requirements outlined in answer A; an UPDATE trigger and a DELETE trigger that inserts records into an activity table every time update and delete operations are performed on a table containing sensitive data would be the best way to accomplish the requirements outlined in answer C; and an identity column or sequence could be used to address the requirements shown in answer D.
Trackback Pings
TrackBack URL for this entry:
http://www.ibmdatabasemag.com/blog/main/archives/2007/09/question_7_a_ta.html
« DB2 LUW Performance: Tuning LOGBUFSZ | Main | DB2 LUW Performance: Index Design Tips 1 »
This is a public forum. CMP Media and its affiliates are not responsible for and do not control what is posted herein. CMP Media makes no warranties or guarantees concerning any advice dispensed by its staff members or readers.
Community standards in this comment area do not permit hate language, excessive profanity, or other patently offensive language. Please be aware that all information posted to this comment area becomes the property of CMP Media LLC and may be edited and republished in print or electronic format as outlined in CMP Media's Terms of Service.
Important Note: This comment area is NOT intended for commercial messages or solicitations of business.