create or replace procedure p_roleprivs( v_role varchar2 
create or replace procedure p_roleprivs(
v_role varchar2
)
is
cursor c_rolesys is select PRIVILEGE from ROLE_SYS_PRIVS where role=upper(v_role);
cursor c_roletab is select PRIVILEGE,TABLE_NAME from ROLE_TAB_PRIVS where ROLE=upper(v_role);
begin
--dbms_output.put_line('ROLE_SYS_privs : '||v_role );
for i_rolesys in c_rolesys loop
dbms_output.put_line(i_rolesys.privilege);
end loop;
--dbms_output.put_line('ROLE_TAB_privs : '||v_role);
for i_roletab in c_roletab loop
dbms_output.put_line(i_roletab.privilege||' in '||i_roletab.TABLE_NAME);
end loop;
end;
/
create or replace procedure p_privs(
v_name varchar2
)
is
cursor c_sysprivs is select privilege from dba_sys_privs where GRANTEE=upper(v_name);
cursor c_tabprivs is select privilege,TABLE_NAME from dba_tab_privs where GRANTEE=upper(v_name);
cursor c_role is select GRANTED_ROLE from dba_role_privs where GRANTEE=upper(v_name);
begin
--dbms_output.put_line('SYS_privs : ');
for i_sys in c_sysprivs loop
dbms_output.put_line(i_sys.privilege);
end loop;
--dbms_output.put_line('TAB_privs : ');
for i_tab in c_tabprivs loop
dbms_output.put_line(i_tab.privilege||' in '||i_tab.TABLE_NAME);
end loop;
dbms_output.put_line(' ROLE start : ');
for i_role in c_role loop
p_roleprivs(i_role.GRANTED_ROLE);
end loop;
end;
/
======================================================
创建完 过程后直接调用
sql> exec p_privs('scott');
UNLIMITED TABLESPACE
READ in IMG
ROLE start :
CREATE SEQUENCE
CREATE TRIGGER
CREATE CLUSTER
CREATE PROCEDURE
CREATE TYPE
CREATE OPERATOR
CREATE TABLE
CREATE INDEXTYPE
CREATE TABLE
SELECT in EMP
CREATE ANY TABLE
CREATE SESSioN
PL/SQL procedure successfully completed.
--结束END--
本文标题: Oracle 查看 用户拥有权限的过程
本文链接: https://www.lsjlt.com/news/42543.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-07
2024-05-07
2024-05-07
2024-05-07
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0