Which two statements are true in the above scenario?

View the Exhibit and examine the structure of the departments table in SCOTT’s schema.

Examine the following block of code:
CREATE OR REPLACE PROCEDURE add_dept(
p_id NUMBER, 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.)

View the Exhibit and examine the structure of the departments table in SCOTT’s schema.

A.
JONES executes the procedure with definer’s rights.

B.
JONES executes the procedure with invoker’s rights.

C.
SCOTT should grant only theexecute privilege for the procedure to JONES.

D.
SCOTT should grant both the BXKCOTE privilege for the procedure and insert privilege for the
table to

Show Hint

← Previous question

Next question →

User

Using Invoker’s Rights Versus Definer’s Rights (AUTHID Clause)
By default, stored procedures and SQL methods execute with the privileges of their owner, not their current user. Such definer’s rights subprograms are bound to the schema in which they reside, allowing you to refer to objects in the same schema without qualifying their names. For example, if schemas HR and OE both have a table called departments, a procedure owned by HR can refer to departments rather than HR.departments. If user OE calls HR’s procedure, the procedure still accesses the departments table owned by HR.

If you compile the same procedure in both schemas, you can define the schema name as a variable in SQL*Plus and refer to the table like &schema..departments. The code is portable, but if you change it, you must recompile it in each schema.

A more maintainable way is to use the AUTHID clause, which makes stored procedures and SQL methods execute with the privileges and schema context of the calling user. You can create one instance of the procedure, and many users can call it to access their own data.

Such invoker’s rights subprograms are not bound to a particular schema. The following version of procedure create_dept executes with the privileges of the calling user and inserts rows into that user’s departments table:

Example 8-13 Specifying Invoker’s Rights With a Procedure

CREATE OR REPLACE PROCEDURE create_dept (
v_deptno NUMBER,
v_dname VARCHAR2,
v_mgr NUMBER,
v_loc NUMBER)
AUTHID CURRENT_USER AS
BEGIN
INSERT INTO departments VALUES (v_deptno, v_dname, v_mgr, v_loc);
END;
/
CALL create_dept(44, ‘Information Technology’, 200, 1700);

Advantages of Invoker’s Rights
Invoker’s rights subprograms let you reuse code and centralize application logic. They are especially useful in applications that store data using identical tables in different schemas. All the schemas in one instance can call procedures owned by a central schema. You can even have schemas in different instances call centralized procedures using a database link.

Consider a company that uses a stored procedure to analyze sales. If the company has several schemas, each with a similar SALES table, normally it would also need several copies of the stored procedure, one in each schema.

To solve the problem, the company installs an invoker’s rights version of the stored procedure in a central schema. Now, all the other schemas can call the same procedure, which queries the appropriate to SALES table in each case.

You can restrict access to sensitive data by calling from an invoker’s rights subprogram to a definer’s rights subprogram that queries or updates the table containing the sensitive data. Although multiple users can call the invoker’s rights subprogram, they do not have direct access to the sensitive data.