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
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:
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.GRANT CREATEIN ON SCHEMA DEVL7083 TO PAOLORW; - 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:
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.GRANT CREATEIN ON SCHEMA DEVL7083 TO ROLE ADMINISTRATOR;
Results
