When you choose 'the user with DBA privileges' to do these grants, please be aware that those grants will disappear if the granting user is dropped, or loses the privileges necessary to perform those grants.

So I advise against doing these grants from a 'personal' user who has DBA privileges. Instead, create a DBA user account for this purpose which will be 'permanent', and will not get dropped as a result of someone leaving the company.

Of course, if the grant is issued by the object owner, then dropping the object-owning user will also involve dropping all of its objects, so that effect is not an issue.

You may also pro-actively find such grants that have already been issued, and re-issue them from a more permanent account if needed. Start with a query like this:
select * from dba_tab_privs where owner <> grantor;

This caution only applies to object grants - not grants of roles or grants of system privileges.

When you choose 'the user with DBA privileges' to do these grants, please be aware that those grants will disappear if the granting user is dropped, or loses the privileges necessary to perform those grants.

So I advise against doing these grants from a 'personal' user who has DBA privileges. Instead, create a DBA user account for this purpose which will be 'permanent', and will not get dropped as a result of someone leaving the company.

Of course, if the grant is issued by the object owner, then dropping the object-owning user will also involve dropping all of its objects, so that effect is not an issue.

You may also pro-actively find such grants that have already been issued, and re-issue them from a more permanent account if needed. Start with a query like this:
select * from dba_tab_privs where owner <> grantor;

This caution only applies to object grants - not grants of roles or grants of system privileges.

Hi Tom,

Thank you so much for the quick response.

We do not want to grant DBA privilege to this admin user, so we will do grant xxxx with GRANT OPTION;

I cannot duplicate what commentator Michael Tefft claims. Tom, could you comment on his assertion?  This is run against a 10g Express Edition:

H:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 5 16:46:38 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> connect /
Connected.
SQL> create user a identified by a;

User created.

SQL> create user d identified by d;

User created.

SQL> grant dba to d;

Grant succeeded.

SQL> connect d/d
Connected.
SQL> grant select on hr.employees to a;

Grant succeeded.

SQL> connect /
Connected.
SQL> select count(*)
  2    from dba_tab_privs p
  3   where p.owner = 'HR'
  4     and p.table_name = 'EMPLOYEES'
  5     and p.grantee = 'A'
  6     and p.privilege = 'SELECT';

  COUNT(*)
----------
         1

SQL> drop user d;

User dropped.

SQL> select count(*)
  2    from dba_tab_privs p
  3   where p.owner = 'HR'
  4     and p.table_name = 'EMPLOYEES'
  5     and p.grantee = 'A'
  6     and p.privilege = 'SELECT';

  COUNT(*)
----------
         1

I apologize; the use of GRANT ANY OBJECT PRIVILEGE (which comes with DBA) does not cause this effect.
http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authorization.htm#i1007808
(That is 11g; I can't get to the 10g equivalent at the moment)

When the GRANT ANY OBJECT PRIVILEGE privilege is used, the owner is still recorded as the 'grantor:

06:41:32 TEST_GRANTOR> select grantee, grantor, privilege from dba_tab_privs
06:42:13   2  where owner = 'TEST_OWNER' and table_name = 'T';

no rows selected

Elapsed: 00:00:00.04
06:42:13 TEST_GRANTOR> 
06:42:13 TEST_GRANTOR> grant select on test_owner.t to test_grantee;

Grant succeeded.

Elapsed: 00:00:00.04
06:42:13 TEST_GRANTOR> 
06:42:13 TEST_GRANTOR> select grantee, grantor, privilege from dba_tab_privs
06:42:13   2  where owner = 'TEST_OWNER' and table_name = 'T';

GRANTEE                        GRANTOR                        PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
TEST_GRANTEE                   TEST_OWNER                     SELECT

1 row selected.

Elapsed: 00:00:00.00
06:42:13 TEST_GRANTOR>

According to the Security Guide: If the grantor also has the object privilege WITH GRANT OPTION when making the grant, then the grant is recorded with the grantor as GRANTOR. Then the above caveat would apply.

We have been bitten by this before; as you can imagine, once the granting user is dropped, it is difficult to reconstruct what the prior state was, without significant effort.

I will be researching this some more, as it seems even more unusual to have GRANTOR <> OWNER, given our practices here.