Function ListSysPrivs(ObjOwner varchar2 default null) return PrivsList pipelined is

    c cur;

    v_sql varchar2(2000);

    begin

    v_sql:=‘select grantee,privilege from dba_sys_privs where grantee in (’||nvl(FromStrToStr(ObjOwner),‘Grantee’)||‘)

    order by 1‘;

    –dbms_output.put_line(v_sql);

    open c for v_sql;

    loop

    fetch c into v_owner,v_privs;

    exit when c%notfound;

    pipe row(upper(rpad(v_owner,30,‘ ’)||‘ : ’||v_privs));

    end loop;

    close c;

    return ;

    end;

    Function ListRolePrivs(ObjOwner varchar2 default null) return PrivsList pipelined is

    c cur;

    v_sql varchar2(2000);

    begin

    v_sql:=‘select grantee,Granted_Role from dba_role_privs where grantee in (’||nvl(FromStrToStr(ObjOwner),‘Grantee’)||‘)

    order by 1‘;

    –dbms_output.put_line(v_sql);

    open c for v_sql;

    loop

    fetch c into v_owner,v_privs;

    exit when c%notfound;

    pipe row(upper(rpad(v_owner,30,‘ ’)||‘ : ’||v_privs));

    end loop;

    close c;

    return ;

    end;

    Function ListRoleSysPrivs(ObjOwner varchar2 default null) return PrivsList pipelined is

    c cur;

    v_sql varchar2(2000);

    begin

    v_sql:=‘select s1.grantee,s1.granted_role,s2.privilege from dba_role_privs s1,role_sys_privs s2 where s1.granted_role=s2.role and s1.grantee in (’||nvl(FromStrToStr(ObjOwner),‘s1.Grantee’)||‘)

    order by 1‘;

    –dbms_output.put_line(v_sql);

    open c for v_sql;

    loop

    fetch c into v_owner,v_role,v_privs;

    exit when c%notfound;

    pipe row(upper(rpad(v_owner,30,‘ ’)||‘ : ’||rpad(v_role,30,‘ ’)||‘ : ’||v_privs));

    end loop;

    close c;

    return ;

    end;

    Function ListRoleTabPrivs(ObjOwner varchar2 default null) return PrivsList pipelined is

    c cur;

    v_sql varchar2(2000);

    begin

    v_sql:=‘select s1.grantee,s1.granted_role,s2.owner,s2.table_name,s2.privilege from dba_role_privs s1,role_tab_privs s2 where s1.granted_role=s2.role and s1.grantee in (’||nvl(FromStrToStr(ObjOwner),‘s1.Grantee’)||‘)

    order by 1‘;

    –dbms_output.put_line(v_sql);

    open c for v_sql;

    loop

    fetch c into v_grantee,v_role,v_owner,v_name,v_privs;

    exit when c%notfound;

    pipe row(upper(rpad(v_grantee,30,‘ ’)||rpad(v_role,30,‘ ’)||‘ : ’||‘ : ’||rpad(v_owner,30,‘ ’)||‘ : ’||rpad(v_name,30,‘ ’)||‘ : ’||v_privs));

    end loop;

    close c;

    return ;

    end;

    Procedure RevokeTabPrivs(ObjOwner varchar2 , ObjName varchar2 default null, Privs varchar2 default null,

    Grantee varchar2 default null) 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 ’;

    open c for v_sql;

    loop

    fetch c into v_grantee,v_owner,v_name,v_privs;

    exit when c%notfound;

    begin

    dbms_output.enable;

    dbms_output.put_line(upper(‘revoke ’||v_privs||‘ on ’||v_owner||‘.'||v_name||’ from ‘||v_grantee||’;'));

    exception

    when others then null;

    end;