oracle角色权限，系统权限和对象权限

--角色权限

 

select t2.n2, t2.grantee, substr(t2.str, 2)

  from (select rownum n2,

               t1.grantee,

               sys_connect_by_path(granted_role, ', ') str

          from (select rownum n1, t.* from dba_role_privs t) t1

         where t1.n1 in (select max(m.n)

                           from (select rownum n, t.* from dba_role_privs t) m

                          group by m.grantee)

        connect by n1 - 1 = prior n1

               and grantee = prior grantee) t2

 where t2.n2 in (select min(m.n)

                   from (select rownum n, t.* from dba_role_privs t) m

                  group by m.grantee)

--系统权限

 

select t2.n2, t2.grantee, substr(t2.str, 2)

  from (select rownum n2,

               t1.grantee,

               sys_connect_by_path(privilege, ',') str

          from (select rownum n1, t.* from dba_sys_privs t) t1

         where t1.n1 in (select max(m.n)

                           from (select rownum n, t.* from dba_sys_privs t) m

                          group by m.grantee)

        connect by n1 - 1 = prior n1

               and grantee = prior grantee) t2

 where t2.n2 in (select min(m.n)

                   from (select rownum n, t.* from dba_sys_privs t) m

                  group by m.grantee)

--对象权限

select ROWNUM n, t.* from dba_tab_privs t where t.owner = ？;