View the Exhibit and examine the structure of the departments table in SCOTT’s schema.
Examine the following block of code:
7E OR REPLACE PROCEDURE add_dept(
p_idNUMBER, p_name VARCHAR2) IS
BEGIN
INSERT INTO departments VALUES <p_id, p_name, NULL, NULL); END;
/
The above procedure is created by user SCOTT. Another user JONES needs to use the procedure.
Which two statements are true in the above scenario? (Choose two.)
A.
JONES executes the procedure with definer’s rights.
B.
JONES executes the procedure with invoker’s rights.
C.
SCOTT should grant only the execute privilege for the procedure to JONES.
D.
SCOTT should grant both the BXKCOTE privilege for the procedure and insert privilege for the table to
Answer is A and C.
Yep, A and C are correct.
Both definer’s rights and invoker’s rights are a way to control access to the privileges necessary to run a user-created procedure, or program unit. In a definer’s rights procedure, the procedure executes with the privileges of the owner. The privileges are bound to the schema in which they were created. An invoker’s rights procedure executes with the privileges of the current user, that is, the user who invokes the procedure.
For example, suppose user bixby creates a procedure that is designed to modify table cust_records and then he grants the EXECUTE privilege on this procedure to user rlayton. If bixby had created the procedure with definer’s rights, then the procedure would look for table cust_records in bixby’s schema. Had the procedure been created with invoker’s rights, then when rlayton runs it, the procedure would look for table cust_records in rlayton’s schema.
By default, all procedures are considered definer’s rights.
Thanks PO. Explanation cleared my doubt about procedure and access to table
A,C
A, C