Authorities for DB2 stored procedures and user-defined functions

To create DB2® routines, you must have certain authorities and privileges, depending on your operating system. Contact your DB2 database administrator to obtain the correct authorities and privileges.

General authorities and privileges for DB2 for Linux, UNIX and Windows

Action	Required authority
Access target databases	CONNECT
Register stored procedures with a database server	CREATE PROCEDURE And one of the following privileges: SYSADM or DBADM authority CREATEIN privilege for the schema if the schema name of the stored procedure refers to an existing schema IMPLICIT_SCHEMA authority on the database if the implicit or explicit schema name of the stored procedure does not exist. IMPLICIT_SCHEMA authority allows you to implicitly create an object with a CREATE statement and specify a schema name that does not exist. SYSIBM becomes the owner of the implicitly created schema, and PUBLIC is given the privilege to create objects in this schema.
Register user-defined functions with a database server	CREATE FUNCTION
Retrieve rows from a table or view	SELECT
Create a view on a table	SELECT
Run the EXPORT utility	SELECT
Insert an entry in a table or view, and run the IMPORT utility	IMPORT
Change an entry in a table, a view, or one or more specific columns in a table or view	UPDATE
Delete rows from a table or view	DELETE
Test a stored procedure or user-defined function	SYSADM or DBADM EXECUTE or CONTROL privilege for the package that is associated with the stored procedure (for SQL stored procedures or Java™ stored procedures with embedded SQL)
Drop a stored procedure	You must own the stored procedure and have at least one of the following authorities: DELETE privilege DROPIN privilege for the schema or all schemas SYSADM or SYSCTRL authority

Role for debugging routines on DB2 Version 10.1 Fix Pack 2 or later for Linux, UNIX and Windows

To debug routines that are deployed on DB2 Version 10.1 Fix Pack 2 or later for Linux, UNIX and Windows databases, the database user ID that deploys the routine must be a member of the SYSDEBUG role.

Authorities and privileges for DB2 for z/OS or DB2 UDB for z/OS and OS/390

To provide DB2 development features, the workbench accesses DB2 system catalog tables. The user ID that is specified for a connection in the workbench must have the following privileges:

CONNECT
BINDADD
SYSADM or SYSCTRL authority, or
CREATEIN privilege on each desired collection ID
DROPIN privilege to drop routines
ALTERIN privilege to alter routines

For DB2 for z/OS® and OS/390® Version 7 and DB2 for z/OS Version 8, the workbench accesses the following tables:

SYSIBM.SYSROUTINES
SYSIBM.SYSDUMMY1
SYSIBM.SYSROUTINES_SRC
SYSIBM.SYSROUTINES_OPTS
SYSIBM.SYSPACKAGE
SYSIBM.SYSPARMS

Note: The workbench does not directly write to the previous list of tables. The REXX stored procedure DSNTPSMP performs the writing. Therefore, the user ID that is specified for a connection also requires the following authorities:

SELECT privilege
EXECUTE privilege on DSNTPSMP

Using a secondary ID

To use a secondary ID, the following privileges must be granted to the secondary ID:

CONNECT
BINDADD For example: GRANT BINDADD TO secondary-ID;
SYSADM or SYSCTRL authority, or
CREATEIN privilege on each desired collection ID. For example: GRANT CREATEIN COLLECTION collection-ID TO secondary-ID;
DROPIN privilege to drop routines
ALTERIN privilege to alter routines

After these privileges are granted, you can create stored procedures by adding the OWNER keyword to the BIND options. The format for this option is OWNER(secondary ID).

DDNAMES used by DSNTPSMP on workload manager (WLM)

You also need access to specific data sets defined in the WLM environment in which the procedure DSNTPSMP is running. The data set names can vary from site to site, depending on how they are defined in the WLM JCL that they are running.

Feedback