Shulou(Shulou.com)06/01 Report--

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

/

=

Call directly after the procedure is created

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.