Re: dba can't grant object permissions!

James Dickson wrote:
>
> I am trying to grant execute on a procedure that belongs
> to one user to another user, i.e. something like this:
>
> SQLDBA> GRANT EXECUTE ON "TOM"."TOMSTHING" TO "HARRY";
>
> and I'm getting the error:
> ORA-01031: insufficient privileges
>
> even though as DBA I have the 'grant any privilege' privilege.
>
> As a workaround I could change TOM's password,
> log in as TOM, grant execute priv to HARRY,
> log back in as DBA and change TOM's password back
> to what it was (I don't know what it is but I can
> see the encoded value). This is very messy. Is there
> an easier way, like granting myself some other privilege?
> Surely the DBA should be able to easily grant execute
> permissions on any users' objects to any other user?
>
> Thanks,
> James james_at_valhall.esrin.esa.it

Even though the DBA role seems to inherit the ability to grant permissions on other users' objects, this is not the case. Unless the DBA user has been given privileges on objects with the GRANT option, the DBA user cannot pass on privileges to others.

The only way to accomplish this, though I do not recommend, can be illustrated using the following simple example:

USERA owns PERSON

Log in as USERA and issue command

        grant execute on PERSON to DBA_USER with grant option;

then log in as DBA_USER and issue command

        grant execute on USERA.PERSON to OTHER_USER_OR_ROLE;

Michael

###################################################################
# Michael Abbey           Ottawa ON Canada      613 780 2364
#
# Co-author of:   Tuning Oracle          Oracle: A Beginner's Guide
#                 Oracle Data Warehousing
###################################################################