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

Managing SPJs in NonStop SQL/MX
HP NonStop SQL/MX Guide to Stored Procedures in Java—523727-004
6-8
Showing Privileges on the SPJs
The query returns the catalog, schema, and procedure names of all the SPJs in the
catalog, as shown:
Showing Privileges on the SPJs
To display the ownership and privileges for all the SPJs in a user catalog of the
database, query the TBL_PRIVILEGES, OBJECTS, and SCHEMATA system metadata
tables and store the results in a log file, if desired.
For example:
1. In an MXCI session, enter the LOG command so that MXCI writes the output of the
query to a log file:
LOG spj_prvlgs.txt;
2. In the same MXCI session, enter this query to return ownership and grantor
information for the SPJs in a catalog of the database:
SELECT SUBSTRING(schema_name,1,8) AS "SCHEMA",
SUBSTRING(object_name,1,15) AS "PROCEDURE",
object_owner, grantor, grantor_type, grantee
FROM catalog.definition_schema_version_vernum.objects ot,
nonstop_sqlmx_node.system_schema.schemata st,
catalog.definition_schema_version_vernum.tbl_privileges pr
WHERE ot.object_type = 'UR'
AND ot.schema_uid = st.schema_uid
AND ot.object_uid = pr.table_uid;
The schema version number vernum for NonStop SQL/MX Releases 2.0 and 2.1
is 1200.
For the output, see Interpreting Ownership and Grantor Information on page 6-9.
3. Enter this query to return the privileges that grantees have on the SPJs in a
catalog of the database:
SELECT SUBSTRING(schema_name,1,8) AS "SCHEMA",
SUBSTRING(object_name,1,15) AS "PROCEDURE",
grantee, grantee_type, privilege_type, is_grantable
FROM catalog.definition_schema_version_vernum.objects ot,
nonstop_sqlmx_node.system_schema.schemata st,
catalog.definition_schema_version_vernum.tbl_privileges pr
WHERE ot.object_type = 'UR'
CATALOG SCHEMA PROCEDURE
------------------------- ------------------------- --------------------
SAMDBCAT PERSNL ADJUSTSALARY
SAMDBCAT SALES DAILYORDERS
SAMDBCAT SALES LOWERPRICE
SAMDBCAT SALES MONTHLYORDERS
SAMDBCAT SALES TOTALPRICE
--- 5 row(s) selected.