Re : user listed as grantee multiple times for a table

Re : user listed as grantee multiple times for a table

 

  

You will need to revoke CONTROL or any/all privileges higher than what you want GROUP1 to have.
If select is the only thing, hyou need to revoke each and every one of the explicit grant done to GROUP1 and leave only select..

If this applies to only one user in GROUP1 you<ll have to first take the userid out of GROUP1 and grant select to that id.
Hope this helps, Pierre.

----- Message d'origine -----
De: DanLuchinski <db2udbdba-ezmlmshield-x70195997.[Email address protected]
Date: Vendredi, Juin 29, 2007 10:09 am
Objet: user listed as grantee multiple times for a table
À: LazyDBA Discussion <[Email address protected]

> Gurus,
>
> Could you let me know if there is an issue with having a user
> (or group)
> having more than one access to a table? I know this probably
> doesn't sound
> clear, so maybe this will help...
>
> If I query the syscat.tabauth table, I find grantee GROUP1 shows
> up twice
> for table A. GRANTORA gave only select privs on the table, and
> GRANTORB
> gave full access to the table.
>
> We recently migrated to a new server by restoring a backup for
> the
> database. Now, a user that is part of GROUP1 can't update the
> table. I
> was thinking it might have to do with having the two different
> grants.
> Maybe it worked before because the full access was granted
> later, but with
> the restore, the grants reversed order???
>
> Anyway, I tried to revoke all access. However, I get ...
>
> Error during SQLExecute
> 42504(-558)[IBM][CLI Driver][DB2/NT] SQL0558N An attempt to
> revoke a
> privilege from "GROUP1" was denied because "GROUP1" would still
> hold
> "CONTROL" authority. SQLSTATE=42504
>
> Any help would be greatly appreciated.
>
> Thanks,
>
> Dan Luchinski
> Database Administrator
> ASPCA Knowledge Management Center
> University of Illinois Allied Agency
>
>
> -----------------------------------------------------------------
> ----
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>

Pierre Saint-Jacques

DB2 & UDB email list listserv db2-l LazyDBA home page