By default, a stored procedure executes with the privileges of its definer, not its invoker. Such procedures are bound to the schema in which they reside. For example, assume that the following standalone procedure, which can drop any kind of database object, resides in schema scott:
CREATE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2) AS
BEGIN
EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;
END;
Also assume that user jones has been granted the EXECUTE privilege on this procedure. When user jones calls drop_it, as follows, the dynamic DROP statement executes with the privileges of user scott:
SQL> CALL drop_it('TABLE', 'dept');
Also, the unqualified reference to table dept is resolved in schema scott. So, the procedure drops the table from schema scott, not from schema jones.
However, the AUTHID clause enables a stored procedure to execute with the privileges of its invoker (current user). Such procedures are not bound to a particular schema. For example, the following version of 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;
