Revoking a privilege from self (DB2 z/OS)

Graham O'Hara

Revoking a privilege from self (DB2 z/OS)
An Authorisation ID has granted DBADM to themselves, ie in
SYSIBM.SYSDBAUTH, one row has the same ID in GRANTOR and GRANTEE
fields.

The question is how to revoke this privilege.

Using the Grantor/Grantee as the SQLID gives a -555 "AN AUTHORIZATION ID
CANNOT REVOKE A PRIVILEGE FROM ITSELF"

Using a SYSADM user as the SQLID gives a -556 "authid2 CANNOT HAVE THE
privilege PRIVILEGE on_object REVOKED BY authid1 BECAUSE THE REVOKEE
DOES NOT POSSESS THE PRIVILEGE OR THE REVOKER DID NOT MAKE THE
GRANT"

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Wayne Stevens

Re: Revoking a privilege from self (DB2 z/OS)
(in response to Graham O'Hara)
Make sure the ID does not have Install SYSADM. Revoke all the other
SYSADM authorizations 1st using the method before. Then revoke the last
SYSADM without the By clause. IF that does not work, then revoke it
using the Install sysadm id.

Wayne Stevens
[login to unmask email]

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Graham O'Hara
Sent: Friday, January 11, 2008 7:32 AM
To: [login to unmask email]
Subject: Revoking a privilege from self (DB2 z/OS)

An Authorisation ID has granted DBADM to themselves, ie in
SYSIBM.SYSDBAUTH, one row has the same ID in GRANTOR and GRANTEE
fields.

The question is how to revoke this privilege.

Using the Grantor/Grantee as the SQLID gives a -555 "AN AUTHORIZATION ID

CANNOT REVOKE A PRIVILEGE FROM ITSELF"

Using a SYSADM user as the SQLID gives a -556 "authid2 CANNOT HAVE THE
privilege PRIVILEGE on_object REVOKED BY authid1 BECAUSE THE REVOKEE
DOES NOT POSSESS THE PRIVILEGE OR THE REVOKER DID NOT MAKE THE
GRANT"

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Raymond Bell

Re: Revoking a privilege from self (DB2 z/OS)
(in response to Wayne Stevens)
Graham,

When Grantor and Grantee are the same value it means the ID owns the
object. In this case 'authid2' created the database - they didn't grant
it. The only way to get rid of it is to drop and recreate the object -
but someone will still appear in this way.

Or you can just learn to live with it and ignore grants from the owner,
to the owner.

Cheers,


Raymond
PS. Plain text. Oh joy...

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Graham O'Hara
Sent: 11 January 2008 13:32
To: [login to unmask email]
Subject: [DB2-L] Revoking a privilege from self (DB2 z/OS)

An Authorisation ID has granted DBADM to themselves, ie in
SYSIBM.SYSDBAUTH, one row has the same ID in GRANTOR and GRANTEE
fields.

The question is how to revoke this privilege.

Using the Grantor/Grantee as the SQLID gives a -555 "AN AUTHORIZATION ID

CANNOT REVOKE A PRIVILEGE FROM ITSELF"

Using a SYSADM user as the SQLID gives a -556 "authid2 CANNOT HAVE THE
privilege PRIVILEGE on_object REVOKED BY authid1 BECAUSE THE REVOKEE
DOES NOT POSSESS THE PRIVILEGE OR THE REVOKER DID NOT MAKE THE
GRANT"

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Phil Grainger

Re: Revoking a privilege from self (DB2 z/OS)
(in response to Raymond Bell)
Not sure of this will help, but the SQL Ref says (in part) about the TO
authid of a GRANT statement (the REVOKE statement has the same comments)

"TO Specifies to what authorization IDs the privileges are granted.
authorization-name,... Lists one or more authorization IDs.
The value of CURRENT RULES determines whether you can use the ID of the
GRANT statement itself (to grant privileges to yourself).
When CURRENT RULES is: DB2 You cannot use the ID of the GRANT statement.
STD You can use the ID of the GRANT statement."

SO, try:

SET CURRENT RULES 'STD';
REVOKE DBADM FROM myid;

And cross your fingers!

Phil Grainger
CA
Product Management Director
Phone: +44 (0)1753 577 733
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]

Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL

CA plc a company registered in England and Wales under company
registration number 1282495 with its registered office at the address
set out above. VAT number 697904179.



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Graham O'Hara
Sent: 11 January 2008 13:32
To: [login to unmask email]
Subject: [DB2-L] Revoking a privilege from self (DB2 z/OS)

An Authorisation ID has granted DBADM to themselves, ie in
SYSIBM.SYSDBAUTH, one row has the same ID in GRANTOR and GRANTEE fields.

The question is how to revoke this privilege.

Using the Grantor/Grantee as the SQLID gives a -555 "AN AUTHORIZATION ID
CANNOT REVOKE A PRIVILEGE FROM ITSELF"

Using a SYSADM user as the SQLID gives a -556 "authid2 CANNOT HAVE THE
privilege PRIVILEGE on_object REVOKED BY authid1 BECAUSE THE REVOKEE
DOES NOT POSSESS THE PRIVILEGE OR THE REVOKER DID NOT MAKE THE GRANT"

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Graham O'Hara

Re: Revoking a privilege from self (DB2 z/OS)
(in response to Phil Grainger)
Thanks guys, the SET CURRENT RULES seemed to execute successfully, but
didn't actually remove the thread. So, I think it will a case of having to drop
and recreate objects... ho hum...

PS - I'm new on the list so not sure what text options work best - should I be
moving away from plain text?

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Chris Hoelscher

Re: Revoking a privilege from self (DB2 z/OS)
(in response to Graham O'Hara)
if the ueser in question created the DB, than the privileges cannot be
revoked - if that is NOT the case, than any SYSADM can revoke the
privileges with the BY ALL suffix to the revoke command.


Chris Hoelscher
Senior IDMS & DB2 Database Administrator
Humana Inc
502-476-2538
[login to unmask email]



The information transmitted is intended only for the person or entity to which it is addressed and may contain CONFIDENTIAL material. If you receive this material/information in error, please contact the sender and delete or destroy the material/information.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Raymond Bell

Re: Revoking a privilege from self (DB2 z/OS)
(in response to Chris Hoelscher)
Graham,

Remember I said that someone will end up being the owner of the object?
Point is you'll always have a row with the grantor matching the grantee.
If you wanna change that value, go ahead and
unload/drop/recreate/reload. But if you can live with it I wouldn't
bother.

Cheers,


Raymond
PS. DB2-L faux pas No: 1 - don't mention the font. I mentioned it once
but think I got away with it. (apologies to JC).

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Graham O'Hara
Sent: 11 January 2008 15:44
To: [login to unmask email]
Subject: Re: [DB2-L] Revoking a privilege from self (DB2 z/OS)

Thanks guys, the SET CURRENT RULES seemed to execute successfully, but
didn't actually remove the thread. So, I think it will a case of having
to drop
and recreate objects... ho hum...

PS - I'm new on the list so not sure what text options work best -
should I be
moving away from plain text?

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms