SQL/MX 3.2.1 Guide to Stored Procedures in Java (H06.26+, J06.15+)

6 Managing SPJs in NonStop SQL/MX
This section covers management tasks related to SPJs in NonStop SQL/MX:
• Granting Privileges for Invoking SPJs (page 83)
• Displaying Information About SPJs (page 88)
• Keeping SPJ Statements in OBEY Command Files (page 94)
• Backing Up SPJs (page 97)
• Using SPJs in a Distributed Database Environment (page 98)
Granting Privileges for Invoking SPJs
Security for SPJs is implemented by the schema ownership rules and by granting privileges to
specified users.
The schema in which an SPJ is registered is the unit of ownership. The person who creates the
schema is the owner of that schema and all objects associated with it. In NonStop SQL/MX, the
schema owner and the super ID automatically have these privileges:
• Ability to create and drop SPJs in the schema
• EXECUTE and WITH GRANT OPTION privileges on the SPJs in the schema
To create or drop an SPJ, you must be either the owner of its schema or the super ID. To invoke
an SPJ, you must have the EXECUTE privilege on the SPJ. The EXECUTE privilege allows a user to
invoke an SPJ by issuing a CALL statement. The WITH GRANT OPTION privilege allows a user to
grant the EXECUTE and WITH GRANT OPTION privileges to other users. For more information,
see:
• Granting Privileges on an SPJ (page 83)
• Granting Privileges on Referenced Database Objects (page 84)
• Revoking Privileges on an SPJ (page 85)
To display the current ownership and privileges, see Showing Privileges on the SPJs (page 89).
Granting Privileges on an SPJ
Use the GRANT EXECUTE or GRANT statement to assign the EXECUTE and WITH GRANT OPTION
privileges on an SPJ to specific users. In a GRANT statement, specify ALL PRIVILEGES to grant the
EXECUTE privilege on an SPJ. For the syntax of the GRANT EXECUTE and GRANT statements, see
the SQL/MX Reference Manual.
If you own the SPJ, or are the super ID acting on behalf of the object owner, you can grant the
EXECUTE and WITH GRANT OPTION privileges on the SPJ to any user. If you are not the owner
of the SPJ or the super ID, you must have been granted the WITH GRANT OPTION privilege to
grant privileges to other users.
As the owner of an SPJ, or the super ID acting on behalf of a user with the WITH GRANT OPTION
privilege, you can selectively grant the EXECUTE and WITH GRANT OPTION privileges to specified
users. For some SPJs, particularly ones that handle sensitive information or modify data, you should
grant the EXECUTE and WITH GRANT OPTION privileges to a restricted number of users.
For example, the SPJ named ADJUSTSALARY changes an employee's salary in the database.
Therefore, only specific users should be allowed to invoke this SPJ. In this example, the database
administrator (the super ID), acting on behalf of the SPJ owner, grants the EXECUTE and WITH
GRANT OPTION privileges on ADJUSTSALARY to the regional HR directors. The HR directors grant
the EXECUTE privilege on ADJUSTSALARY to the department managers:
Granting Privileges for Invoking SPJs 83