Catalog Tables and OwnerShip

Mohamed Esmael

Catalog Tables and OwnerShip

Dear All 

  I want your help on the below issue , 

  i f user X (SYSADM OR DBADM) create DataBase named ABC so there is record added on SYSIBM.SYSDBAUTH show that the Grantee is X and Grantor is X and with all the Privileges fields will be G (Privilege held with the GRANT option)

My Question Can SECADM Change Privilege fields from G (Privilege held with the GRANT option) to Y (Privilege is held without the GRANT option) ??

Peter Vanroose

RE: Catalog Tables and OwnerShip
(in response to Mohamed Esmael)

Yes, of course.  As long as user X and the particular SECADM are not the same userID.

In Reply to Mohamed Esmael:

if user X (SYSADM OR DBADM) create DataBase named ABC so there is record added on SYSIBM.SYSDBAUTH show that the Grantee is X and Grantor is X and with all the Privileges fields will be G (Privilege held with the GRANT option)

My Question Can SECADM Change Privilege fields from G (Privilege held with the GRANT option) to Y (Privilege is held without the GRANT option) ??

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

Mohamed Esmael

RE: Catalog Tables and OwnerShip
(in response to Peter Vanroose)

Thanks for your reply

i want to clarify that  

1-  Even if user X is (SYSADM OR DBADM)  is it possible

2- how to Change Privilege fields from G (Privilege held with the GRANT option) to Y (Privilege is held without the GRANT option) ?

Peter Vanroose

RE: Catalog Tables and OwnerShip
(in response to Mohamed Esmael)

Mohamed,

I was expecting the following would work (assuming "X" holds the CREATEDBA grant, and S is secadm):

X>   CREATE DATABASE abc; -- (1)

S>   GRANT  dbadm ON DATABASE abc TO x; -- (2), without grant option

S>   REVOKE dbadm ON DATABASE abc FROM x BY x; -- (3)

But it doesn't; that is: (1) and (2) work, but not (3); also, interchanging (2) and (3) does not change matters.

So you are right: it seems impossible to revoke the "with grant option" grants from the creator of a database (and of any other object, for that matter).

Error message is: SQLCODE -556:  An attempt to revoke a privilege from "X" was denied because "X" does not hold this privilege.


In Reply to Mohamed Esmael:

1-  Even if user X is (SYSADM OR DBADM)  is it possible

2- how to Change Privilege fields from G (Privilege held with the GRANT option) to Y (Privilege is held without the GRANT option) ?

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

Edited By:
Peter Vanroose[Organization Members] @ Jan 16, 2018 - 09:21 AM (Europe/Brussels)

Peter Vanroose

Re: Catalog Tables and OwnerShip
(in response to Peter Vanroose)

Db2 12 for z/OS (finally) has a "fix" for this: you'll need to transfer ownership of the database to some other (pseudo)user; after that, you can revoke the "G" authorities from the previous owner.

TRANSFER OWNERSHIP OF DATABASE abc TO new_user;
GRANT dbadm ON DATABASE abc TO old_user;
REVOKE dbadm ON DATABASE abc FROM old_user BY old_user NOT INCLUDING DEPENDENT PRIVILEGES;


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

Mohamed Esmael

Re: Catalog Tables and OwnerShip
(in response to Peter Vanroose)

we use DB2 v.11  , also if later we upgrade to V.12 and use Transfer Owner Ship , does the new user will held the privileges with grant privileges  or what ? 

Peter Vanroose

Re: Catalog Tables and OwnerShip
(in response to Mohamed Esmael)

In Reply to Mohamed Esmael:

we use DB2 v.11  , also if later we upgrade to V.12 and use Transfer Owner Ship , does the new user will held the privileges with grant privileges  or what ? 

Yes, that seems to be unavoidable...

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

Mohamed Esmael

Re: Catalog Tables and OwnerShip
(in response to Peter Vanroose)

Thanks a lot for your reply and clarification  , i have another question 

can that problem solved on LUW (Linux , UNIX , windows) environment ? 

Peter Vanroose

Re: Catalog Tables and OwnerShip
(in response to Mohamed Esmael)

Db2 for LUW does not know the concept of a database in the sense of Db2 for z/OS.

On the other hand, Db2 for LUW allows transferral of ownership on any possible kind of object, already since version 9.1.

In Reply to Mohamed Esmael:

can that problem solved on LUW (Linux , UNIX , windows) environment ?

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

Mohamed Esmael

Re: Catalog Tables and OwnerShip
(in response to Peter Vanroose)

Thanks a lot for your reply and clarification