Cross-Server Query permissions
User reports that cross-server queries from aserver to bserver succeed despite revoking SELECT permissions on btable from auser, while direct queries to bserver correctly fail with permission error 272. Direct queries work as expected when permissions are granted. Alexandre Marini suggests checking user mapping configuration (which was OFF).
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I have 2 servers on same machine, running under different users(auser,buser).
On bserver i have:
grant connect to auser;
REVOKE SELECT ON btable FROM auser;
Still i can make Cross-Server Queries, i think this should not be possible.
[auser@test]$ echo "SELECT FIRST 1 bcol1,bcol2 FROM bdatabase@bserver:btable;"
| dbaccess adatabase@aserver
Database selected.
bcol1 bcol2....
But if i run it directly with auser in aserver i get what i expect:
[auser@test]$ echo "SELECT FIRST 1 bcol1,bcol2 FROM bdatabase@bserver:btable;"
| dbaccess bdatabase@bserver
Database selected.
272: No SELECT permission for btable....
or:
[auser@test]$ echo "SELECT FIRST 1 bcol1,bcol2 FROM btable;" | dbaccess
bdatabase@bserver
Database selected.
272: No SELECT permission for btable....
If i grant select to auser, in bserver:
GRANT SELECT ON btable TO auser;
Both will work as expected:
[auser@test]$ echo "SELECT FIRST 1 bcol1,bcol2 FROM bdatabase@bserver:btable;"
| dbaccess adatabase@aserver
Database selected.
bcol1 bcol2...
[auser@test]$ echo "SELECT FIRST 1 bcol1,bcol2 FROM bdatabase@bserver:btable;"
| dbaccess bdatabase@bserver
Database selected.
bcol1 bcol2...
How can i REVOKE SELECT ON btable FROM auser so the Cross-Server Query gives
me this:
[auser@test]$ echo "SELECT FIRST 1 bcol1,bcol2 FROM bdatabase@bserver:btable;"
| dbaccess adatabase@aserver
Database selected.
272: No SELECT permission for btable....
Hello. From what I saw, all your queries were executed by userA shell. Unless you specify a different user, your connections will follow your login user. Please check this out and you can use and revoke permissions accordingly: https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_0344.htm HTH Alexandre Marini
"From what I saw, all your queries were executed by userA shell.
Unless you specify a different user, your connections will follow your login
user. "
- yes you are correct, i assume.
So why this is allowed:
[auser@test]$ echo "SELECT FIRST 1 bcol1,bcol2 FROM bdatabase@bserver:btable;"
| dbaccess adatabase@aserver
Database selected.
bcol1 bcol2....
But this is not:
[auser@test]$ echo "SELECT FIRST 1 bcol1,bcol2 FROM bdatabase@bserver:btable;"
| dbaccess bdatabase@bserver
Database selected.
272: No SELECT permission for btable....
Nor is this:
[auser@test]$ echo "SELECT FIRST 1 bcol1,bcol2 FROM btable;" | dbaccess
bdatabase@bserver
Database selected.
272: No SELECT permission for btable....
Is there any user mapping configured? Probably that is the reason... Just guessing... HTH Alexandre Marini
Hi,
Sorry late responce:
USERMAPPING OFF
I did a little test with a setup similar to what you described and I get the
expected "272: No SELECT permission for btable." .
So something must be different on your setup.
What are the actual database privileges for each user?
Who is the owner of each table?
What are the actual permissions for each table?
Can you use "onstat" and check what is the actual owner of the distributed
session?