6 Replies Latest reply: Feb 25, 2011 1:01 PM by Ravi Kumar Pilla RSS

    How to check the privileges assigned to a role

    756020
      Hi All,

      Can you please let me know how to check the privileges assigned to a role in Oracle?

      When I query the dba_tab_privs it says no rows returned.

      Please help..

      Regards,
      Dan
        • 1. Re: How to check the privileges assigned to a role
          asifkabirdba
          -- select all the role name and granted role

          select *
          from role_role_privs
          order by 1;


          -- select all the roles and their privilages

          select *
          from role_sys_privs
          where ROLE = 'TABQ'
          order by 1;

          ------------------------------------------------------

          -- show all object privilages in a role

          select *
          from role_tab_privs
          where role = 'TABQ';


          Regards
          Asif Kabir


          Handle:      user9212851
          Status Level:      Newbie
          Registered:      Feb 24, 2010
          Total Posts:      20
          Total Questions: *7 (6 unresolved)*


          -- Mark your helpful post as correct/helpful and close all the answered threads.
          • 2. Re: How to check the privileges assigned to a role
            731759
            You can use the below views to find role related informations,

            DBA_ROLE_PRIVS - Roles granted to users and roles
            ROLE_ROLE_PRIVS - Roles which are granted to roles
            ROLE_SYS_PRIVS - System privileges granted to roles
            ROLE_TAB_PRIVS - Table privileges granted to roles

            Thanks
            • 3. Re: How to check the privileges assigned to a role
              Chinar
              Also query from
              dba_role_privs,
              dba_sys_privs,
              role_sys_privs,
              role_role_privs
              • 4. Re: How to check the privileges assigned to a role
                Pavan Kumar
                Hi,

                Oracle online manual are available. Just spend some time...
                http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/admusers.htm#i1008832

                - Pavan Kumar N
                • 5. Re: How to check the privileges assigned to a role
                  Jonathan Lewis
                  user9212851 wrote:

                  Can you please let me know how to check the privileges assigned to a role in Oracle?

                  When I query the dba_tab_privs it says no rows returned.
                  When you've checked the manuals and identified the views suggested by other posters you will find that it's still not a trivial problem since a role may be granted to another role - which means you need to do some recursion to uncover all the privileges available to a role.

                  Pete Finnigan - who specialises in Oracle security - published some appropriate scipts a few years ago; they are probably still relevant. Here's a starting link: http://www.petefinnigan.com/weblog/archives/00001243.htm

                  Regards
                  Jonathan Lewis
                  • 6. Re: How to check the privileges assigned to a role
                    Ravi Kumar Pilla
                    Privileges assigned to a role
                    =================

                    select * from dba_sys_privs where grantee='DBA'
                    /* here DBA is role name */

                    privileges and roles assigned to user
                    ======================
                    Select a.grantee User_name, a.granted_role role, b.privilege from DBA_ROLE_PRIVS a, DBA_SYS_PRIVS b where
                    a.granted_role=b.grantee and a.grantee='SCOTT'

                    Edited by: Ravi Kumar Pilla on Feb 25, 2011 10:57 PM