SQL/MX Guide to Stored Procedures in Java (G06.24+, H06.03+)

HP NonStop SQL/MX Guide to Stored Procedures in Java—523727-004
6-1
6
Managing SPJs in NonStop SQL/MX
This section covers management tasks related to SPJs in NonStop SQL/MX:
•
Granting Privileges for Invoking SPJs on page 6-1
•
Displaying Information About SPJs on page 6-7
•
Keeping SPJ Statements in OBEY Command Files on page 6-14
•
Backing Up SPJs on page 6-18
•
Using SPJs in a Distributed Database Environment on page 6-18
•
Migrating SPJs From NonStop SQL/MX Release 1.8 to NonStop SQL/MX Release
2.x on page 6-22
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 on page 6-1
•
Granting Privileges on Referenced Database Objects on page 6-3
•
Revoking Privileges on an SPJ on page 6-4
To display the current ownership and privileges, see Showing Privileges on the SPJs
on page 6-8.
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