如何通过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中国

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

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

作者

佚名
佚名

相关推荐