Oracle查看用户拥有权限的过程

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;
/

创新互联长期为1000+客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为文安企业提供专业的成都网站设计、成都做网站、外贸网站建设，文安网站改版等技术服务。拥有十余年丰富建站经验和众多成功案例,为您定制开发。

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.

文章名称：Oracle查看用户拥有权限的过程
网页路径：http://azwzsj.com/article/gppcjc.html

Oracle查看用户拥有权限的过程

其他资讯