How to Retrieve the Roles and Privileges Assigned to an Oracle User? Options

codeling Posts: 1100 Points: 4620
Posted: Friday, January 8, 2016 9:06:58 AM

The following SQL statement can retrieve an Oracle user ("dev" in this example) and his asigned rols and privileges information

select

  lpad(' ', 4*level) || granted_role "User, his roles and privileges"

from

  (

  /* THE USERS */

    select

      null     grantee,

      username granted_role

    from

      dba_users

    where

      username like upper('dev')

  /* THE ROLES TO ROLES RELATIONS */

  union

    select

      grantee,

      granted_role

    from

      dba_role_privs

  /* THE ROLES TO PRIVILEGE RELATIONS */

  union

    select

      grantee,

      privilege

    from

      dba_sys_privs

  )

start with grantee is null

connect by grantee = prior granted_role;

The result:

User, his roles and privileges

    DEV

        ALTER ANY PROCEDURE

        DEBUG ANY PROCEDURE

        DEBUG CONNECT SESSION

        DEVELOPER_ROLE

            ALTER ANY PROCEDURE

            ALTER ANY TRIGGER

            CREATE ANY PROCEDURE

            CREATE ANY TRIGGER

            CREATE SESSION

            CREATE TABLE

            DROP ANY PROCEDURE

            DROP ANY TRIGGER

            EXECUTE ANY PROCEDURE

            SELECT ANY SEQUENCE

        SELECT ANY SEQUENCE

        TEST_ROLE

            CREATE PROCEDURE

            CREATE SESSION

            CREATE TABLE

            CREATE TRIGGER

            CREATE TYPE

            EXECUTE ANY PROCEDURE

            SELECT ANY DICTIONARY

            SELECT ANY SEQUENCE

 

codeling Posts: 1100 Points: 4620
Posted: Friday, January 8, 2016 9:31:06 AM

To allow you run above SQL Statement, you have to be assigned privilege of  SELECT ANY DICTIONARY, otherwise you will get "Tabel or view does not exist" error. The reason why you get this error is because you have no access to Oracle directory tables/views: dba_users, dba_sys_privs and dba_sys_privs.

Users browsing this topic
Guest