/

    create or replace package body ManPrivs is

    –Create type PrivsList is table of varchar2(4000);

    Type ColVar is table of varchar2(100);

    Type cur is ref cursor;

    v_grantee varchar2(30);

    v_owner varchar2(30);

    v_role varchar2(30);

    v_name varchar2(30);

    v_privs varchar2(60);

    v_separator varchar2(1):=‘,';

    Function FromStrToVar(v_string varchar2) return ColVar is

    v_sql varchar2(2000);

    v_var_col ColVar:=ColVar();

    v_pos number;

    v_var_num number:=1;

    begin

    if length(v_string)>0 then

    v_sql:=v_string;

    loop

    v_pos:=instr(v_sql,v_separator,1);

    if v_pos=0 or v_pos is null then

    v_var_col.extend;

    v_var_col(v_var_num):=v_sql;

    exit;

    end if;

    v_var_col.extend;

    v_var_col(v_var_num):=substr(v_sql,1,v_pos-1);

    v_sql:=substr(v_sql,v_pos+1);

    v_var_num:=v_var_num+1;

    end loop;

    end if;

    return v_var_col;

    end FromStrToVar;

    Function FromStrToStr(v_string varchar2) return varchar2 is

    v_sql varchar(2000);

    v_var_col ColVar:=ColVar();

    v_pos number;

    v_var_num number:=1;

    begin

    if length(v_string)>0 then

    v_sql:=v_string;

    loop

    v_pos:=instr(v_sql,v_separator,1);

    if v_pos=0 or v_pos is null then

    v_var_col.extend;

    v_var_col(v_var_num):=v_sql;

    exit;

    end if;

    v_var_col.extend;

    v_var_col(v_var_num):=substr(v_sql,1,v_pos-1);

    v_sql:=substr(v_sql,v_pos+1);

    v_var_num:=v_var_num+1;

    end loop;

    end if;

    v_sql:=“;

    for k in 1..v_var_col.count loop v_sql:=v_sql||“”||v_var_col(k)||“‘,';

    end loop;

    return upper(substr(v_sql,1,length(v_sql)-1));

    end FromStrToStr;

    Function ListTabPrivs(ObjOwner varchar2, ObjName varchar2 default null, Privs varchar2 default null, IsRevoke varchar2 default ‘N’,

    Grantee varchar2 default null) return PrivsList pipelined is

    c cur;

    v_sql varchar2(2000);

    begin

    v_sql:=‘select grantee,owner,table_name,privilege from dba_tab_privs where owner in (’||nvl(FromStrToStr(ObjOwner),‘owner’)||‘) and table_name in (’||nvl(FromStrToStr(ObjName),‘Table_name’)||‘) and privilege in (’||nvl(FromStrToStr(Privs),‘Privilege’)||‘) and grantee in (’||nvl(FromStrToStr(Grantee),‘Grantee’)||‘) order by 1,2 ’;

    –dbms_output.put_line(v_sql);

    open c for v_sql;

    loop

    fetch c into v_grantee,v_owner,v_name,v_privs;

    exit when c%notfound;

    if IsRevoke=‘Y’then

    pipe row(upper(‘Revoke ’||v_privs||‘ on ’||v_owner||‘.'||v_name||’ From ‘||v_grantee||’;'));

    else

    pipe row(upper(‘Grant ’||v_privs||‘ on ’||v_owner||‘.'||v_name||’ to ‘||v_grantee||’;'));

    end if;

    end loop;

    close c;

    return ;

    end;