What is the best way to control access to each group’s sensitive data?

A middle-tier application server logs on to the database as TrustedUser and submits requests on behalf of application end users. The server is shared by Finance and Human Resources groups and uses ProxyUser query band to identify end users to the database. Each group needs access to its own sensitive data, so the Administrator has created two separate roles with the appropriate permissions.

What is the best way to control access to each group’s sensitive data?
A . Define the roles as external and use the ProxyRole query band to specify one role.
B . Grant both roles to TrustedUser, and add the ProxyRole query band to specify one role.
C . Specify the appropriate role for each end user in a grant connect through statement.
D . Include both roles in the grant connect through statement, and use ProxyRole in the query band to select the appropriate role.

View Answer

Answer: D

Explanation:

The GRANT CONNECT THROUGH statement allows the TrustedUser to act on behalf of multiple end users while securely connecting to the database. By granting both roles (Finance and Human Resources) in this statement, you allow the ProxyUser to switch between roles depending on the query band’s ProxyRole value.

Using the ProxyRole query band, the application can specify which role (Finance or Human Resources) should be used for each specific request. This approach provides flexibility, as the application can dynamically assign the appropriate role to the user based on the query context. Option A (Defining roles as external and using ProxyRole) wouldn’t fully address the need to manage multiple roles dynamically for a shared server.

Option B (Granting both roles to TrustedUser) doesn’t allow for flexible role switching on a per-request basis without the use of GRANT CONNECT THROUGH and could lead to over-granting of permissions.

Option C (Specifying a role for each end user in GRANT CONNECT THROUGH) isn’t as flexible as allowing both roles to be used and dynamically selected through the query band.

Thus, Option D is the most appropriate solution, as it provides both security and flexibility, enabling the application to use the correct role based on the ProxyRole query band for each query submitted.