Granting the CREATEIN privilege on schemas for stored procedures

When a stored procedure is created, it is explicitly or implicitly qualified by a schema. Users must have the required CREATEIN privilege on the schema before they can create stored procedures.

About this task

Begin general-use programming interface information. Many users create stored procedures in the same schema at an application level. These users need the CREATEIN privilege on the schema. You can grant this privilege to a secondary ID or role that is associated with individual users. Those users can then issue a SET CURRENT SQLID statement to the secondary ID or role prior to creating stored procedures in the schema.

Procedure

To grant the CREATEIN privilege on schemas for stored procedures:

Issue a GRANT statement with the appropriate options, depending on whether you are granting the privilege to a secondary ID or to a role.

For a secondary ID, issue a GRANT statement with the CREATEIN ON SCHEMA clause. Specify the schema name and secondary ID. For example, assume that you want a user with the secondary ID of PAOLORW to be able to create stored procedures in a schema named DEVL7083. To give this user the necessary privilege, you can issue this statement:
```
GRANT CREATEIN ON SCHEMA DEVL7083 TO PAOLORW;
```
If the ID PAOLORW issues a CREATE PROCEDURE statement without having the required CREATEIN privilege on the schema, an error occurs, and the procedure is not created.
For a role, issue a GRANT statement with the CREATEIN ON SCHEMA clause. Specify the schema name and the role that will be in effect when the stored procedure is created. (For users to be associated with a role, the trusted context that links them to the role needs to be defined with the ROLE AS OBJECT OWNER AND QUALIFIER clause.) For example, assume that you want to grant the CREATEIN privilege to a role named ADMINISTRATOR so that users who are associated with the ADMINISTRATOR role can create stored procedures in a schema named DEVL7083. To grant this privilege, you can issue this statement:
```
GRANT CREATEIN ON SCHEMA DEVL7083 TO ROLE ADMINISTRATOR;
```
If a user who is associated with the role named ADMINISTRATOR issues a CREATE PROCEDURE statement without having the required CREATEIN privilege on the schema, an error occurs, and the procedure is not created.

Results

After a secondary ID or role is granted the CREATEIN privilege for a stored procedure and then creates a stored procedure, that ID or role is the owner of that stored procedure. End general-use programming interface information.

End general-use programming interface information.