Manage user account permissions

Before you create any stored procedures, familiarize yourself with the required account permissions necessary to create and manage these objects. The admin user has full permission to manage and execute stored procedures. The database owner also has full permission to objects within the database that the owner owns. For release 7.0.3 and later systems that support multiple schemas in a database, the schema owner also has full permission to objects within that schema. In addition, the owner of the stored procedure has permission to manage and execute the stored procedure.

As the admin user or any user who has account management permissions, you can grant other users permission to create, manage, or execute the stored procedures on a Netezza Performance Server system. You can assign permissions by using the GRANT and REVOKE SQL commands.

If you use SQL commands to manage account permissions, the database to which you are connected has meaning when you run the GRANT command. If you are connected to the SYSTEM database, the privilege applies to all databases. If you are connected to a specific database, the privilege applies only within that database.

You can use a fully qualified object notation to set the scope of object privileges from any database. The fully qualified object notation has the format database.schema.object, where:

  • database is a specific database name or the keyword ALL to grant the privilege across all databases.
  • schema is a specific schema name or the keyword ALL for all schemas in the specified database value.
  • object is the class PROCEDURE for all stored procedures, or a full signature such as customer(VARCHAR(20)).
Make sure that you specify the correct signature for the object, including the sizes for numeric and string data types, otherwise you receive an error similar to the following example:
ERROR:  GrantRevokeCommand: existing UDX NAME(ARGS) differs in size of 
string/numeric arguments

You can use a similar format to set the scope of administrative privileges from any database. The scope notation has the format IN database.schema, where:

  • database is a specific database name or the keyword ALL to grant the privilege across all databases. You can omit a database name.
  • schema is a specific schema name or the keyword ALL for all schemas in the specified database. If you omit the database name, the scope is for ALL schemas or all the schemas of the specified name.

Grant Create Procedure administration permission

To grant Create Procedure administration permission, use the following command:
GRANT CREATE PROCEDURE TO entity;
For example, the following command grants Create Procedure permissions to the user bsmith:
GRANT CREATE PROCEDURE TO bsmith;

If you run this command while connected to the SYSTEM database, the privilege is granted to the user in all databases. If you are connected to a specific database, the privilege is granted to the user in only that database.

Using the release 7.0.3 fully qualified name form, you could also issue the command from any database and specify an IN clause to set the privilege scope. For example, the following command grants the user Create Procedure privilege in all databases and schemas:

GRANT CREATE PROCEDURE IN ALL.ALL TO bsmith;

The following command grants the user Create Procedure privilege in the database named NORTH and the schema named SALES:

GRANT CREATE PROCEDURE IN NORTH.SALES TO bsmith;

Grant all object permissions

To grant users or a group with all object permissions, use the following command:
GRANT ALL ON PROCEDURE TO entity;

This command gives the user or group the Alter, Drop, List, and Execute privileges for procedures and other permissions such as Insert, Delete, and others that are not used for procedures.

For example, the following command grants object permissions to the user bsmith:
GRANT ALL ON PROCEDURE TO bsmith;

For example, the following command grants object permissions to the user bsmith, and is scoped to the database (and schema, if applicable) in which you run the command:

GRANT ALL ON PROCEDURE TO bsmith;

Revoke Create Procedure administration permission

To revoke Create Procedure administration permission, use the following command:
REVOKE CREATE PROCEDURE FROM entity;
For example, the following command revokes Create Procedure permissions from the group analysts:
REVOKE CREATE PROCEDURE FROM GROUP analysts; 

Manage the Alter permission

To grant Alter permission on an object, use the following command:
GRANT ALTER ON object TO entity;
To revoke Alter permission on an object, use the following command:
REVOKE ALTER ON object FROM entity;
For example, to grant Alter permissions for the sample stored procedure returntwo() to the user asmith:
GRANT ALTER ON returntwo(timestamp) TO asmith;
For example, to revoke Alter permissions on the returntwo() procedure from the group sales:
REVOKE ALTER ON returntwo(timestamp) FROM GROUP sales;

Manage the Execute permission

To grant Execute permission on an object, use the following command:
GRANT EXECUTE ON object TO entity;
To revoke Execute permission on an object, use the following command:
REVOKE EXECUTE ON object TO entity;
For example, to grant Execute permissions for the sample procedure returntwo() to the user bsmith:
GRANT EXECUTE ON returntwo(timestamp) TO bsmith;
For example, to revoke Execute permissions for the sample procedure returntwo() from the group sales:
REVOKE EXECUTE ON returntwo(timestamp) FROM GROUP sales;

Manage the Drop permission

To grant Drop permission on an object, use the following command:
GRANT DROP ON object TO entity;
To revoke Drop permission on an object, use the following command:
REVOKE DROP ON object FROM entity;
For example, to grant Drop permissions for the sample procedure returntwo() to the user pcollins:
GRANT DROP ON returntwo(timestamp) TO pcollins;
For example, to revoke Drop permissions for the sample procedure returntwo() from the user bsmith:
REVOKE DROP ON returntwo(timestamp) FROM bsmith;