How to show all privileges from a user in oracle

Question

Can someone please tell me how to show all privileges rules from a specific user in the sql-console

User · Answer

There are various scripts floating around that will do that depending on how crazy you want to get. I would personally use Pete Finnigan's find_all_privs script.

If you want to write it yourself, the query gets rather challenging. Users can be granted system privileges which are visible in DBA_SYS_PRIVS. They can be granted object privileges which are visible in DBA_TAB_PRIVS. And they can be granted roles which are visible in DBA_ROLE_PRIVS (roles can be default or non-default and can require a password as well, so just because a user has been granted a role doesn't mean that the user can necessarily use the privileges he acquired through the role by default). But those roles can, in turn, be granted system privileges, object privileges, and additional roles which can be viewed by looking at ROLE_SYS_PRIVS, ROLE_TAB_PRIVS, and ROLE_ROLE_PRIVS. Pete's script walks through those relationships to show all the privileges that end up flowing to a user.

User · Answer

More simpler single query oracle version  WITH data       AS  SELECT granted role           FROM   dba role privs           CONNECT BY PRIOR granted role   grantee           START WITH grantee     amp USER    SELECT  SYSTEM      typ          grantee      grantee          privilege    priv          admin option ad           --          tabnm           --          colnm           --          owner  FROM   dba sys privs  WHERE  grantee     amp USER           OR grantee IN  SELECT granted role                         FROM   data   UNION  SELECT  TABLE     typ          grantee    grantee          privilege  priv          grantable  ad          table name tabnm           --        colnm          owner      owner  FROM   dba tab privs  WHERE  grantee     amp USER           OR grantee IN  SELECT granted role                         FROM   data   ORDER  BY 1

User · Answer

You can use below code to get all the privileges list from all users   select   from dba sys privs

User · Answer

Another useful resource   http   psoug org reference roles html   DBA SYS PRIVS  DBA TAB PRIVS DBA ROLE PRIVS

User · Answer

While Raviteja Vutukuri s answer works and is quick to put together  it s not particularly flexible for varying the filters and doesn t help too much if you re looking to do something programmatically  So I put together my own query   SELECT     PRIVILEGE      OBJ OWNER      OBJ NAME      USERNAME      LISTAGG GRANT TARGET       WITHIN GROUP  ORDER BY GRANT TARGET  AS GRANT SOURCES  -- Lists the sources of the permission     MAX ADMIN OR GRANT OPT  AS ADMIN OR GRANT OPT  -- MAX acts as a Boolean OR by picking  YES  over  NO      MAX HIERARCHY OPT  AS HIERARCHY OPT -- MAX acts as a Boolean OR by picking  YES  over  NO  FROM       -- Gets all roles a user has  even inherited ones     WITH ALL ROLES FOR USER AS           SELECT DISTINCT CONNECT BY ROOT GRANTEE AS GRANTED USER  GRANTED ROLE         FROM DBA ROLE PRIVS         CONNECT BY GRANTEE   PRIOR GRANTED ROLE           SELECT         PRIVILEGE          OBJ OWNER          OBJ NAME          USERNAME          REPLACE GRANT TARGET  USERNAME   Direct to user   AS GRANT TARGET          ADMIN OR GRANT OPT          HIERARCHY OPT     FROM           -- System privileges granted directly to users         SELECT PRIVILEGE  NULL AS OBJ OWNER  NULL AS OBJ NAME  GRANTEE AS USERNAME  GRANTEE AS GRANT TARGET  ADMIN OPTION AS ADMIN OR GRANT OPT  NULL AS HIERARCHY OPT         FROM DBA SYS PRIVS         WHERE GRANTEE IN  SELECT USERNAME FROM DBA USERS          UNION ALL         -- System privileges granted users through roles         SELECT PRIVILEGE  NULL AS OBJ OWNER  NULL AS OBJ NAME  ALL ROLES FOR USER GRANTED USER AS USERNAME  GRANTEE AS GRANT TARGET  ADMIN OPTION AS ADMIN OR GRANT OPT  NULL AS HIERARCHY OPT         FROM DBA SYS PRIVS         JOIN ALL ROLES FOR USER ON ALL ROLES FOR USER GRANTED ROLE   DBA SYS PRIVS GRANTEE         UNION ALL         -- Object privileges granted directly to users         SELECT PRIVILEGE  OWNER AS OBJ OWNER  TABLE NAME AS OBJ NAME  GRANTEE AS USERNAME  GRANTEE AS GRANT TARGET  GRANTABLE  HIERARCHY         FROM DBA TAB PRIVS         WHERE GRANTEE IN  SELECT USERNAME FROM DBA USERS          UNION ALL         -- Object privileges granted users through roles         SELECT PRIVILEGE  OWNER AS OBJ OWNER  TABLE NAME AS OBJ NAME  GRANTEE AS USERNAME  ALL ROLES FOR USER GRANTED ROLE AS GRANT TARGET  GRANTABLE  HIERARCHY         FROM DBA TAB PRIVS         JOIN ALL ROLES FOR USER ON ALL ROLES FOR USER GRANTED ROLE   DBA TAB PRIVS GRANTEE       ALL USER PRIVS     -- Adjust your filter here     WHERE USERNAME    USER NAME    DISTINCT USER PRIVS GROUP BY     PRIVILEGE      OBJ OWNER      OBJ NAME      USERNAME     Advantages    I easily can filter by a lot of different pieces of information  like the object  the privilege  whether it s through a particular role  etc  just by changing that one WHERE clause  It s a single query  meaning I don t have to mentally compose the results together  It resolves the issue of whether they can grant the privilege or not and whether it includes the privileges for subobjects  the  hierarchical  part  across differences sources of the privilege  It s easy to see everything I need to do to revoke the privilege  since it lists all the sources of the privilege  It combines table and system privileges into a single coherent view  allowing us to list all the privileges of a user in one fell swoop  It s a query  not a function that spews all this out to DBMS OUTPUT or something  compared to Pete Finnigan s linked script   This makes it useful for programmatic use and for exporting  The filter is not repeated  it only appears once  This makes it easier to change  The subquery can easily be pulled out if you need to examine it by each individual GRANT

User · Answer

To show all privileges      select name from system privilege map

User · Answer

You can try these below views   SELECT   FROM USER SYS PRIVS   SELECT   FROM USER TAB PRIVS  SELECT   FROM USER ROLE PRIVS    DBAs and other power users can find the privileges granted to other users with the DBA  versions of these same views   They are covered in the documentation    Those views only show the privileges granted directly to the user   Finding all the privileges  including those granted indirectly through roles  requires more complicated recursive SQL statements   select   from dba role privs connect by prior granted role   grantee start with grantee     amp USER  order by 1 2 3  select   from dba sys privs  where grantee     amp USER  or grantee in  select granted role from dba role privs connect by prior granted role   grantee start with grantee     amp USER   order by 1 2 3  select   from dba tab privs  where grantee     amp USER  or grantee in  select granted role from dba role privs connect by prior granted role   grantee start with grantee     amp USER   order by 1 2 3 4

[sql] How to show all privileges from a user in oracle?

Examples related to sql

Examples related to oracle

Examples related to rules

Examples related to privileges