DB2 z/OS - replace table auth’s

Mark Vickers

DB2 z/OS - replace table auth’s
Hi Folks,
We need to replace I,U,D authorizations which were granted to PUBLIC with a non TSO userid.
We only have RACF validating the TSO user logon, from there it is all DB2 internal security.
This is what we tested:

1. Revoked I,U,D from public on one table and tested an online screen and package got a -904 (invalidated)

2. Tried rebinding the package which did not work until we gave I,U,D on the table to the package owner.

3. Then started over back from 1. With the PUBLIC access back on, granted I,U,D to the package owner, revoked from public and the package still got invalidated.

4. We figured out if we do the grant to package owner, then rebind, then revoke from public, there was no outage, all worked.

Am I missing some other authority (like BINDAUTH on the PLAN or maybe CREATE on the COLLID) or is there a better way to do this without rebinding the world ?

Thanks,

[Signature5]

Attachments

  • image002.jpg (6.1k)

James Campbell

DB2 z/OS - replace table auth’s
(in response to Mark Vickers)
You will have to rebind the world.

Db2 uses the contents of the SYS%AUTH tables to determine consequential invalidation
when a permission is revoked. Package invalidation in this case. It does not evaluate if
there are other, alternative, permissions that would bypass the need to invalidate.

This is why item 3 (below) failed. The revoke (from public) did not trigger a re-evaluation of
the package owner's authorities.

Don't count on item 4 always working. I have seen situations (albeit several versions ago)
where DB2 (as it was) prefered a grant to public over anything else - even install sysadm.
After rebinding the world, check SYSTABAUTH to confirm that PUBLIC is no longer the
GRANTOR of any row on the tables.

If you have a view that is dependant on a grant, it will be dropped when the grant is revoked.

James Campbell

On 19 Sep 2018 at 19:19, Vickers, Mark wrote:

>
> Hi Folks,
> We need to replace I,U,D authorizations which were granted to PUBLIC with a non TSO
> userid.
> We only have RACF validating the TSO user logon, from there it is all DB2 internal
> security.
> This is what we tested:
> 1.      Revoked I,U,D from public on one table and tested an online screen and package got a
> -904 (invalidated)
> 2.      Tried rebinding the package which did not work until we gave I,U,D on the table to the
> package owner.
> 3.      Then started over back from 1. With the PUBLIC access back on, granted I,U,D to the
> package owner, revoked from public and the package still got invalidated.
> 4.      We figured out if we do the grant to package owner, then rebind, then revoke from
> public, there was no outage, all worked.
>  
> Am I missing some other authority (like BINDAUTH on the PLAN or maybe CREATE
> on the COLLID) or is there a better way to do this without rebinding the world ?
>  
> Thanks,
>  


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

Mahmood Wadee

RE: DB2 z/OS - replace table auth’s
(in response to Mark Vickers)

Hi Mark , 

I am busy with a similar cleanup. 

Because the table has PUBLIC access the owners of the packages associated with the table/s implicitly inherit PUBLIC access. You will notice that PUBLIC becomes the GRANTOR on the packages. 

To resolve the issue you have to do what you have done.. I don't think there is another way
1) Grant the owners explicit access to the tables

2) REBIND the packages 

3) REVOKE PUBLIC access on the tables. 

 

Mark Vickers

DB2 z/OS - replace table auth’s
(in response to Mahmood Wadee)
Thank you both for the replies.

From: Mahmood Wadee [mailto:[login to unmask email]
Sent: Wednesday, September 19, 2018 10:10 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 z/OS - replace table auth’s


Hi Mark ,

I am busy with a similar cleanup.

Because the table has PUBLIC access the owners of the packages associated with the table/s implicitly inherit PUBLIC access. You will notice that PUBLIC becomes the GRANTOR on the packages.

To resolve the issue you have to do what you have done.. I don't think there is another way
1) Grant the owners explicit access to the tables

2) REBIND the packages

3) REVOKE PUBLIC access on the tables.



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