Oracle privileges

Enabled privileges

When a new session is started, the initial privileges to the connecting user are the combination of

those that are directly granted to the user and
those that are in the user's default roles

When connected, the user can change the enabled roles (and thus the enabled privileges) with the set role statement.

Data dictionary views

The data dictionary views that start with ALL_ show objects on which the querying user has privileges.

In addition, the data dictionary also features the following views which list privileges that a user has

`dba_role_privs`	Shows granted roles to other roles and users
`dba_tab_privs`	Lists which object privileges (such as `select` etc on tables or `execute` on packages) are granted to users and roles.
`dba_col_privs`	Lists privileges on columns.
`dba_sys_privs`	Lists which system privileges (such as `create table`) are granted to users and roles.
`role_role_privs`	Lists the roles that are granted to other roles (only roles to which the user has access are listed).
`role_sys_privs`	Lists which system privileges are assigned to the roles to which the current user has access.
`role_tab_privs`	Lists which object privileges are assigned to the roles to which the current user has access.
`session_roles`	Lists all roles that are enabled in the current session.
`session_privs`	Lists all system privileges that are enabled in the current session.
`system_privilege_map`

Dynamic performance views

v$pwfile_users shows users and their granted administrative privileges (if at least one was granted to a user).

Query select privileges hierarchically

Hierarchical result

The following query hierarchically selects, depending on what lines are commented, a user's system or objects privileges:

with roles_of_user (
   user_or_role,
   lvl
)as (
   select
     'RENE'  user_or_role,
      0              lvl
   from
      dual
union all
    select
       drp.granted_role,
       rou.lvl+1
    from
       roles_of_user    rou                                         join
       dba_role_privs   drp on rou.user_or_role = drp.grantee     
)
search depth first by user_or_role set order_by 
select
   case when nvl( LAG( rou.order_by ) over (order by rou.order_by),  -1 ) <>  rou.order_by then  lpad(' ', rou.lvl*3) || user_or_role end user_or_role,
   prv.owner, prv.table_name, prv.privilege, prv.type, prv.grantor, prv.grantable
-- sys.privilege, sys.admin_option-- , sys.common, sys.inherited
from
   roles_of_user rou                                                left  join
   dba_tab_privs prv on rou.user_or_role = prv.grantee
-- dba_sys_privs sys on rou.user_or_role = sys.grantee
order by
   rou.order_by
;

Selecting «granted role path»

The following query selects all «granted-role-paths» from tables via roles to a user. Each path is selected in one record.

The interesting tables are specified in tabs, the users in usrs.

with tabs as (
   select
       owner       own,
       object_name nam,
       object_type typ
    from
       dba_objects
    where
       owner = 'RENE'           and
       object_name like 'OBJ%'  and
       object_type in ('VIEW', 'TABLE')
),
usrs as (
   select 'USR1'                nam from dual union all
   select 'USR2'                nam from dual union all
   select 'USR3'                nam from dual
),
roles_of_user (
   user_or_role,
   lvl,
   path_,
   usr
) as (
   select
      nam      user_or_role,
      0        lvl_,
      nam      path_,
      nam      usr
   from
      usrs
union all
   select
      drp.granted_role,
      rou.lvl+1,
      drp.granted_role || ' -> ' || rou.path_,
      rou.usr
   from
      roles_of_user   rou                                           join
      dba_role_privs  drp  on rou.user_or_role = drp.grantee
)
search depth first by user_or_role set order_by
select
   tabs.own,
   tabs.nam,
   tabs.typ,
-- prvs.grantee,
   rous.usr,
   prvs.privilege,
   rous.path_,
   prvs.grantor,
   prvs.grantable
from
   tabs                                                                        join
   dba_tab_privs prvs on tabs.own = prvs.owner        and
                         tabs.nam = prvs.table_name   and
                         tabs.typ = prvs.type                                  join
   roles_of_user rous on prvs.grantee = rous.user_or_role
-- where
--   path_ is not null
order by
   tabs.nam
;