In order for this to work directly, using a simple "grant select on scott.dept to cluser", SCOTT would have needed to have granted:

SQL> grant select on dept to SYSTEM with grant option;

Now, a DBA has lots of powerful privileges - among them are:

o create any procedure
o execute any procedure

since by default a procedure executes as the "owner", we can use that to our advantage if we are in a pinch and really need to grant on someone elses objects. (no, this is not a bug, not a hole -- this is a direct side effect of having the incredibably POWERFUL privileges listed above).

If a user has "CREATE ANY PROCEDURE" and "EXECUTE ANY PROCEDURE" (eg:a DBA), the following script works well:

---------------------------------------------------------------
rem 1 = OWNER OF OBJECT
rem 2 = OBJECT NAME
rem 3 = PRIV to grant
rem 4 = WHO to grant to


show user

create procedure &1..dba_parse_$$( p_cur in number, p_str in varchar2 )
as
begin
dbms_sql.parse( p_cur, p_str, dbms_sql.native );
end;
/

column grantee format a8
column grantor format a8

select grantee, grantor, privilege from dba_tab_privs
where owner = upper('&1') and table_name = upper('&2')
/

declare
exec_cursor integer default dbms_sql.open_cursor;
rows_processed number default 0;
sql_stmt varchar2(255);
begin
sql_stmt := 'grant &3 on &1..&2 to &4';
&1..dba_parse_$$( exec_cursor, sql_stmt );
rows_processed := dbms_sql.execute(exec_cursor);
dbms_sql.close_cursor( exec_cursor );
end;
/

select grantee, grantor, privilege from dba_tab_privs
where owner = upper('&1') and table_name = upper('&2')
/

drop procedure &1..dba_parse_$$;
-------------------------------------------------------------



You would run it as such:


SQL> @dogrant scott emp all public

user is "TKYTE"
old 1: create procedure &1..dba_parse_$$( p_cur in number, p_str in varchar2 )
new 1: create procedure scott.dba_parse_$$( p_cur in number, p_str in varchar2
)

Procedure created.

old 2: where owner = upper('&1') and table_name = upper('&2')
new 2: where owner = upper('scott') and table_name = upper('emp')

no rows selected

old 6: sql_stmt := 'grant &3 on &1..&2 to &4';
new 6: sql_stmt := 'grant all on scott.emp to public';
old 7: &1..dba_parse_$$( exec_cursor, sql_stmt );
new 7: scott.dba_parse_$$( exec_cursor, sql_stmt );

PL/SQL procedure successfully completed.

old 2: where owner = upper('&1') and table_name = upper('&2')
new 2: where owner = upper('scott') and table_name = upper('emp')

GRANTEE GRANTOR PRIVILEGE
-------- -------- ----------------------------------------
PUBLIC SCOTT ALTER
PUBLIC SCOTT DELETE
PUBLIC SCOTT INDEX
PUBLIC SCOTT INSERT
PUBLIC SCOTT SELECT
PUBLIC SCOTT UPDATE
PUBLIC SCOTT REFERENCES

7 rows selected.

old 1: drop procedure &1..dba_parse_$$
new 1: drop procedure scott.dba_parse_$$

Procedure dropped.

SQL>
===============================================================

that shows that it worked (the grants were in fact given) without logging in as the other user.