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

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.schematast,
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, 2.1, and 2.2 is
1200.
For the output, see Interpreting Privileges Information (page 90).
4. To end logging, enter the LOG command without the name of the log file:
LOG;
Interpreting Ownership and Grantor Informationntor Information
The first query in 2 returns the owners, grantors of privileges, and grantees for all SPJs in a catalog,
as shown:
SCHEMA PROCEDURE OBJECT_OWNER GRANTOR GRANTOR_TYPE GRANTEE
-------- --------------- ------------ ----------- ------------ ------
PERSNL ADJUSTSALARY 2053 -2 S 2053
PERSNL ADJUSTSALARY 2053 2054 U 2069
PERSNL ADJUSTSALARY 2053 2054 U 2068
PERSNL ADJUSTSALARY 2053 2054 U 2055
PERSNL ADJUSTSALARY 2053 2053 U 2054
PERSNL ADJUSTSALARY 2053 2053 U 2051
SALES DAILYORDERS 2053 -2 S 2053
SALES LOWERPRICE 2053 -2 S 2053
SALES MONTHLYORDERS 2053 -2 S 2053
SALES MONTHLYORDERS 2053 2053 U -1
SALES TOTALPRICE 2053 -2 S 2053
--- 11 row(s) selected.
The owners, grantors, and grantees are represented by user ID numbers. Use the USER_GETINFO_
procedure to return the user name associated with the user ID number. For more information, see
the Guardian Procedure Calls Reference Manual.
By default, the system (represented by -2 in the GRANTOR column) grants ownership privileges to
the creator of each schema and its SPJs, who happens to be user ID 2053 in this case, as shown
in the OBJECT_OWNER column. The GRANTOR_TYPE column shows whether the system (S) or a
user (U) was responsible for granting privileges to other users.
In the example, the super ID (65535), acting on behalf of the object owner (2053), granted
privileges on the MONTHLYORDERS procedure to PUBLIC (represented by -1 in the GRANTEE
column). The super ID, acting on behalf of the object owner (2053), also granted privileges on
the ADJUSTSALARY procedure to HR.MGRNA (2054) and HR.MGREU (2051). The user named
HR.MGRNA (2054) granted privileges to MKG.RAYMOND (2069), RD.MCKAY (2068), and
CORP.GREEN (2055), as shown in the highlighted rows.
Interpreting Privileges Information
The second query in 3 returns the grantees and the privileges they have on each SPJ in a catalog,
as shown:
SCHEMA PROCEDURE GRANTEE GRANTEE_TYPE PRIVILEGE_TYPE IS_GRANTABLE
-------- -------------- -------- ------------ -------------- ------------
90 Managing SPJs in NonStop SQL/MX