Neoview Guide to Stored Procedures in Java (R2.3, R2.4)

6 Granting Privileges for Executing SPJs
Security for SPJs is implemented by schema ownership rules and by granting privileges to
specified user roles.
The schema in which an SPJ is registered is the unit of ownership. The role of the user who creates
the schema is the owner of that schema and all objects associated with it. In Neoview SQL, the
schema owner automatically has 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 the owner of its schema. 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 roles. For more information, see:
• “Granting Privileges on an SPJ”
• “Granting Privileges on Reference Database Objects” (page 52)
• “Revoking Privileges on an SPJ” (page 52)
• “Using Script Files to Grant and Revoke Privileges” (page 53)
• “Using DB Admin to Grant Privileges on SPJs” (page 54)
• “Using DB Admin to Revoke Privileges on SPJs” (page 57)
• “Showing Privileges on SPJs” (page 60)
To display the current ownership and privileges, see “Showing Privileges on SPJs” (page 60).
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 user roles. 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 Neoview SQL Reference Manual.
If you own the SPJ, you can grant the EXECUTE and WITH GRANT OPTION privileges on the
SPJ to any role. If you are not the owner of the SPJ, you must have been granted the WITH
GRANT OPTION privilege to grant privileges to other roles.
As the owner of an SPJ, you can selectively grant the EXECUTE and WITH GRANT OPTION
privileges to specified roles. 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 group of roles. For example, the SPJ named ADJUSTSALARY changes an employee’s
salary in the database. Therefore, only specific roles should be allowed to invoke this SPJ. In this
example, the SPJ owner (or creator) grants the EXECUTE and WITH GRANT OPTION privileges
on ADJUSTSALARY to the Payroll directors.
GRANT EXECUTE
ON PROCEDURE persnl.adjustsalary
TO "ROLE.PAYROLLA", "ROLE.PAYROLLB"
WITH GRANT OPTION;
One of the Payroll directors grants the EXECUTE privilege on ADJUSTSALARY to the regional
department managers:
GRANT EXECUTE
ON PROCEDURE persnl.adjustsalary
TO "ROLE.MGMTRGN1", "ROLE.MGMTRGN2", "ROLE.MGMTRGN3"
WITH GRANT OPTION;
In some cases, all users of a database system might need to invoke an SPJ. For example, the SPJ
named TOTALPRICE calculates the total price of an item, including tax and shipping charges.
This SPJ does not handle sensitive information or modify data and might be useful to customers
Granting Privileges on an SPJ 51