sql help please

Hi,

I have a user A who owns several tables. Now, I want to grant select, update, delete, insert on all of user A's tables to user B. For this, I have written the following in grant.sql which I run after logging in as user A:

grant.sql:

declare
cursor c_tables is select table_name from user_tables;
v_tables c_tables%ROWTYPE;

begin
open c_tables;
loop
fetch c_tables into v_tables;
exit when c_tables%notfound;
grant select, insert, update, delete on v_tables.table_name to B;
end loop;
close c_tables;
end;
/

But this does not work. It says, "grant not allowed". How can I correct this, or is there a better way to do it ?

Thanks in advance for your help

use dynamic sql as user A:

set verify off
set term off
set heading off
set feedback off
set pause off
set trimspool on
set linesize 1024
set pagesize 0
clear breaks
clear columns

spool grant.sql

select 'grant select, insert, update, delete on '||
table_name || ' to USER_A;'
from user_tables;

spool off
@grant.sql

It's because grant is data control language(not allowed in procedures) and not DML.
You have to use EXECUTE IMMEDIATE for 8 version or
use dbms_sql package for version bellow 8:


here a procedure for delete a oracle user (data definition language)
=====================================
PROCEDURE DeleteUser(user_id IN NUMBER,
errorcode OUT NUMBER)
IS
v_CreateCommand VARCHAR2(120);
v_Dummy INTEGER;
v_CursorID INTEGER;
ausername VARCHAR2(80);
BEGIN
errorcode :=0;
ausername:='';
SELECT login_id INTO ausername FROM broker_codes
WHERE brc_id = user_id;
UPDATE BROKER_CODES
SET login_id = ''
Where brc_id = user_id;
COMMIT;
v_CreateCommand := 'DROP USER ' || ausername ;
v_CursorID := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_CursorID, v_CreateCommand, DBMS_SQL.NATIVE);
v_dummy := DBMS_SQL.EXECUTE(v_CursorID);
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
EXCEPTION
WHEN OTHERS THEN
errorcode:= SQLCODE;
End DeleteUser;

Thanks for your prompt reply.