对于如何获得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;