EXECUTE Privilege v13

An SPL program (function, procedure, or package) can begin execution only if any of the following are true:

• The current user is a superuser, or
• The current user has been granted EXECUTE privilege on the SPL program, or
• The current user inherits EXECUTE privilege on the SPL program by virtue of being a member of a group which does have such privilege, or
• EXECUTE privilege has been granted to the PUBLIC group.

Whenever an SPL program is created in Advanced Server, EXECUTE privilege is automatically granted to the PUBLIC group by default, therefore, any user can immediately execute the program.

This default privilege can be removed by using the REVOKE EXECUTE command. The following is an example:

REVOKE EXECUTE ON PROCEDURE list_emp FROM PUBLIC;

Explicit EXECUTE privilege on the program can then be granted to individual users or groups.

GRANT EXECUTE ON PROCEDURE list_emp TO john;

Now, user, john, can execute the list_emp program; other users who do not meet any of the conditions listed at the beginning of this section cannot.

Once a program begins execution, the next aspect of security is what privilege checks occur if the program attempts to perform an action on any database object including:

• Reading or modifying table or view data.
• Creating, modifying, or deleting a database object such as a table, view, index, or sequence.
• Obtaining the current or next value from a sequence.
• Calling another program (function, procedure, or package).

Each such action can be protected by privileges on the database object either allowed or disallowed for the user.

Note that it is possible for a database to have more than one object of the same type with the same name, but each such object belonging to a different schema in the database. If this is the case, which object is being referenced by an SPL program? This is the topic of the next section.