Federated database and host privileges on SYSIBM tables

[Michael Rudolph]

Hi,

at the moment i try to use the federated database feature (DB2/NT 8.2.3)
to use a remote host db2 (DB2 OS/390 7.1.2).

I am unsure what privileges are needed for the host db2 user.
A select on SYSIBM.SYSTABLES and on the tables where the data resides as
mentioned in the following URL is successful.
http://publib.boulder.ibm.com/infoce...t/tlsdb209.htm

But if i try to add a nickname, the following error is returned:
SQL0551N "The ODBC authid " does not have the privilege to perform
operation "unknown " on object "unknown " SQLSTATE=42501

db2diag.log have some more useful output that is attached at the bottom.
According to that output Information Integrator seems to use more SYSIBM
tables to that I dont have access (e.g. SYSIBM.SYSINDEXES). I don't find
them documented in the infocenter, does anyone have a hint to some
documentation about privileges for DRDA data sources for federation?

I'm not sure if the privileges are similar to those of the "Data
Warehouse Center":
http://publib.boulder.ibm.com/infoce...n/t0005549.htm

I think its not necessary to have SYSADM or SYSCTRL authorizations. How
can i check for the BINDADD authorization?

Thanks, Michael Rudolph

db2diag.log output:
2006-02-27-17.37.30.014000+060 I18221672H452 LEVEL: Error
PID : 2840 TID : 4276 PROC : db2syscs.exe
INSTANCE: DB2CTLSV NODE : 000 DB : TEST
APPHDL : 0-698 APPID: *LOCAL.DB2CTLSV.060227163636
FUNCTION: DB2 UDB, drda wrapper, report_error_message, probe:10
MESSAGE : DRDA Server:
DATA #1 : Hexdump, 7 bytes
0x045A3700 : 5345 5344 5244 42 SESDRDB

2006-02-27-17.37.30.024000+060 I18222126H452 LEVEL: Error
PID : 2840 TID : 4276 PROC : db2syscs.exe
INSTANCE: DB2CTLSV NODE : 000 DB : TEST
APPHDL : 0-698 APPID: *LOCAL.DB2CTLSV.060227163636
FUNCTION: DB2 UDB, drda wrapper, report_error_message, probe:20
MESSAGE : Function name:
DATA #1 : Hexdump, 7 bytes
0x053D1F1C : 646F 5F70 7265 70 do_prep

2006-02-27-17.37.30.024000+060 I18222580H449 LEVEL: Error
PID : 2840 TID : 4276 PROC : db2syscs.exe
INSTANCE: DB2CTLSV NODE : 000 DB : TEST
APPHDL : 0-698 APPID: *LOCAL.DB2CTLSV.060227163636
FUNCTION: DB2 UDB, drda wrapper, report_error_message, probe:30
MESSAGE : ODBC native err:
DATA #1 : Hexdump, 4 bytes
0x087B618C : 2D35 3531 -551

2006-02-27-17.37.30.034000+060 I18223031H451 LEVEL: Error
PID : 2840 TID : 4276 PROC : db2syscs.exe
INSTANCE: DB2CTLSV NODE : 000 DB : TEST
APPHDL : 0-698 APPID: *LOCAL.DB2CTLSV.060227163636
FUNCTION: DB2 UDB, drda wrapper, report_error_message, probe:40
MESSAGE : ODBC sqlstate:
DATA #1 : Hexdump, 6 bytes
0x087B6BB1 : 3432 3530 3100 42501.

2006-02-27-17.37.30.044000+060 I18223484H1196 LEVEL: Error
PID : 2840 TID : 4276 PROC : db2syscs.exe
INSTANCE: DB2CTLSV NODE : 000 DB : TEST
APPHDL : 0-698 APPID: *LOCAL.DB2CTLSV.060227163636
FUNCTION: DB2 UDB, drda wrapper, report_error_message, probe:50
MESSAGE : ODBC error txt:
DATA #1 : Hexdump, 169 bytes
0x087B67B0 : 5B49 424D 5D5B 434C 4920 4472 6976 6572 [IBM][CLI Driver
0x087B67C0 : 5D5B 4442 325D 2053 514C 3035 3531 4E20 ][DB2] SQL0551N
0x087B67D0 : 2022 5255 444F 4C50 4822 2076 6572 66FC "RUDOLPH" verf.
0x087B67E0 : 6774 206E 6963 6874 20FC 6265 7220 6469 gt nicht .ber di
0x087B67F0 : 6520 4265 7265 6368 7469 6775 6E67 2C20 e Berechtigung,
0x087B6800 : 6469 6520 4F70 6572 6174 696F 6E20 2253 die Operation "S
0x087B6810 : 454C 4543 5422 2066 FC72 2064 6173 204F ELECT" f.r das O
0x087B6820 : 626A 656B 7420 2253 5953 4942 4D2E 5359 bjekt "SYSIBM.SY
0x087B6830 : 5349 4E44 4558 4553 2022 2061 7573 7A75 SINDEXES " auszu
0x087B6840 : 66FC 6872 656E 2E20 2053 514C 5354 4154 f.hren. SQLSTAT
0x087B6850 : 453D 3432 3530 310D 0A E=42501..

[s.sathyaram]

You should be defining user mappings for every server.

Let us say, on the OS390, you have READUSER and WRTEUSER which have
read and write access to a table T1

Now you want read1 and read2 users to have read access to T1 on host.

You should be defining a user mapping - to map read1 and read2 to
READUSER.

Then you create a nickname fedt1 for T1 and grant read1 and read2
access to fedt1.

When read1 does SELECT * from fedt1, read1 is translated to READUSER
and its authority to perform SELECT on the table T1 is checked.

Have a look at this :

http://www.gseukdb2.org.uk/downloads/0506federation.zip

For the BINDADD, select from SYSIBM.SYSUSERAUTH table on the host.

The user creating the Nicknames should be mapped to a user on the
remote server that has SELECT privileges on the remote table and the
catlog.
HTH

Sathyaram

Michael Rudolph wrote:

Hi,

at the moment i try to use the federated database feature (DB2/NT 8.2.3)
to use a remote host db2 (DB2 OS/390 7.1.2).

I am unsure what privileges are needed for the host db2 user.
A select on SYSIBM.SYSTABLES and on the tables where the data resides as
mentioned in the following URL is successful.
http://publib.boulder.ibm.com/infoce...t/tlsdb209.htm

But if i try to add a nickname, the following error is returned:
SQL0551N "The ODBC authid " does not have the privilege to perform
operation "unknown " on object "unknown " SQLSTATE=42501

db2diag.log have some more useful output that is attached at the bottom.
According to that output Information Integrator seems to use more SYSIBM
tables to that I dont have access (e.g. SYSIBM.SYSINDEXES). I don't find
them documented in the infocenter, does anyone have a hint to some
documentation about privileges for DRDA data sources for federation?

I'm not sure if the privileges are similar to those of the "Data
Warehouse Center":
http://publib.boulder.ibm.com/infoce...n/t0005549.htm

I think its not necessary to have SYSADM or SYSCTRL authorizations. How
can i check for the BINDADD authorization?

Thanks, Michael Rudolph

db2diag.log output:
2006-02-27-17.37.30.014000+060 I18221672H452 LEVEL: Error
PID : 2840 TID : 4276 PROC : db2syscs.exe
INSTANCE: DB2CTLSV NODE : 000 DB : TEST
APPHDL : 0-698 APPID: *LOCAL.DB2CTLSV.060227163636
FUNCTION: DB2 UDB, drda wrapper, report_error_message, probe:10
MESSAGE : DRDA Server:
DATA #1 : Hexdump, 7 bytes
0x045A3700 : 5345 5344 5244 42 SESDRDB

2006-02-27-17.37.30.024000+060 I18222126H452 LEVEL: Error
PID : 2840 TID : 4276 PROC : db2syscs.exe
INSTANCE: DB2CTLSV NODE : 000 DB : TEST
APPHDL : 0-698 APPID: *LOCAL.DB2CTLSV.060227163636
FUNCTION: DB2 UDB, drda wrapper, report_error_message, probe:20
MESSAGE : Function name:
DATA #1 : Hexdump, 7 bytes
0x053D1F1C : 646F 5F70 7265 70 do_prep

2006-02-27-17.37.30.024000+060 I18222580H449 LEVEL: Error
PID : 2840 TID : 4276 PROC : db2syscs.exe
INSTANCE: DB2CTLSV NODE : 000 DB : TEST
APPHDL : 0-698 APPID: *LOCAL.DB2CTLSV.060227163636
FUNCTION: DB2 UDB, drda wrapper, report_error_message, probe:30
MESSAGE : ODBC native err:
DATA #1 : Hexdump, 4 bytes
0x087B618C : 2D35 3531 -551

2006-02-27-17.37.30.034000+060 I18223031H451 LEVEL: Error
PID : 2840 TID : 4276 PROC : db2syscs.exe
INSTANCE: DB2CTLSV NODE : 000 DB : TEST
APPHDL : 0-698 APPID: *LOCAL.DB2CTLSV.060227163636
FUNCTION: DB2 UDB, drda wrapper, report_error_message, probe:40
MESSAGE : ODBC sqlstate:
DATA #1 : Hexdump, 6 bytes
0x087B6BB1 : 3432 3530 3100 42501.

2006-02-27-17.37.30.044000+060 I18223484H1196 LEVEL: Error
PID : 2840 TID : 4276 PROC : db2syscs.exe
INSTANCE: DB2CTLSV NODE : 000 DB : TEST
APPHDL : 0-698 APPID: *LOCAL.DB2CTLSV.060227163636
FUNCTION: DB2 UDB, drda wrapper, report_error_message, probe:50
MESSAGE : ODBC error txt:
DATA #1 : Hexdump, 169 bytes
0x087B67B0 : 5B49 424D 5D5B 434C 4920 4472 6976 6572 [IBM][CLI Driver
0x087B67C0 : 5D5B 4442 325D 2053 514C 3035 3531 4E20 ][DB2] SQL0551N
0x087B67D0 : 2022 5255 444F 4C50 4822 2076 6572 66FC "RUDOLPH" verf.
0x087B67E0 : 6774 206E 6963 6874 20FC 6265 7220 6469 gt nicht .ber di
0x087B67F0 : 6520 4265 7265 6368 7469 6775 6E67 2C20 e Berechtigung,
0x087B6800 : 6469 6520 4F70 6572 6174 696F 6E20 2253 die Operation "S
0x087B6810 : 454C 4543 5422 2066 FC72 2064 6173 204F ELECT" f.r das O
0x087B6820 : 626A 656B 7420 2253 5953 4942 4D2E 5359 bjekt "SYSIBM.SY
0x087B6830 : 5349 4E44 4558 4553 2022 2061 7573 7A75 SINDEXES " auszu
0x087B6840 : 66FC 6872 656E 2E20 2053 514C 5354 4154 f.hren. SQLSTAT
0x087B6850 : 453D 3432 3530 310D 0A E=42501..

[Michael Rudolph]

Hi,

s.*********@googlemail.com wrote:

You should be defining user mappings for every server.

Let us say, on the OS390, you have READUSER and WRTEUSER which have
read and write access to a table T1

Now you want read1 and read2 users to have read access to T1 on host.

You should be defining a user mapping - to map read1 and read2 to
READUSER.

Then you create a nickname fedt1 for T1 and grant read1 and read2
access to fedt1.

When read1 does SELECT * from fedt1, read1 is translated to READUSER
and its authority to perform SELECT on the table T1 is checked.
I think i understand the creation of nicknames and user mapping quite
well, it already works for other remote DBs (like DB2 UDB or Oracle).
Have a look at this :

http://www.gseukdb2.org.uk/downloads/0506federation.zip

For the BINDADD, select from SYSIBM.SYSUSERAUTH table on the host.
Thanks, this is one of the tables my user lacks access to.
The user creating the Nicknames should be mapped to a user on the
remote server that has SELECT privileges on the remote table and the
catlog.

Select privileges on the remote table are given. Queries via a direct
db2 connect or PASSTHRU statements are successful. But what means
"catalog" here? All SYSIBM-tables or only SYSUSERAUTH and SYSINDEXES?

I need to request these privileges and give a reason for this. So I hope
there is some official document out there which I wasn't able to find
yet (as a reference).

Is it possible to configure the federated DB to not use remote
systables? Surely this would mean to not use the statistics and indexes
directly, but the tables and the queries are quite simple and should run
fine without them.
Thanks, Michael

[Michael Rudolph]

>> The user creating the Nicknames should be mapped to a user on the

remote server that has SELECT privileges on the remote table and the
catlog.

Select privileges on the remote table are given. Queries via a direct
db2 connect or PASSTHRU statements are successful. But what means
"catalog" here? All SYSIBM-tables or only SYSUSERAUTH and SYSINDEXES?

I need to request these privileges and give a reason for this. So I hope
there is some official document out there which I wasn't able to find
yet (as a reference).

My user has now access to a RACF group which has access to the SYS
tables. Everything is working fine. Anyway it would be wunderful to see
this information in some WII documentation.

Thanks, Michael