Grant Privileges to subprograms

o For stored subprograms and packages the relevant privilege is EXECUTE.
o Grant EXECUTE ON …….to userB;
o Then userB can execute the procedure like userA.procedure.
o A subprogram executes under the privilege set of its owner.
o Like if we have the same table name in the schema of the user who execute the subprogram and the user who owns the subprogram, the table for the user who owns the subprogram will be used.
o A subprogram executes under the privileges that have been granted explicitly to its owner, not via a role.
o The only objects available inside a stored procedure, function, package, or trigger are the ones owned by the owner of the subprogram, or explicitly granted to the owner not via a role the reason of that the set roles applies for the database session only, while GRANT and REVOKE apply to all sessions.
o Because pl/sql is early binding all roles are disabled inside stored procedures and triggers.
o The privileges are checked at compile time, not runtime.
o All roles are disabled inside stored procedures, functions, packages, and triggers.
o GRANT and REVOKE are both DDL statements, they take effect immediately, and the new privileges are recorded in the data dictionary.
o All database sessions will see the new privilege set.