Multiple choice technology databases

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?

  1. Encrypt the table's data

  2. Create a view that does not contain the AVG_SALARY column

  3. Revoke SELECT access for the AVG_SALARY column from users who should not see AVG_SALARY data

  4. Store AVG_SALARY data in a separate table and grant SELECT privilege for that table to the appropriate users
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Creating a view that excludes the AVG_SALARY column is the best approach - it provides most users access to needed data while hiding sensitive salary information. Views are standard SQL mechanism for column-level access control. Encryption doesn't control access, column-level revoke is complex and error-prone, and a separate table adds unnecessary complexity.

AI explanation

Creating a view that simply omits the AVG_SALARY column is the standard way to restrict access to sensitive data while still letting users query the rest of the table's columns normally. Revoking column-level SELECT privileges is possible in some databases but is less commonly supported and harder to manage than a view; splitting into a separate table adds unnecessary schema complexity; and encryption protects data at rest/in transit but doesn't stop an authorized query from returning decrypted values.

Keep practicing — related questions