Using Invoker Rights:
By default, the stored procedure executes with the privileges of its definer, not its invoker. These procedures are bound to the schema in which they inhabit. For illustration, suppose that the following stand-alone procedure, that can drop any type of the database object, resides in the schema scott:
CREATE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2) AS
BEGIN
EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;
END;
Also suppose that the user jones has been granted the EXECUTE privilege on this procedure. When the user jones calls drop_it, as shown, the dynamic DROP statement executes with the rights of user scott:
SQL> CALL drop_it('TABLE', 'dept');
Moreover, the unqualified reference to table dept is solved in the schema scott. Therefore, the procedure drops the table from schema scott, not from the schema jones. Though, the AUTHID clause enables the stored procedure to execute with the privileges of its invoker. These procedures are not bound to the particular schema. For illustration, the version below of the drop_it executes with the privileges of its invoker:
CREATE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2)
AUTHID CURRENT_USER AS
BEGIN
EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;
END;