1Z0-052 · Question #24
The HR user creates a stand-alone procedure as follows and grants the EXECUTE privilege on the procedure to many database users: CREATE OR REPLACE PROCEDURE create_dept ( v_deptno NUMBER, v_dname…
The correct answer is B. Create the procedure with invoker's right. Switching the procedure to invoker's rights causes it to execute under the calling user's privilege context, so users lacking INSERT on DEPARTMENTS will be denied when the procedure attempts the INSERT.
Question
The HR user creates a stand-alone procedure as follows and grants the EXECUTE privilege on the procedure to many database users:
CREATE OR REPLACE PROCEDURE create_dept ( v_deptno NUMBER, v_dname VARCHAR2, v_mgr NUMBER, v_loc NUMBER) BEGIN INSERT INTO hr.departments VALUES (v_deptno, v_dname, v_mgr, v_loc); END; The users having permission to execute the procedure are able to insert records into the DEPARTMENTS table even though they do not have the INSERT privilege on the table. You want only those users who have privileges on the DEPARTMENTS table to be able to execute the procedure successfully. What would you suggest to the PL/SQL developers to achieve this?
Options
- ACreate the procedure with definer's right.
- BCreate the procedure with invoker's right.
- CGrant the EXECUTE privilege with GRANT OPTION on the procedure to selected users.
- DCreate the procedure as part of a PL/SQL package and grant the EXECUTE privilege on the package
How the community answered
(44 responses)- A25% (11)
- B55% (24)
- C16% (7)
- D5% (2)
Why each option
Switching the procedure to invoker's rights causes it to execute under the calling user's privilege context, so users lacking INSERT on DEPARTMENTS will be denied when the procedure attempts the INSERT.
Definer's rights is the default behavior already in place, running the procedure under the owner's (HR's) privileges - this is the root cause of the problem because it allows unprivileged users to insert rows through the procedure.
With invoker's rights (AUTHID CURRENT_USER), Oracle resolves object references and enforces privileges using the calling user's security context at runtime - a user who does not have INSERT on HR.DEPARTMENTS will receive an insufficient privileges error when the procedure executes the INSERT statement, effectively restricting access to only those with the required table privilege.
Granting EXECUTE WITH GRANT OPTION only controls which users can further delegate EXECUTE rights on the procedure; it does not change the privilege model used during procedure execution.
Wrapping the procedure in a PL/SQL package does not alter the rights model - the package still uses definer's rights by default, preserving the same unintended privilege escalation for users without table-level INSERT.
Concept tested: Oracle PL/SQL invoker's rights vs definer's rights AUTHID clause
Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/invokers-rights-and-definers-rights-clauses.html
Topics
Community Discussion
No community discussion yet for this question.