DS0-001 practice question 15 of 15
A database administrator needs to provide access to data from two different tables to multiple group users in order to facilitate ongoing reporting. However, some columns in each table are restricted, and users should not be able to see the values in these columns. Which of the following is the best action for the administrator to take?
Full set: 80 questionsA view is the best solution as it acts as a virtual table created from a SELECT query. The administrator can define the view to join the two tables and explicitly select only the non-restricted columns. This provides column-level security. Permissions can then be granted to user groups on this view, allowing them to query the data for ongoing reporting as if it were a single, simple table, without giving them access to the underlying restricted data. The data in the view is always current as it queries the base tables each time it is accessed.
A. Create a stored procedure: While a stored procedure can return the correct data, it is less flexible for reporting users who may need to run their own ad-hoc queries with different filters and aggregations.
C. Create a csv export: This creates a static, point-in-time copy of the data. It is unsuitable for "ongoing reporting" which requires access to live, up-to-date information.
D. Create a trigger: Triggers are event-driven procedures that execute automatically in response to data modifications (INSERT, UPDATE, DELETE). They are not used for granting read-access to data.
1. Microsoft SQL Server Documentation: "Views". In the section "What are the benefits of using views
" it states
"You can use views as a security mechanism by allowing users to access data through the view
without granting the users permissions to directly access the underlying base tables." This directly supports using views for column-level security.
2. PostgreSQL 14 Documentation: "5.6. Views". The documentation explains
"The user of a view does not need to know the details of how the view is defined... This is also a security aspect: you can use views to give users access to only a subset of the data in a table."
3. Garcia-Molina
H.
Ullman
J. D.
& Widom
J. (2008). Database Systems: The Complete Book (2nd ed.). Pearson Prentice Hall. In Section 8.1.1
"Simple Views
" the text describes how views can be used for security purposes: "A user may be granted permission to query a view
but not to query the base tables from which the view is defined. This arrangement allows the user to see only a limited part of the base table's data." (p. 356).
Keep practicing
Take the free DS0-001 practice test
Ten exam-style questions with answers and explanations, plus the exam facts and study guides.
More questions
Other DS0-001 practice questions
- Question 1Which of the following types of RAID, if configured with the same number and type of disk…
- Question 3Which of the following can be used to protect physical database appliances from damage in…
- Question 4A database administrator is updating an organization's ERD. Which of the following is the…
- Question 5Which of the following is the deployment phase in which a DBA ensures the most recent pat…
- Question 6Which of the following database instances are created by default when SQL Server is insta…
- Question 7A database administrator manages a database server that is running low on disk space. A l…
