Benefits of Packages

o Ability to reference the objects from other Pl/SQL blocks, provide global variables for PL/SQL.
o The package header does not depend on anything; this is the advantage of packages.
o We can change the package body without having to change the header.
o So other objects depend on the header won’t have to be recompiled at all.

Designate a package construct as either public or private

o The package constructs that are declared in the package specification and defined in the package body are public, they are visible and accessible in the package as well as outside of the package.
o The package constructs that are solely defined in the package body are private; they are visible and accessible only within the package.

Identify a package specification and body

o A package has two separate parts – the specification and the body.
o Each of them is stored separately in the data dictionary.
o This enables you to change to definition of a program construct in the package body without causing the Oracle server to invalidate other schema objects that call or reference the program construct.
o Package cannot be local unlike procedures and functions.
o You can drop the body of a package while retaining the package specification.
o You cannot drop the package specification while retaining the package body.
o If you remove the package specification, then the package body is removed.
o This is because a package specification can exist without the package body but the package body cannot exist without the package specification.
o The package specification is required but the package body is optional.

Views in the data dictionary to manage stored objects

o User_objects view contains information about all objects, including stored subprograms, when object is created and last modified, and the Status for the object valid/ invalid.
o User_source contains the original source code for the object.
o User_errors, show errors: contains information about compile errors.
o All_procedures lists all function and procedures along with their associated properties.

Contrast invoker’s rights with definers rights

o The procedure owner is the definer and the user who runs the procedure is the invoker’s
o However if the AUTHID CURRENT_USER clause is used while creating the subprogram (function, procedure, package), the subprogram is executed under the privileges of the executing user and not the owner (invoker’s rights).
o However, references in PL/SQL statements (procedure) are still resolved under the owner’s privilege set so it does not need to be granted to the invoker’s user. P. 438 9i.
o In the case of the invoker’s running the procedure under his privileges he has to grant the DML statement either explicitly or via role.
o A database trigger will always be executed with definer’s rights, and will execute under the privilege set of the schema that owns the triggering table. This also true for a PL/SQL function that is called from a view.

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.