On 02-Jun-2015 09:08 -0600, Dale Monti wrote:
I am trying query QSYS2.VIEWS, but I get different results from
SQLGETAUTH
If I run the below query on V5R4, the result is a 3.
The value x'0003' as a SMALLINT return value, or something else?
If the scalar function SQLGETAUTH were intending to return the
authority to a specific object, I would expect a value of anything but
x'0003'. Or if instead intending to return whether the invoker was
authorized to an object, I would almost expect a zero\one result, much like:
[
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzajq/rzajqudfsqlcheckauth.htm]
SQL_CHECK_AUTHORITY
"The SQL_CHECK_AUTHORITY scalar function returns an indication of
whether the user is authorized to query the specified *FILE object.
>>-SQL_CHECK_AUTHORITY--(--library-name--,--file-name--)---><
..."
Perhaps though, either the return values greater than zero imply the
authority exists and the values greater than one allude whence the
authority comes, or that the "GET" implies a "Retrieve" feature and thus
the output is not merely an indication of /authorized/ but an indication
of the /authorization/ [albeit I can make no sense of three as a return
value in the latter case].?
On V6R1 it is a 0.
SELECT SYSIBM.SQLGETAUTH( 'WDMONTI2' , 'DEMOAUT', 'FILE '
, '*OBJEXIST ', X'FF3C' )
FROM SYSIBM.SYSDUMMY1
What I would like to do is call this function myself, testing for
*CHANGE authority.
Being an apparently undocumented interface, why not just create your
own function to do what is expected? Besides the CL Check Authority
(CHKAUT) to implement what might be desired, there are also at least the
following:
[
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/apis/QSYCUSRA.htm]
"The Check User Authority to Object (QSYCUSRA) API provides an
indication of whether the user has the specified authority to an object.
..."
or
[
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_61/apis/QSYCUSRA.htm]
[
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/apis/qsyrusra.htm]
"The Retrieve User Authority to Object (QSYRUSRA) API returns a specific
user's authority for an object to the caller.
..."
or
[
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_61/apis/qsyrusra.htm]
I tried this on V6R1:
SELECT SYSIBM.SQLGETAUTH( 'WDMONTI2' , 'DEMOAUT', 'FILE '
, '*CHANGE ', X'FF3C' )
FROM SYSIBM.SYSDUMMY1
But it returns 0. I've tried passing in various hex codes but I
don't see a pattern.
I wrote the following text [starting with next paragraph] before I
even checking that the function also existed on v5r3 to which I have
access. After finding the function exists there, and after testing
there, I get x'F2' as a CHAR(1) result irrespective of inputs; I tested
with effectively random input literal data strings such as 'X' for every
argument, and still the result was always CHAR('2'). Thus I do not
expect the invocation is returning anything about the object, and is
instead returning an effective capabilities-level of the user. Ensure
the user on each release has the same User Class (USRCLS), Special
Authorities (SPCAUT), and administrative rights [per Change Function
Usage (CHGFNCUSG)] on both releases. BTW, what/where is the example
invocation from which the the above tests were derived?
Quite possible either release has a defect; for lack of docs about
the function, however, quite difficult to know what are the
proper\expected inputs and proper\expected output for that
system-provided FUNCTION\UDF. To eliminate possible discrepancies, best
to use the same invocation on both releases, and...
First, omit the trailing blanks in the string literals as arguments
to ensure that might not be an issue. Second, verify each of the
ownership of the file, the invoker, the file object attribute [and
file's SQL attribute] are the same on both releases, and that the
Display File Description (DSPFD) of the file on both releases shows the
same for each of the /Allow Operation/ (ALW) values [for read, write,
update, and delete]. Third, verify the invoker has *CHANGE or more
authority to the database file DEMOAUT/WDMONTI2.
From the examples, difficult to know what the purpose is for having
two arguments with apparent authorization masks; one as the Special
Value [symbolic] named authority bit or named set of authority bits, and
the other as a hex value representing all authority bits. Possibly the
fifth argument is ignored when the fourth argument is supplied [as a
non-NULL or perhaps non-empty-string value], or vice versa.
FWiW, the mask representing *OBJEXIST is x/8000 and the mask
representing *CHANGE is x/3F10
As for the /pattern/ [¿apparently of bits; inferred that nothing
except zero is the consistent output?], see the following:
[
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_61/apis/QSYCVTA.htm]
"Convert Authority Values to MI Value (QSYCVTA) converts authority
values to the machine interface (MI) representation of the value.
..."
<
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/rzatk/TESTAU.htm>
_Test Authority_ (TESTAU)
" ...
The format for the available authority template (operand 1) is as
follows: (1 = authorized)
- Offset -
Dec Hex Field Name Data Type and Length
0 0 Authorization template Char(2)
0 0 Object control Bit 0
0 0 Object management Bit 1
0 0 Authorized pointer Bit 2
0 0 Space authority Bit 3
0 0 Retrieve Bit 4
0 0 Insert Bit 5
0 0 Delete Bit 6
0 0 Update Bit 7
0 0 Ownership (1 = yes) Bit 8
0 0 Excluded Bit 9
0 0 Authority list management Bit 10
0 0 Execute Bit 11
0 0 Alter Bit 12
0 0 Reference Bit 13
0 0 Reserved (binary 0) Bits 14-15
2 2 --- End ---
..."
midrange.com
