execute immediate ‘revoke ’||v_privs||‘ on ’||v_owner||‘.'||v_name||’ from ‘||v_grantee;

    end loop;

    close c;

    exception

    when others then

    dbms_output.put_line(sqlerrm);

    end;

    Procedure GrantTabPrivs(ObjOwner varchar2, ObjName varchar2, Privs varchar2,

    Grantee varchar2) is

    c cur;

    v_sql varchar2(2000);

    v_privs_list ColVar:=ColVar();

    v_grantee_list ColVar:=ColVar();

    v_str varchar2(2000);

    begin

    v_sql:=‘select owner,table_name from dba_tables where owner in (’||nvl(FromStrToStr(ObjOwner),‘owner’)||‘) and table_name in (’||nvl(FromStrToStr(ObjName),‘Table_name’)||‘) order by 1,2 ’;

    open c for v_sql;

    loop

    fetch c into v_owner,v_name;

    exit when c%notfound;

    v_privs_list:=FromStrToVar(Privs);

    v_grantee_list:=FromStrToVar(Grantee);

    for i in 1..v_privs_list.count loop for j in 1..v_grantee_list.count loop

    begin

    dbms_output.enable;

    dbms_output.put_line(‘grant ’||v_privs_list(i)||‘ on ’||v_owner||‘.'||v_name||’ to ‘||v_grantee_list(j)||’;');

    exception

    when others then null;

    end;

    v_str:=‘grant ’||v_privs_list(i)||‘ on ’||v_owner||‘.'||v_name||’ to ‘||v_grantee_list(j);

    execute immediate v_str;

    end loop;

    end loop;

    end loop;

    close c;

    exception

    when others then

    dbms_output.put_Line(sqlerrm);

    end;

    end ManPrivs;

    /

    3:查询示例:

    SQL>connect hr/hr

    SQL>GRANT ALL ON EMP TO PUBLIC;

    SQL>select * from table(manprivs.ListTabPrivs(‘hr’,'emp‘));

    RESULT:

    GRANT ALTER ON HR.EMP TO PUBLIC;

    GRANT DELETE ON HR.EMP TO PUBLIC;

    GRANT INDEX ON HR.EMP TO PUBLIC;

    GRANT INSERT ON HR.EMP TO PUBLIC;

    GRANT SELECT ON HR.EMP TO PUBLIC;

    GRANT UPDATE ON HR.EMP TO PUBLIC;

    GRANT REFERENCES ON HR.EMP TO PUBLIC;

    GRANT ON COMMIT REFRESH ON HR.EMP TO PUBLIC;

    GRANT QUERY REWRITE ON HR.EMP TO PUBLIC;

    GRANT DEBUG ON HR.EMP TO PUBLIC;

    GRANT FLASHBACK ON HR.EMP TO PUBLIC;