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

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

Whenever you create an SPL program in a PolarDB-O, the 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 statement. Example:

REVOKE EXECUTE ON PROCEDURE list_emp FROM PUBLIC;

Then explicit the EXECUTE privilege on the program can 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.

After 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 such as function, procedure, or package

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

Note A database can have multiple objects of the same type with the same name, but each such object belonging to a different schema in the database. For more information about which object is being referenced by an SPL program in this case, see Database object name resolution.