GROUP privileges question

Harishkumar .Pathangay

GROUP privileges question

Hi,
This might be a FAQ. But If I do not know I have to ask only.
The Table is having Select privilege to a group GNOME. The privilege is not at a user level or role level. Managing Privileges is not the best way, I get that after reading information center. But they still support GROUP privileges. The question is:

If the user is removed from group, it is done at OS Level. For db2 to reflect that it is only in new connections. for existing connections, the select access is not revoked. they can still access the table. so what I did was, flushed the dynamic package cache, assuming a new compilation will be initiated, so the existing connections will also reflect the change in privilege. but sadly, it did not work. the existing connections still hold the select privilege.

the question is, does db2 maintain authorization information at application level rather than at catalog cache level?
A revoke privilege statement, immediately reflects the change in privilege. I understand that, because the change is happening inside db2. so db2 is aware of changing privileges. but user / group association is happening at OS level. that is why I flushed the package cache thinking that new compilation of query will change the authorization information cached in catalog cache. but it is not reflecting for existing connections and only reflects for new connections.
so that got me thinking that authorization information is getting cached at application level also? again as usual these are all based on my test case and observation.

please provide your valuable inputs. it is only a question for learning, not a real time scenario.

Thanks,
Harish Pathangay

Roy Boxwell

GROUP privileges question
(in response to Harishkumar .Pathangay)
If talking z/OS then you must refresh your RACF/ACF2/TOPSecret defs
In the latest RACF exit it can even do this automatically…

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

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

From: Harishkumar .Pathangay [mailto:[login to unmask email]
Sent: Thursday, September 7, 2017 9:20 AM
To: [login to unmask email]
Subject: [DB2-L] - GROUP privileges question


Hi,
This might be a FAQ. But If I do not know I have to ask only.
The Table is having Select privilege to a group GNOME. The privilege is not at a user level or role level. Managing Privileges is not the best way, I get that after reading information center. But they still support GROUP privileges. The question is:

If the user is removed from group, it is done at OS Level. For db2 to reflect that it is only in new connections. for existing connections, the select access is not revoked. they can still access the table. so what I did was, flushed the dynamic package cache, assuming a new compilation will be initiated, so the existing connections will also reflect the change in privilege. but sadly, it did not work. the existing connections still hold the select privilege.

the question is, does db2 maintain authorization information at application level rather than at catalog cache level?
A revoke privilege statement, immediately reflects the change in privilege. I understand that, because the change is happening inside db2. so db2 is aware of changing privileges. but user / group association is happening at OS level. that is why I flushed the package cache thinking that new compilation of query will change the authorization information cached in catalog cache. but it is not reflecting for existing connections and only reflects for new connections.
so that got me thinking that authorization information is getting cached at application level also? again as usual these are all based on my test case and observation.

please provide your valuable inputs. it is only a question for learning, not a real time scenario.

Thanks,
Harish Pathangay

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

Harishkumar .Pathangay

GROUP privileges question
(in response to Roy Boxwell)
I am sorry. Hence forth I will remember to post on platform information as well.

I am on LUW, DB2 V11.1 express-C, Linux 64 bit

Thanks,
Harish Pathangay

Sent from Mail for Windows 10

From: Boxwell, Roy
Sent: 07 September 2017 14:12
To: [login to unmask email]
Subject: [DB2-L] - RE: GROUP privileges question

If talking z/OS then you must refresh your RACF/ACF2/TOPSecret defs
In the latest RACF exit it can even do this automatically…

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: [login to unmask email]
http://www.seg.de

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

From: Harishkumar .Pathangay [mailto:[login to unmask email]
Sent: Thursday, September 7, 2017 9:20 AM
To: [login to unmask email]
Subject: [DB2-L] - GROUP privileges question

Hi,
This might be a FAQ. But If I do not know I have to ask only.
The Table is having Select privilege to a group GNOME. The privilege is not at a user level or role level. Managing Privileges is not the best way, I get that after reading information center. But they still support GROUP privileges. The question is:
If the user is removed from group, it is done at OS Level. For db2 to reflect that it is only in new connections. for existing connections, the select access is not revoked. they can still access the table. so what I did was, flushed the dynamic package cache, assuming a new compilation will be initiated, so the existing connections will also reflect the change in privilege. but sadly, it did not work. the existing connections still hold the select privilege.
the question is, does db2 maintain authorization information at application level rather than at catalog cache level?
A revoke privilege statement, immediately reflects the change in privilege. I understand that, because the change is happening inside db2. so db2 is aware of changing privileges. but user / group association is happening at OS level. that is why I flushed the package cache thinking that new compilation of query will change the authorization information cached in catalog cache. but it is not reflecting for existing connections and only reflects for new connections.
so that got me thinking that authorization information is getting cached at application level also? again as usual these are all based on my test case and observation.
please provide your valuable inputs. it is only a question for learning, not a real time scenario.
Thanks,
Harish Pathangay

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


Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
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


Attachments

  • 48F14D862C2144BF91262B69C1A225E3.png (<1k)

Nadir Doctor

GROUP privileges question
(in response to Roy Boxwell)
Hi Harish,

You may try deactivating the database and activating it again to see if
that helps in resolving the issue.


Best Regards,
Nadir



On Thu, Sep 7, 2017 at 3:41 AM, Boxwell, Roy <[login to unmask email]> wrote:

> If talking z/OS then you must refresh your RACF/ACF2/TOPSecret defs
>
> In the latest RACF exit it can even do this automatically…
>
>
>
> *Roy Boxwell*
>
> SOFTWARE ENGINEERING GMBH and SEGUS Inc.
> -Product Development-
>
>
> *Heinrichstrasse 83
> https://maps.google.com/?q=Heinrichstrasse+83&entry=gmail&source=g -85
> 40239 Duesseldorf/Germany*
> * Tel. *
>
> *+49 (0)211 96149-675 <+49%20211%2096149675> Fax +49 (0)211 96149-32
> <+49%20211%209614932> Email: **[login to unmask email]* <[login to unmask email]>
> *http://www.seg.de* http://www.seg.de
>
>
>
> * Software Engineering GmbH Amtsgericht Düsseldorf, HRB 37894
> Geschäftsführung: Gerhard Schubert, Bettina Schubert*
>
>
>
> *From:* Harishkumar .Pathangay [mailto:[login to unmask email]
> *Sent:* Thursday, September 7, 2017 9:20 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - GROUP privileges question
>
>
>
> Hi,
> This might be a FAQ. But If I do not know I have to ask only.
> The Table is having Select privilege to a group GNOME. The privilege is
> not at a user level or role level. Managing Privileges is not the best way,
> I get that after reading information center. But they still support GROUP
> privileges. The question is:
>
> If the user is removed from group, it is done at OS Level. For db2 to
> reflect that it is only in new connections. for existing connections, the
> select access is not revoked. they can still access the table. so what I
> did was, flushed the dynamic package cache, assuming a new compilation will
> be initiated, so the existing connections will also reflect the change in
> privilege. but sadly, it did not work. the existing connections still hold
> the select privilege.
>
> the question is, does db2 maintain authorization information at
> application level rather than at catalog cache level?
> A revoke privilege statement, immediately reflects the change in
> privilege. I understand that, because the change is happening inside db2.
> so db2 is aware of changing privileges. but user / group association is
> happening at OS level. that is why I flushed the package cache thinking
> that new compilation of query will change the authorization information
> cached in catalog cache. but it is not reflecting for existing connections
> and only reflects for new connections.
> so that got me thinking that authorization information is getting cached
> at application level also? again as usual these are all based on my test
> case and observation.
>
> please provide your valuable inputs. it is only a question for learning,
> not a real time scenario.
>
> Thanks,
> Harish Pathangay
>
>
> -----End Original Message-----
>
> -----End Original Message-----
>

Harishkumar .Pathangay

GROUP privileges question
(in response to Nadir Doctor)
Hi Nadir,
Yeah that sure works and also works on a new connection.
The User Group Association is cached during Connection Establishment.
After that there is nothing from inside the DB2 that we can do for re-evaluating the user group association. Like Flushing Package Cache did not help.
What action will re-evaluate the user group association other than terminating or deactivating database or instance restart.

Thanks,
Harish Pathangay

Sent from Mail for Windows 10

From: Nadir Doctor
Sent: 07 September 2017 16:16
To: [login to unmask email]
Subject: [DB2-L] - RE: GROUP privileges question

Hi Harish,

You may try deactivating the database and activating it again to see if that helps in resolving the issue.



Best Regards,
Nadir
 


On Thu, Sep 7, 2017 at 3:41 AM, Boxwell, Roy <[login to unmask email]> wrote:
If talking z/OS then you must refresh your RACF/ACF2/TOPSecret defs
In the latest RACF exit it can even do this automatically…
 
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: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert
 
From: Harishkumar .Pathangay [mailto:[login to unmask email]
Sent: Thursday, September 7, 2017 9:20 AM
To: [login to unmask email]
Subject: [DB2-L] - GROUP privileges question
 
Hi,
This might be a FAQ. But If I do not know I have to ask only.
The Table is having Select privilege to a group GNOME. The privilege is not at a user level or role level. Managing Privileges is not the best way, I get that after reading information center. But they still support GROUP privileges. The question is:
If the user is removed from group, it is done at OS Level. For db2 to reflect that it is only in new connections. for existing connections, the select access is not revoked. they can still access the table. so what I did was, flushed the dynamic package cache, assuming a new compilation will be initiated, so the existing connections will also reflect the change in privilege. but sadly, it did not work. the existing connections still hold the select privilege.
the question is, does db2 maintain authorization information at application level rather than at catalog cache level?
A revoke privilege statement, immediately reflects the change in privilege. I understand that, because the change is happening inside db2. so db2 is aware of changing privileges. but user / group association is happening at OS level. that is why I flushed the package cache thinking that new compilation of query will change the authorization information cached in catalog cache. but it is not reflecting for existing connections and only reflects for new connections.
so that got me thinking that authorization information is getting cached at application level also? again as usual these are all based on my test case and observation.
please provide your valuable inputs. it is only a question for learning, not a real time scenario.
Thanks,
Harish Pathangay
 
-----End Original Message-----


Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
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




Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
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


Attachments

  • 38C8C0CC97AD42E1BC8525304669E528.png (<1k)
  • 7E947DC27206400F802393A139D7477B.png (<1k)