A DBA user created and configured this secure application role: Why does the error occur?

Question

Exhibit

Options

• AUser psmith Is connecting outside of the SYSDATE specified.
• BThe set hole hr_admin stuternent must be executed with the dbms. session. set_role procedure.
• Cuser psmith is connecting remotely.
• DThe HR_ADMIN role must be granted to user PSMITH.
• EThe HR.ROLE_CHECK procedure must be created with the AUTHID CURRENT_USERR clause.
• FThe HR.ROLE_CHECK procedure must be created without the AUTHID clause.

• A
  8% (2)
• D
  75% (18)
• E
  4% (1)
• F
  13% (3)

Explanation

Option D is correct because in Oracle, a secure application role (created with IDENTIFIED USING schema.procedure) must still be explicitly granted to the user before the role-verification procedure can activate it. Without GRANT HR_ADMIN TO PSMITH, the procedure has nothing to enable - Oracle cannot set a role for a user who was never granted that role.

Why the distractors are wrong:

• A - A time-based check in the procedure would silently skip role activation, not necessarily throw an error; more importantly, the question implies the error is a grant/privilege issue.
• B - DBMS_SESSION.SET_ROLE is actually the correct mechanism inside a secure role procedure; regular SET ROLE cannot directly enable a secure application role.
• C - Remote connections do not inherently prevent secure application roles from functioning.
• E - AUTHID CURRENT_USER (invoker's rights) would be harmful here, as the calling user may lack privileges needed by the procedure; the default definer's rights is preferred.
• F - Omitting the AUTHID clause defaults to AUTHID DEFINER, which is actually correct for this use case - so this option describes the right behavior, not the problem.

Memory tip: Think of a secure application role as a locked door - the procedure is the key, but the user still needs to be on the guest list (GRANT) before the key works.

No community discussion yet for this question.