Examine the statement: CREATE BOLE hr_admin IDENTIFIED USING pac_mgr.hr_admin_rola_ch9ck; Which three are true about the sec_mgr. hr_admin_role_check procedure?

The correct answer is C. It must use the deflner's rights to enable the role. F. It can use only the DBMS_SESSION. SET_ROLE procedure. G. Its owner SEC_MGR must be granted the execute any procedure role. When Oracle's CREATE ROLE ... IDENTIFIED USING syntax creates a secure application role, the named PL/SQL procedure acts as the gatekeeper - and three strict constraints apply to that procedure. C is correct because the procedure must be compiled with AUTHID DEFINER (definer's…

Configuring and Auditing User Security

Question

Examine the statement:

CREATE BOLE hr_admin IDENTIFIED USING pac_mgr.hr_admin_rola_ch9ck; Which three are true about the sec_mgr. hr_admin_role_check procedure?

Options

AIt must use only one security check to validate the user.
BIt must use the invokcr's rights to enable the role,
CIt must use the deflner's rights to enable the role.
DIt can Include one or more security checks to validate the user.
EIt must contain a SET ROLE statement or a DBMS_SESSION.SET_ROLE call.
FIt can use only the DBMS_SESSION. SET_ROLE procedure.
GIts owner SEC_MGR must be granted the execute any procedure role.

How the community answered

(21 responses)

A
14% (3)
B
29% (6)
C
48% (10)
D
5% (1)
E
5% (1)

Explanation

When Oracle's CREATE ROLE ... IDENTIFIED USING syntax creates a secure application role, the named PL/SQL procedure acts as the gatekeeper - and three strict constraints apply to that procedure.

C is correct because the procedure must be compiled with AUTHID DEFINER (definer's rights), ensuring it executes under SEC_MGR's privileges rather than the calling user's. Invoker's rights would fail because the calling user lacks the authority to enable the role.

F is correct because raw DDL (SET ROLE) cannot be executed inside a stored PL/SQL procedure - only DBMS_SESSION.SET_ROLE is a valid programmatic path to enable the role, making it the only mechanism available.

G is correct because SEC_MGR (the procedure owner) must hold the EXECUTE ANY PROCEDURE system privilege for Oracle's internal security framework to permit a definer's-rights procedure to enable roles on behalf of connecting users.

Why distractors fail: B is the opposite of C - invoker's rights break the mechanism. E is partially right in intent but wrong in mechanics, since SET ROLE is not valid inside stored PL/SQL (F is the precise truth). A is too restrictive and D is too permissive - the procedure has a fixed, constrained pattern it must follow, not an open-ended set of security checks.

Memory tip: Think "DES" - Definer's rights, Execute any procedure (owner needs it), SET_ROLE via DBMS_SESSION only. If any of these three are missing, the application role mechanism breaks.

Topics

#Role-Based Authentication#Definer's Rights#DBMS_SESSION.SET_ROLE#Procedure Security Privileges

Community Discussion

No community discussion yet for this question.

Full 1Z0-116 Practice