load authority not working for implicit trusted connections

Harishkumar .Pathangay

load authority not working for implicit trusted connections

hi,

I have a database on server configured with trusted context CTX1 with a default role load_role.

The user who got the implicit trusted connection based on ip address, is not able to load data into the table.

he is able to insert into table, but not load. Does trusted context role inheritance apply for load authority? afaik, it should, why it should not? the snapshot of application clearly states the connection is trusted implicitly.

what is happening? thanks,

harish p

Roy Boxwell

load authority not working for implicit trusted connections
(in response to Harishkumar .Pathangay)
What about the LOAD privilege?



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Harishkumar .Pathangay [mailto:[login to unmask email]
Sent: Thursday, January 31, 2019 10:15 AM
To: [login to unmask email]
Subject: [DB2-L] - load authority not working for implicit trusted connections



hi,

I have a database on server configured with trusted context CTX1 with a default role load_role.

The user who got the implicit trusted connection based on ip address, is not able to load data into the table.

he is able to insert into table, but not load. Does trusted context role inheritance apply for load authority? afaik, it should, why it should not? the snapshot of application clearly states the connection is trusted implicitly.

what is happening? thanks,

harish p



-----End Original Message-----

Attachments

  • smime.p7s (5.1k)

Harishkumar .Pathangay

RE: load authority not working for implicit trusted connections
(in response to Roy Boxwell)

I am in db2 11.1.3.3 udb dba in Linux.

there is only load authority and not as a privilege. 

Roy Boxwell

load authority not working for implicit trusted connections
(in response to Harishkumar .Pathangay)
Ahh! I am z !



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Harishkumar .Pathangay [mailto:[login to unmask email]
Sent: Thursday, January 31, 2019 10:25 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: load authority not working for implicit trusted connections



I am in db2 11.1.3.3 udb dba in Linux.

there is only load authority and not as a privilege.



-----End Original Message-----

Attachments

  • smime.p7s (5.1k)

Harishkumar .Pathangay

RE: load authority not working for implicit trusted connections
(in response to Roy Boxwell)

please find attached diag.log error for the same.

Attachments

  • load-error-tc.txt (4.1k)

Ian Bjorhovde

load authority not working for implicit trusted connections
(in response to Harishkumar .Pathangay)
Harish,

From the documentation (https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/doc/c0050517.html https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/doc/c0050517.html ):

Trusted context privileges acquired through a role are effective only for dynamic DML operations. They are not effective for:
DDL operations
Non-dynamic SQL (operations involving static SQL statements such as BIND, REBIND, implicit rebind, incremental bind, and so on)


Ian Bjorhovde
IBM Gold Consultant




> On Jan 31, 2019, at 9:53 AM, Harishkumar .Pathangay <[login to unmask email]> wrote:
>
> please find attached diag.log error for the same.
>
>
> Attachment Links: load-error-tc.txt (4 k) https://www.idug.org/p/fo/do/?download=1&fid=9833
> Site Links: View post online https://www.idug.org/p/fo/st/?post=188118&anc=p188118#p188118 View mailing list online https://www.idug.org/p/fo/si/?topic=19 Start new thread via email <mailto:[login to unmask email]> Unsubscribe from this mailing list <mailto:[login to unmask email]?Subject=Unsubscribe> Manage your subscription https://www.idug.org/p/us/to
>
> This email has been sent to: [login to unmask email]
> ESAi has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
> BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
> http://www.ESAIGroup.com/idug http://www.esaigroup.com/idug
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2 http://www.idug.org/p/cm/ld/fid=2

Harishkumar .Pathangay

RE: load authority not working for implicit trusted connections
(in response to Ian Bjorhovde)

Hi Ian,

Export Utility and Import utility [allow write access mode] is working fine for the same implicit trusted connection user with role inheritance.

Only with Load and Import [allow no access] I am facing issues.

If they are allowing only DML, why they are allowing export and import utility. But not load even after having sufficient authorities and privileges inherited via role inheritance. Looks like a defect to me.

thanks,

harish pathangay

Edited By:
Harishkumar .Pathangay[Organization Members] @ Feb 01, 2019 - 12:45 PM (Asia/Calcutta)

Harishkumar .Pathangay

RE: load authority not working for implicit trusted connections
(in response to Harishkumar .Pathangay)

Hi Ian,

I want to give my opinions on reasons why it should work for these utilities.

when I grant select or insert or any privilege on a table to a user/role or group I do not worry about the isolation levels.

Example, I grant select privilege on a table for a user. the user should be able to run select queries on table at any isolation level.

if he runs with ur - no locks are acquired

runs with cs - row levels transient locks are acquired

runs with rs - row level share/IX locks are acquired

runs with rr - table level share locks are acquired.

the lock type, lock granularity, lock mode that I acquire may vary based on isolation levels used. it cannot prevent the select access or privilege, just because I am not able to acquire a Lock on the same table. it defeats the purpose of granting privileges right.

that is why I am expecting these utilities to work even if role is inherited from a trusted connection context.

you cannot say I will allow for one mode of import/load and prevent access for the other.

hope my understanding and concern is reasonable expectation from a ends user.

thanks,

harish pathangay

Harishkumar .Pathangay

RE: load authority not working for implicit trusted connections
(in response to Harishkumar .Pathangay)

hi all,

any inputs?

thanks