DBA keyword and Execute privilege on the created function

If you create a UDR with the DBA keyword, it is known as a DBA-privileged UDR. You need the DBA privilege to create a DBA-privileged UDR. If you omit the DBA keyword, the UDR is known as an owner-privileged UDR. Other users who have Execute privilege on the UDR run the UDR with the privileges of the user who created the UDR.

DBA-privileged UDRs

For a DBA-privileged UDR, users who do not hold the DBA privilege can run the routine under the following conditions:

  • The DBA grants the user Execute privilege on the UDR.
  • The DBA grants the Execute privilege to PUBLIC so that all users can run the UDR.

When you have Execute privilege on a DBA-privileged UDR, you run the UDR with DBA privileges.

For additional information about DBA-privileged UDRs, see Ownership of Created Database Objects.

Owner-privileged UDRs

In an ANSI-compliant database, before other users can execute an owner-privileged UDR, the owner must grant the Execute privilege, either to individual users, or to roles, or to PUBLIC.

In a database that is not ANSI compliant, any user can run the UDR because PUBLIC is granted the Execute privilege by default. To restrict access to an owner-privileged UDR to specific users, the owner must revoke the Execute privilege on the UDR from PUBLIC, and then grant it to specified users or roles. However, you can prevent privileges on UDRs from being granted to PUBLIC by default. by setting the NODEFDAC environment variable to yes. Then the owner must grant the Execute privilege for the UDR to other users.

For an owner-privileged UDR, all non-qualified database objects that the UDR references are implicitly qualified by the name of the UDR owner. To allow other users to run the UDR, the owner of the UDR must be granted the necessary privileges on any underlying database objects with the WITH GRANT OPTION keyword in the GRANT statement.

The privileges of the owner determine who can run an owner-privileged UDR:

  • If the owner does not have privileges on all of the objects that are referenced by the UDR, then the UDR fails regardless of whether the owner or another user runs it.
  • If the owner has privileges on all referenced objects, but does not have the GRANT option, then only the owner can run the UDR. Other users cannot run the UDR, even if they have Execute privilege on the UDR.
  • If the owner has privileges with the GRANT option on all referenced objects, then the owner and other users who have the Execute privilege can run the UDR. Other users who have Execute privilege run the UDR with the owner's privileges.

If an external C or Java™ language function has a negator function, you must grant the Execute privilege on both the external function and on its negator function before users can execute the external function.