如何通过TABLE FUNCTION获得Oracle权限

日期： 2009-11-09 作者：佚名 来源：TechTarget中国

　　对于如何获得Oracle对象权限、系统权限、角色权限，Oracle提供了以DBA_* ROLE_* SESSION_* 等开头的系统视图供用户查询权限分配的信息，我们也可以通过OEM等其他工具来得到这些权限，在这里我们如果通过TABLE FUNCTION来获得这些权限.声明:由于获得权限需要系统视图的支持，我们需要dba_sys_privs，dba_role_privs，role_sys_privs，role_tab_privs，dba_tables有读权限，当然，可以把下面的包建立在sys用户下面，也可以通过授权模式在其他用户上使用.

　　创建包:

　　1:创建对象

　　SQL>create type PrivsList is table of varchar2(4000);

　　SQL>/

　　2:创建包

　　包中只列举了一些常用的获得权限的功能

　　create or replace package ManPrivs is

　　—Author : LIYAN

　　—Created : 2009-8-26 10:06:18

　　—Purpose :

　　–执行此包中过程需要对dba_sys_privs，dba_role_privs，role_sys_privs，role_tab_privs，dba_tables有读权限

　　–并且拥有DBA授权能力

　　–建议系统权限以及角色权限授权、权限回收操作使用OEM完成

　　–查询角色包含角色权限建议使用OEM完成

　　Function ListTabPrivs(ObjOwner varchar2， ObjName varchar2 default null， Privs varchar2 default null， IsRevoke varchar2 default ‘N’，
　　Grantee varchar2 default null) return PrivsList pipelined;
　　/*查看对象的表格权限
　　Example:
　　select * from table(manprivs.ListTabPrivs(‘sh，hr’，“，‘select，update，delete，insert’，’N‘，’PUBLIC’));
　　*/
　　Function ListSysPrivs(ObjOwner varchar2 default null) return PrivsList pipelined;
　　/*产看对象的系统权限
　　Example:
　　select * from table(ManPrivs.ListSysPrivs(‘sh，hr’));
　　*/
　　Function ListRolePrivs(ObjOwner varchar2 default null) return PrivsList pipelined ;
　　/*查看对象的角色权限
　　Example:
　　select * from table(ManPrivs.ListRolePrivs(‘sh，hr’));
　　*/
　　Function ListRoleSysPrivs(ObjOwner varchar2 default null) return PrivsList pipelined ;
　　/*查看对象拥有角色的系统权限
　　Example:
　　select * from table(ManPrivs.ListRoleSysPrivs(‘sh，hr’));
　　*/
　　Function ListRoleTabPrivs(ObjOwner varchar2 default null) return PrivsList pipelined ;
　　/*查看对象拥有角色的表格权限
　　Example:
　　SELECT * fROM TABLE(MANPRIVS.ListRoleTabPrivs(‘SH，HR’))
　　*/
　　–Function ListRoleRolePrivs(ObjOwner varchar2 default null) return PrivsList pipelined ;
　　Procedure RevokeTabPrivs(ObjOwner varchar2， ObjName varchar2 default null， Privs varchar2 default null，
　　Grantee varchar2 default null);
　　/*回收对象的表格权限
　　Example:
　　Execute ManPrivs.RevokeTabPrivs(‘HR’，“，”，‘PUBLIC’)
　　*/
　　Procedure GrantTabPrivs(ObjOwner varchar2， ObjName varchar2， Privs varchar2，
　　Grantee varchar2);
　　/*对特定的对象进行授予特殊权限
　　Example:
　　Execute ManPrivs.GrantTabPrivs(‘hr’，’jobs‘，’select，update，insert‘，’public’)
　　*/
　　end ManPrivs;
　　/
　　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;
　　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;
　　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;

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论，本站保留删除与本文无关和不雅评论的权力。

作者

佚名
佚名

相关推荐