Plans/Packages granted privileges on tables - what does that mean?

Mohamed Esmael

Plans/Packages granted privileges on tables - what does that mean?

In DB2 V11 for Z/OS, what does it mean to have a plan that has explicit table privileges listed in SYSIBM.SYSTABAUTH? I thought only the owner of a plan needed to have the table privileges, and then execute authority would be given to the users needed to execute the plan. I don't understand what the privileges for a plan are used for.  so why someone would need to make use of these privileges.

Walter Janißen

AW: Plans/Packages granted privileges on tables - what does that mean?
(in response to Mohamed Esmael)
Hi

I think you are talking about packages and not plans. Plans don’t contain any statement anymore. Well you are right that the owner of the package needs to have all necessary privileges and the user who binds a plan needs execute privilege on the packages, contained in PKLIST. The entries in SYSTABAUTH for packages show, how the package works on the table. So you can easily find, which packages select, update, delete or insert on a table.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Mohamed Esmael [mailto:[login to unmask email]
Gesendet: Dienstag, 27. Februar 2018 12:25
An: [login to unmask email]
Betreff: [DB2-L] - Plans/Packages granted privileges on tables - what does that mean?


In DB2 V11 for Z/OS, what does it mean to have a plan that has explicit table privileges listed in SYSIBM.SYSTABAUTH? I thought only the owner of a plan needed to have the table privileges, and then execute authority would be given to the users needed to execute the plan. I don't understand what the privileges for a plan are used for. so why someone would need to make use of these privileges.

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

  • image001.png (2.6k)

Mohamed Esmael

RE: AW: Plans/Packages granted privileges on tables - what does that mean?
(in response to Walter Janißen)

so how can i apply that on RACF Access Control if i enable SOD (Separation of duties ) ?

Walter Jani&#223;en

AW: AW: Plans/Packages granted privileges on tables - what does that mean?
(in response to Mohamed Esmael)
I believe regarding SYSTABAUTH nothing will change, but I am not sure.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Mohamed Esmael [mailto:[login to unmask email]
Gesendet: Dienstag, 27. Februar 2018 12:50
An: [login to unmask email]
Betreff: [DB2-L] - RE: AW: Plans/Packages granted privileges on tables - what does that mean?


so how can i apply that on RACF Access Control if i enable SOD (Separation of duties ) ?

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

  • image001.png (2.6k)

Mohamed Esmael

RE: AW: AW: Plans/Packages granted privileges on tables - what does that mean?
(in response to Walter Janißen)

Thanks

James Campbell

Plans/Packages granted privileges on tables - what does that mean?
(in response to Mohamed Esmael)
Db2 maintains as part of the BIND/FREE processs. And uses them for its own purposes. It is separation of duties from what you do,
so you don't need to know or understand why. You cannot, and should not try, to migrate the information to an external security product (like RACF).

However DBAs like them because it gives them an easy way of matching tables to the
programs that use them.

James Campbell


On 27 Feb 2018 at 4:25, Mohamed Esmael wrote:

>
> In DB2 V11 for Z/OS, what does it mean to have a plan that has explicit table privileges listed in
> SYSIBM.SYSTABAUTH? I thought only the owner of a plan needed to have the table privileges,
> and then execute authority would be given to the users needed to execute the plan. I don't
> understand what the privileges for a plan are used for.  so why someone would need to make use
> of these privileges.
>
>

---
This email has been checked for viruses by AVG.
http://www.avg.com

Peter Vanroose

RE: Plans/Packages granted privileges on tables - what does that mean?
(in response to James Campbell)

It's indeed for Db2 "internal use"; and actually an easy to understand one:

* On a certain package p, user (or role) u has EXECUTE authority. That can be managed by (say) RACF.

* That package contains "static SQL", that is: on execution it does not need to parse the SQL anymore.

* Now user u wants to make use of that EXECUTE right. How does Db2 know which data access is to be granted?

    => Exactly: by looking in SYSIBM.SYSTABAUTH, for package p as the GRANTEE.

Bottomline, and it's very important to be aware of this when migrating to (or already using) external security (like RACF):

       All SYSIBM.*AUTH tables are still in use, and may override what RACF is saying!


--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/


In Reply to James Campbell:

Db2 maintains as part of the BIND/FREE process. And uses them for its own purposes. It is separation of duties from what you do,
so you don't need to know or understand why. You cannot, and should not try, to migrate the information to an external security product (like RACF).

Mohamed Esmael

RE: Plans/Packages granted privileges on tables - what does that mean?
(in response to Peter Vanroose)

Thanks you for clarification , i have two questions 

1- when we see SYSIBM.SYSTABAUTH , we see that privileges (P) has all table authorities with Grant options  even if the user (u) has Execute on that package(P) and select privileges on table , is it possible ??

2- how can we apply that on RACF Access Control ?

James Campbell

Plans/Packages granted privileges on tables - what does that mean?
(in response to Mohamed Esmael)
These rows are best thought of as an internal Db2 function. Leave them alone.

James Campbell

On 28 Feb 2018 at 3:19, Mohamed Esmael wrote:

>
> Thanks you for clarification , i have two questions 
> 1- when we see SYSIBM.SYSTABAUTH , we see that privileges (P) has all table authorities with
> Grant options  even if the user (u) has Execute on that package(P) and select privileges on table ,
> is it possible ??
> 2- how can we apply that on RACF Access Control ?


---
This email has been checked for viruses by AVG.
http://www.avg.com