A database administrator needs to provide access to data from two different tables to multiple group users in order to facilitate ongoing reporting…

A 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).