SQL -555: AN AUTHORIZATION ID CANNOT REVOKE A PRIVILEGE FROM ITSELF

Hans Landaal

SQL -555: AN AUTHORIZATION ID CANNOT REVOKE A PRIVILEGE FROM ITSELF
Recently I received a request to revoke a users DBADM authority. According
to the info in table SYSIBM.SYSDBAUTH the grantee is the same as the
grantor. The grantor has CREATEDBA authority. You probably can guess where
this is going. When I execute the following SQL I receive a '-555 ,an
authid cannot revoke a privilige from itself'.
Executed SQL:
SET CURRENT SQLID = 'userid1' ;
REVOKE DBADM ON DATABASE dbname FROM userid1 ;

When I change the SQL and add the SET CURRENT RULES ='STD' on top there is
no error, DB2 even says 'successful execution', but the authority is still
there. Also I tried to revoke with the install SYSADM, but got errors
again.
The DBADM authority was granted in 1989!!! I don't know which version of
DB2 was running at that time. We are running V5 now.
Does anybody know how I can get rid off the DBADM authority?

Hans



[login to unmask email]

Re: SQL -555: AN AUTHORIZATION ID CANNOT REVOKE A PRIVILEGE FROM ITSELF
(in response to Hans Landaal)
My guess is that userid1 is the creator of the database and thus gets all
privileges including DBADM automatically. The only way to revoke DBADM from
userid1 would be to drop the database and recreate it with another id as
creator.


Regards

Chris Tee

DB2 Systems Programmer
IBM UK DB/DC Systems Support
North Harbour



Hans Landaal <[login to unmask email]> on 06/01/2000 11:24:49

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Chris Tee/UK/Contr/IBM)
Subject: SQL -555: AN AUTHORIZATION ID CANNOT REVOKE A PRIVILEGE FROM
ITSELF




Recently I received a request to revoke a users DBADM authority. According
to the info in table SYSIBM.SYSDBAUTH the grantee is the same as the
grantor. The grantor has CREATEDBA authority. You probably can guess where
this is going. When I execute the following SQL I receive a '-555 ,an
authid cannot revoke a privilige from itself'.
Executed SQL:
SET CURRENT SQLID = 'userid1' ;
REVOKE DBADM ON DATABASE dbname FROM userid1 ;

When I change the SQL and add the SET CURRENT RULES ='STD' on top there is
no error, DB2 even says 'successful execution', but the authority is still
there. Also I tried to revoke with the install SYSADM, but got errors
again.
The DBADM authority was granted in 1989!!! I don't know which version of
DB2 was running at that time. We are running V5 now.
Does anybody know how I can get rid off the DBADM authority?

Hans








Lynne Flatley

Re: SQL -555: AN AUTHORIZATION ID CANNOT REVOKE A PRIVILEGE FROM ITSELF
(in response to TEECHRI@UK.IBM.COM)
I'm going to assume that your own ID has sysadm authority...execute the
revoke from userid1 again but don't set the current SQLID to userid1.
Execute the revoke under your own ID.

> -----Original Message-----
> From: Chris Tee [SMTP:[login to unmask email]
> Sent: Thursday, January 06, 2000 6:52 AM
> To: [login to unmask email]
> Subject: Re: SQL -555: AN AUTHORIZATION ID CANNOT REVOKE A PRIVILEGE
> FROM ITSELF
>
> My guess is that userid1 is the creator of the database and thus gets all
> privileges including DBADM automatically. The only way to revoke DBADM
> from
> userid1 would be to drop the database and recreate it with another id as
> creator.
>
>
> Regards
>
> Chris Tee
>
> DB2 Systems Programmer
> IBM UK DB/DC Systems Support
> North Harbour
>
>
>
> Hans Landaal <[login to unmask email]> on 06/01/2000 11:24:49
>
> Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
> To: [login to unmask email]
> cc: (bcc: Chris Tee/UK/Contr/IBM)
> Subject: SQL -555: AN AUTHORIZATION ID CANNOT REVOKE A PRIVILEGE FROM
> ITSELF
>
>
>
>
> Recently I received a request to revoke a users DBADM authority. According
> to the info in table SYSIBM.SYSDBAUTH the grantee is the same as the
> grantor. The grantor has CREATEDBA authority. You probably can guess where
> this is going. When I execute the following SQL I receive a '-555 ,an
> authid cannot revoke a privilige from itself'.
> Executed SQL:
> SET CURRENT SQLID = 'userid1' ;
> REVOKE DBADM ON DATABASE dbname FROM userid1 ;
>
> When I change the SQL and add the SET CURRENT RULES ='STD' on top there is
> no error, DB2 even says 'successful execution', but the authority is still
> there. Also I tried to revoke with the install SYSADM, but got errors
> again.
> The DBADM authority was granted in 1989!!! I don't know which version of
> DB2 was running at that time. We are running V5 now.
> Does anybody know how I can get rid off the DBADM authority?
>
> Hans
>
>
>
> the
>
>
>
>
>
>
>



Ali OZTURK

Re: SQL -555: AN AUTHORIZATION ID CANNOT REVOKE A PRIVILEGE FROM ITSELF
(in response to DB2DBAinTX@AOL.COM)

Hi Hans,

Can you try the following statement please ?
REVOKE DBADM ON DATABASE dbname FROM userid1 BY userid1;

-----Original Message-----
From: Hans Landaal [mailto:[login to unmask email]
Sent: Thursday, January 06, 2000 1:25 PM
To: [login to unmask email]
Subject: SQL -555: AN AUTHORIZATION ID CANNOT REVOKE A PRIVILEGE FROM
ITSELF


Recently I received a request to revoke a users DBADM authority. According
to the info in table SYSIBM.SYSDBAUTH the grantee is the same as the
grantor. The grantor has CREATEDBA authority. You probably can guess where
this is going. When I execute the following SQL I receive a '-555 ,an
authid cannot revoke a privilige from itself'.
Executed SQL:
SET CURRENT SQLID = 'userid1' ;
REVOKE DBADM ON DATABASE dbname FROM userid1 ;

When I change the SQL and add the SET CURRENT RULES ='STD' on top there is
no error, DB2 even says 'successful execution', but the authority is still
there. Also I tried to revoke with the install SYSADM, but got errors
again.
The DBADM authority was granted in 1989!!! I don't know which version of
DB2 was running at that time. We are running V5 now.
Does anybody know how I can get rid off the DBADM authority?

Hans





[login to unmask email]

Re: SQL -555: AN AUTHORIZATION ID CANNOT REVOKE A PRIVILEGE FROM ITSELF
(in response to Lynne Flatley)
You can either unload all the data out of all the tables in the database,
drop the database, create the database, tablespaces, tables, indexes, foreign
keys, primary keys, view, aliases, synonyms
or
Change macro DSN6SPRC to &SPRMCTU SETC '1''
and reassemble ZPARM and update the catalog with the new owner.

Beware, you only update the catalog if you know what you are doing. After
the updates, I would change the parm back to disable unwanted updates.