Authorities for DB2 UDB stored procedures and user-defined functions
To create DB2 UDB routines, have certain authorities and privileges, depending on your operating system. Contact your DB2 UDB database administrator to obtain the correct authorities and privileges.
General authorities and privileges for DB2 UDB 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 Use the IBM Distributed Debugger to debug stored procedures Note: This is a separate product.
Table privileges (such as SELECT, IMPORT, UPDATE) for the debug table (DB2DBG.ROUTINE_DEBUG) and the source table. 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
Authorities and privileges for DB2 UDB 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 the workbench uses must have the following privileges:
- CONNECT
- CREATE PROCEDURE
- BINDADD
- CREATEIN privilege on each desired collection ID
For DB2 for z/OS and OS/390 Version 7 and DB2 Universal Database 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 tables listed above. The REXX stored procedure DSNTPSMP performs the writing. Therefore, the workbench 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:
- BINDADD privilege. For example: GRANT BINDADD TO secondary-ID;
- CREATEIN privilege on the collection ID. For example: GRANT CREATEIN COLLECTION collection-ID TO secondary-ID;
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.