Oracle to view the process that a user has permissions
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.
