CREATE OR REPLACE PROCEDURE grant_priv
/*
grant select and dml privilege to
user
*/
(strToUser VARCHAR2,
strPriv VARCHAR2 )
AS
strSql VARCHAR2(500);
lngCount NUMBER(10);
tab_name VARCHAR2(100);
CURSOR cur_tab IS
SELECT table_name FROM user_tables ORDER BY 1;
BEGIN
strSql:=' SELECT COUNT(*) FROM dba_users WHERE username='''||strToUser||'''';
EXECUTE IMMEDIATE strSql INTO lngCount ;
IF lngCount>0 THEN
--GRANT DELETE ON AM_DEPT TO LIS
IF strPriv ='SEL' THEN
OPEN cur_tab ;
LOOP
FETCH cur_tab INTO tab_name;
EXIT WHEN cur_tab%NOTFOUND;
strSql:='GRANT SELECT ON '||tab_name|| ' TO '|| strToUser;
EXECUTE IMMEDIATE strsql;
--dbms_output.put_line(strSql);
END LOOP;
CLOSE cur_tab;
END IF;
IF strPriv ='DML' THEN
OPEN cur_tab ;
LOOP
FETCH cur_tab INTO tab_name;
EXIT WHEN cur_tab%NOTFOUND;
strSql:='GRANT ALL ON '||tab_name|| ' TO '|| strTouser;
EXECUTE IMMEDIATE strsql;
--dbms_output.put_line(strsql);
END LOOP;
CLOSE cur_tab;
END IF;
END IF;
END grant_priv;
多年前写的内容，请测试后使用。